Add support for specifying better where clauses when doing a join

[josephmancuso]

I'm trying to inner join a Holiday table to a HolidayDate table, then get all rows that are on dates within the array 'datesOfWeek'.

The 'Date' column is in the HolidayDate table and the 'Deleted' and 'HolidayName' column are in the Holiday table. Also the Holiday model has a has_many relationship to the HolidayDate table defined using: @has_many('IDHoliday', 'HolidayID').

Here's a bit of the code I'm writing, I believe this should be enough to replicate my issue:

datesOfWeek = ['2021-03-11', '2021-03-12']
Holiday.joins('holidayDates').where('Deleted', '=', 0).where_in('Date', datesOfWeek).order_by('HolidayName', 'asc').get()

If it helps at all when using Orator ORM I believe I had it like:

datesOfWeek = ['2021-03-11', '2021-03-12']
Holiday.join('HolidayDate', 'Holiday.IDHoliday', '=', 'HolidayDate.HolidayID').where('Deleted', '=', 0).where_in('Date', datesOfWeek).order_by('HolidayName', 'asc').get()

[josephmancuso]

Firstly, the code snippets in the description that example Orator actually should not be correct. The where clauses at the end should be based on the holiday table since it's being built on the holiday model.

My solution here is to do a few things.

Solution 1

Existing solution would be to have a where clause:

datesOfWeek = ['2021-03-11', '2021-03-12']
Holiday.joins('holidayDates').where(lambda q: (
    q.where_in('Date', datesOfWeek).table("holiday_dates")
))

This should work fine but its not perfect. One of the issues here is that the table name is holiday_dates but that means we need to hardcode the table name.

Solution 2

The second solution here is to use a new join_on method available in the latest release which takes a single relationship and a lambda (callback) function.

This will allow you to not have to specify the table name as it will be pulled from the related methods model:

This would look something like:

datesOfWeek = ['2021-03-11', '2021-03-12']
Holiday.join_on('holidayDates', lambda q: (
    q.where_in('Date', datesOfWeek)
))

Notice we don't need to specify the table name here as the columns will be based on the related tables column names.

Documentation can be found here: https://orm.masoniteproject.com/models#joining

[josephmancuso]

@liquidgenius Joining has been improved. Reference the documentation here: https://orm.masoniteproject.com/query-builder#joining