Objection.js: Query based on eager loaded relation data

[nlazzari]

I'm pretty new, so excuse me if this is a dumb question, or listed elsewhere. I'm wondering how I can make queries based on related model data that is eager loaded in?

For instance, I have a model called Doctor, that uses a HasManyRelation relation property contactInfo as such:

contactInfo: {
relation: Model.HasManyRelation,
modelClass: __dirname + '/DoctorContact',
join: {
from: 'Doctors.id',
to: 'DoctorContacts.DoctorId'
}
}

I'm wondering how I can make a query where, I want all Doctors (and their related data), such that the Doctor is from a certain city (a column from the table used in the relation contactInfo)?

My current query structure is like so:

Doctor
.query()
.eager('[contactInfo]')
.skipUndefined()
.orderBy('lastName')

I know how to do this manually in SQL, but then I would lose all the nice options Objection provides. Any help ?

[newhouse]

This is a good one and I just tried doing it like 10 different ways, but most of them make multiple queries OR would return Doctor instances even if the DoctorContacts.cityField was not a match OR would not load up the Doctor.contactInfo relations as Model instances, etc, etc.

Here's what I found to be the most efficient way to get what I think you want. Apologies if there are any errors in my translation from my stuff to your example, but I think you can take it across the finish line from here:

  .query()
  // Use a (LEFT) JOIN instead of multiple "WHERE IN(...)" queries
  .eagerAlgorithm(Doctor.JoinEagerAlgorithm) 
  .eager('contactInfo')
  // Add a WHERE clause to the eager query to only get ones with the City you're afer
  .modifyEager('contactInfo', builder => {
    builder.where('cityField', 'cityValueYouAreInterestedIn');
  })
  // Filter by some field on the relation table that would not be NULL if the JOIN worked
  // This would not be needed if the eager could be made to NOT use a left join, but I don't know how to do that
  .whereNotNull('contactInfo.cityField')
  .orderBy('lastName')
  .then(onlyDoctorsWithContactInfoInCityYouCareAbout => {

    // Make sure some Doctors were found
    if (typeof onlyDoctorsWithContactInfoInCityYouCareAbout === 'undefined') {
      // No rows found
    }

    // Do some stuff
  });

I'd be interested to see any other thoughts, particularly if there's a way to do a straight join instead of a left join in the JoinEagerAlgorithm to avoid adding that whereNotNull() clause.

Keep in mind that this will only return the contactInfo record(s) for each Doctor that match the City you were filtering on, even though that Doctor may have more contactInfo records than just those. If you wanted to have all contactInfos for all Doctor instances that have any contactInfos with the matching City, let me know and I can whip something else up.

Hope this at least helps...

[fl0w]

I think what you're looking for is modifyEager. Based on the path expression, it appends to the related query.

Check out the examples in docs and write back if you have further questions.

// untested example
await Doctor
  .query()
  .eager('contactInfo')
  .modifyEager('contactInfo', builder => {
    builder.where('city', 'Stockholm')
  })

Edit I think I misread your question - leaving for history.

Another example more close to what you're actually asking, think of objection.js as the query builder you've always wanted - not an ORM.

// untested example
await Doctor // tableName = doctor
  .query()
  .innerJoin('join_table_contact', 'doctor.id', 'join_table_contact.doctor_id')
  .innerJoin('contact_info', 'join_table_contact.contact_id', 'contact_info.id')
  .where('contact_info.city', 'stockholm')
  .eager('contactInfo')

[koskimas]

In addition to what @newhouse suggested, this could also work:

const contactsSubQuery = DoctorContact
  .query()
  .whereRef('DoctorId', 'Doctor.id')
  .where('cityField', 'Sqlmagicstan');

Doctor
  .query()
  .whereExists(contactsSubQuery)
  .eager('contactInfo');

If you want to use joins joinRelation method may be the one you are looking for.

[nlazzari]

@newhouse - you were bang on, and the pitfalls you describe mirror my own.
The only other problem I'm running into now is that I'm trying to add pagination using .page(pageNumber, 10). When I add the .page query I get the error message:
'Error: TypeError: Cannot read property 'columns' of undefined'. Any idea how to solve this ?

@fl0w - I will try your edit solution using inner joins and let you know how that goes, thanks.

@koskimas - I tried your solution and kept getting 'missing FROM' errors, I suspect it falls into the same pitfalls that @newhouse described when he was trying to find a solution.

Thanks for all the detailed responses guys, I really appreciate the help.

[newhouse]

Loss of the use of page and limit methods are listed in the limitations of the JoinEagerAlgorithm, unfortunately. Also, I've not used Objection's pagination helpers before but now's a good time to try.

You're starting to get into the realm of serious trade-offs, but for pagination I'm pretty sure you'll need a subquery to do it in 1 shot. There are definitely a lot of ways to do this and I'm probably doing something horrible, but here's at least one solution that kind of sticks with what _I think_ that _you think_ is important:

  .query()
  .eagerAlgorithm(Doctor.JoinEagerAlgorithm) // Still single query JOIN to load the Models
  .eager('contactInfo')
  // Use this modifyEager if you want ONLY the "Sqlmagicstan" DoctorContact records fetched and attached at the end of the day. Else no need.
  .modifyEager('contactInfo', builder => {
    builder.where('cityField', 'Sqlmagicstan');
  })
  .whereIn('id',

    // Sub-query the Doctors table, returning only 'id' for Doctors with an office in Sqlmagicstan
    Doctor
      .query()
      .select('id')
      .joinRelation('contactInfo')
      .where('contactInfo.city', 'Sqlmagicstan')
      .orderBy('lastName') // Ordering by Last Name since you're paginating for a reason
      .orderBy('id') // Ordering by id in case there are many Dr. Newhouses in Sqlmagicstan
      .page(pageNumber, 10) // This is where you can leverage paging
  )
  .orderBy('lastName') // Order by Last Name again since that's how expect the pagination to come out
  .then(aPageOfDoctorsInSqlmagicstan => {
  });

If I had more time (have to run) I'd figure out how to make that subquery return distinct Doctor IDs since if "Dr. Newhouse" has more than 1 office in Sqlmagicstan, that subquery (while returning 10 rows) will only result in <= 9 Doctors instantiated by the outer query. Probably a distinct or groupBy or something like that will do it.

[koskimas]

@nlazzari Did you solve your problem? Can I close this?

[nlazzari]

I'm still implementing this solution, will see tomorrow if this has done the trick.

[koskimas]

@nlazzari how about now?

[nlazzari]

Sorry, yes it's good now, you can close the thread. Thanks everyone, I appreciate your help.