I am having two tables - Questions and Answers which are connected by foreign key on Answers named questionId. Each question may contain multiple answers. I am trying to create a single query to obtain questions with all fields and put answers into field of type array. For instance this is the question structure:

{
id: 5
name: "abc",
answers: ["a","b","c"]
}

How can I force knex to group by questionId and put all of them to answers? I tried to use leftjoins but it doesn't work for one to many relationships. This is what I've tried:

 var questionQuery = this.knex.select().table(this.questionWithAnswersTb)
            .select("*")
            .leftJoin(this.answersTb, this.questionWithAnswersTb + ".id", this.answersTb + ".questionId");

I am having two tables - Questions and Answers which are connected by foreign key on Answers named questionId. Each question may contain multiple answers. I am trying to create a single query to obtain questions with all fields and put answers into field of type array. For instance this is the question structure:

{
id: 5
name: "abc",
answers: ["a","b","c"]
}

How can I force knex to group by questionId and put all of them to answers? I tried to use leftjoins but it doesn't work for one to many relationships. This is what I've tried:

 var questionQuery = this.knex.select().table(this.questionWithAnswersTb)
            .select("*")
            .leftJoin(this.answersTb, this.questionWithAnswersTb + ".id", this.answersTb + ".questionId");

• javascript
• orm

Share Improve this question Follow edited Dec 22, 2015 at 17:20 MistyK asked Dec 22, 2015 at 17:14 MistyKMistyK 6,27255 gold badges4747 silver badges8181 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 2

I would approach this by doing multiple queries. I would first query for the question's that I want, and you can make your initial questionObjects array with this information that has the name and id properties. Then you can iteratively call something like:

var questionObjects = [{id: 5, name: abc}, {id: 6, name: xyz}]; 
// first query makes something like this array ^^, then..
questionObjects.forEach(function(question, index) {
  this.knex.select('answersTable.answers').from('questionsTable').leftJoin(
  'answersTable',
  'answersTable.questionId', 
  'questionsTable.id').where('answersTable.questionId', question.id})
  .then(function(answers) {
    answers = answers.map(/*make your answers look the way you want...*/)
    questionObjects[index].answers = answers;  
  })

That last bit in the .then() callback may require some additional manipulation of the return value from the previous query to get the answers in the array format you ultimately want.

You can make sql-views from the rdbms for your one-to-many or many-to-many relationships you will have. and then query from those views. Its easier that way if you are going to reuse those relationship using knex. For insertion and updating use one knex transaction on the tables.