how to replicate joins in db.query?

this is a db.select query with a join to filter out only answers that are accepted

const queryAcceptedAnswers = (userId: number)=>
  db.select()
  .from(answersTable)
  .where(eq(answersTable.authorId, userId))
  .innerJoin(questionsTable, eq(answersTable.id, questionsTable.acceptedAnswerId))

const queryAcceptedAnswers = (userId: number)=>
  db.select()
  .from(answersTable)
  .where(eq(answersTable.authorId, userId))
  .innerJoin(questionsTable, eq(answersTable.id, questionsTable.acceptedAnswerId))

let's say, there may be 2 accepted answers, thus this will be the response

"acceptedAnswers": [
    {
      "answers": {
        "id": 8,
        "content": "🤡🤡🤡",
        "authorId": 109352196,
        "questionId": 7,
        "createdAt": "2024-03-23T13:14:22.690Z",
        "useless": false
      },
      "questions": {
        "id": 7,
        "title": "какыфмыфвфывс",
        "content": "фывфывыфвывс",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.434Z",
        "category": "отношения",
        "acceptedAnswerId": 8
      }
    },
    {
      "answers": {
        "id": 6,
        "content": "aaa",
        "authorId": 109352196,
        "questionId": 8,
        "createdAt": "2024-03-23T11:46:38.130Z",
        "useless": false
      },
      "questions": {
        "id": 8,
        "title": "bruh",
        "content": "bruh222",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.992Z",
        "category": "отношения",
        "acceptedAnswerId": 6
      }
    }
  ]

"acceptedAnswers": [
    {
      "answers": {
        "id": 8,
        "content": "🤡🤡🤡",
        "authorId": 109352196,
        "questionId": 7,
        "createdAt": "2024-03-23T13:14:22.690Z",
        "useless": false
      },
      "questions": {
        "id": 7,
        "title": "какыфмыфвфывс",
        "content": "фывфывыфвывс",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.434Z",
        "category": "отношения",
        "acceptedAnswerId": 8
      }
    },
    {
      "answers": {
        "id": 6,
        "content": "aaa",
        "authorId": 109352196,
        "questionId": 8,
        "createdAt": "2024-03-23T11:46:38.130Z",
        "useless": false
      },
      "questions": {
        "id": 8,
        "title": "bruh",
        "content": "bruh222",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.992Z",
        "category": "отношения",
        "acceptedAnswerId": 6
      }
    }
  ]

How do I use joins in db.query api that will result in the same response or will filter out users accepted answers in a similar way? is it even possible with db.query? do i have to manage my relations in the schema?

1 Reply

Sillvva•10mo ago

I don't think this is possible with the query builder alone. Some amount of code-based filtering would be required. - answers with questions would join on answers.questionId = questions.id. You can filter by authorId. This would get all of the user's answers and you'd have to use code to filter out the answers where the questions.acceptedAnswerId doesn't match the answers.id. - questions with answers would join on questions.acceptedAnswerId = answers.id. You could add a where to questions to ensure it has an accepted answer and to answers to ensure the answer has a specific author. However, this would still get all answered questions regardless of whether the specified user answered it or not. If the user did not answer the question or their answer was not accepted, then it would return answers = null. And you'd have to use code to filter out the ones with a value. If you had put the accepted criteria in the answers table as a boolean instead, this would have been possible with:

const acceptedAnswers = db.query.answers.findMany({
  with: {
    questions: true
  },
  where: (answers, { eq, and }) => and(eq(answers.authorId, userId), eq(answers.accepted, true))
})

const acceptedAnswers = db.query.answers.findMany({
  with: {
    questions: true
  },
  where: (answers, { eq, and }) => and(eq(answers.authorId, userId), eq(answers.accepted, true))
})

Gaming

Programming

how to replicate **joins** in **db.query**?

how to replicate joins in db.query?