Bookshelf: Multiple Nesting Levels

[chimmelb]

This is a question/discussion.

In a multi-nested relationship, what is a good way to get to the lower relationships? Example, Survey --> hasMany('Questions')--> hasMany('Answers'). How do I query a survey with full tree?

var survey;
Survey.fetch({withRelated: ['questions'])
.then( function(foundSurvey) {
  survey = foundSurvey;
  return survey.related( 'questions' ).load( [ 'answers' ] );
} )
.then( function() {
   //do something with survey
} )

Being able to access MySQL in those 5 lines saved a lot of time, so thanks for Bookshelf.js : )

This works, and I think the DB calls made are: 1 for survey and all questions + N for the number of questions from which to get answers. Feels like a lot of DB access.

I tried to define my Survey model with an answers attribute that was this.hasMany('Answers').through('Question'), but it only retrieved answers for question 1, not question 2. Also, the answers were not nested within the question, but an answers array within the survey.

Should I instead create my Answer model that belongsTo('Survey').through('Question'), and fetch all answers with a particular survey ID? Might be more model setup than that. And my JSON tree might be inverted.

I could make a large Knex query, and after getting all the data, nest answers into questions, and questions into surveys, simply manipulating the JSON tree. But then I'm just using Knex.js, and not really Bookshelf (the things wouldn't be models).

I just want to be aware of what's going on with the ORM. Is there a more streamlined way? Anything worth trying?

I'd have similar questions about saving, but would guess it's a different discussion for CREATE vs UPDATE as well.

[tgriesser]

1 for survey and all questions + N for the number of questions from which to get answers. Feels like a lot of DB access.

Nope, Bookshelf should take care of the N + 1 query problem. Should only be 3 calls for the above, Survey, Questions, and Answers

There's also a shortcut for what you're doing above.

Survey.fetch({withRelated: ['questions.answers'])
.then(function(survey) {
   // do something with survey, loaded with questions, 
   // each of which has answers
})

or if, for example you want to constrain on the questions or answers

Survey.fetch({withRelated: ['questions': function(qb) {
  qb.where('flagged', true)
}, 'questions.answers'])
.then(function(survey) {
   // Loads all "flagged" questions, and their answers, still only 3 queries.
})

Just want to point out that for the time being, it's possible to run into the object-relational impedance mismatch problem if you fetch things too deeply... e.g. "Users have many Surveys, have many Answers, which belong-to Users." - If the same user is fetched twice in that case it's a different model for potentially the same user entry in the db, so changing one won't change the other - which isn't ideal.

I'm planning on addressing this with a session store for models when they're queried so all objects on the same graph will reference the same underlying record in the DB. This will also help with the saving - there is definitely a gap that needs to be filled for consistent nested saving of related models, currently you need to implement that yourself but I'd like to come up with something better this month.

[johanneslumpe]

@tgriesser for the object-relational impedance mismatch couldn't we use a simple cache on the constructor to take care of this? (maybe) Something like this: https://gist.github.com/johanneslumpe/f163d62924dd9d29dff9

That was something I had done for backbone-associations, but it should be possible to adapt it I guess. You already mentioned you want to use some kind of session store, just wanted to throw this out there in case it helps. Since this works with reference counting for removing objects from the cache when they're not used anymore it might be useful to actually use weakmaps to get rid of that step.

Will the session store be limited to a single request? Or how do want to define a session?

[chimmelb]

Thank you for those examples, that's great. Thanks for the tip on on mismatch as well. I will be aware of that as it comes up.

Could I ask how Bookshelf saves (creates) to the DB with multiple nested objects? Let's say we're creating a new survey with 3 questions and 5 answers for each question. I could see Bookshelf doing it in 19 inserts: one for survey, one for 3 questions, one for 15 answers. I could also see 3 queries again: one for survey, one for 3 questions, one for 15 answers.

I would guess updates are different and would be a on DB query for each item to updated. (Though I could see all the updates for a particular table in a single statement, but that might be overkill).

Thanks again for answering, I appreciate knowing what's going on behind the scenes when working with an ORM. You can close the issue, or leave it open for reference!

[bendrucker]

Bookshelf does not handle related updates or nested objects for saving. If you're inserting multiple objects your best bet is dropping to knex to handle the batch inserts and any transaction logic. The nice thing is that you can use the ORM or the query builder together.