Question: QueryBuilder how to join on id columns and a column with arbitrary value?

[circulon]

Describe the feature as you'd like to see it
I have a requirement that a join be done on a foreign key and the value of another column.

example query:

select item_id, item_group, group1_table.name as group1_name, group2_table.name as group2_name, group3_table.name as group3_name
	from items_used
	left join group1_table on item_group='household' and item_id = group1_table.id
	left join group2_table on item_group='garden' and item_id = group2_table.id
	left join group3_table on item_group='garage' and item_id = group3_table.id

	where items_used.user_id = '30745508-bc6f-49fd-a635-18f5d96f5afe'
	group by item_id, item_group, group1_name, group2_name, group3_name
	order by count(item_id);

This will produce rows where only the groupx_name columns are filled when the join for that item is fully realised
example results

item_id	item_group	group1_name	group2_name	group3_name
6d7d865f-e4e5-4a23-8804-d5cb41dc0fec	household	Box Cutter
c1793683-ccfe-4cb8-b41d-f9361c737928	garden		spade

In Orator you can do it like this example
Its obviously not the same as above query but you get the idea

clause = JoinClause('contacts').on('users.id', '=', 'contacts.user_id').where('contacts.user_id', '>', 5)
db.table('users').join(clause).get()

How do we do this in Masonite Orm?
I am tempted to build a raw query but that would become fragile and I would like to know how to do this properly as the join documentation does not have any info for this common usage.

Help and pointers would be appreciated.

Thanks

[josephmancuso]

Theres not really a simple way to do this with a query builder directly. Using models is a little easier as you can just do:

User.join_on('posts', lambda q: (
  q.where('active', 1)
))

This does a little bit of Kung Fu under the hood though. I can see how much easier it would be to do this without models and directly with the query builder.

Maybe an interface like:

builder.left_join('table1', 'table2.id', '=', 'table1.table_id', lambda query: (
    query.where('contacts.user_id', '>', 5)
))

Would something like that work?

[circulon]

@josephmancuso
Sorry for the Delay in getting back on this.

Yeah the lambda option is good and it helps encapsulate the additional column criteria.
Though this does feel intuitive/obvious IMHO.
Just a thought but how would you do this with 2 or more columns of arbitrary values?

Another option where the lambda parameter is instead have an optional list of tuples
the list of tuples would encapsulate the additional join criterias like so:

builder.left_join('table1', 'table2.id', '=', 'table1.table_id', [('contacts.user_id', '>', 5), ('company.agent_id', '=', '2') ])

which feels more intuitive to me.

WDYT?

[circulon]

Bump @josephmancuso

[josephmancuso]

One solution is to use a new JoinClause class where we can append values onto them

clause = (                                                                       
    JoinClause("report_groups as rg")                                            
    .on("bgt.fund", "=", "rg.fund")                                              
    .on("bgt.dept", "=", "rg.dept")                                              
    .on("bgt.acct", "=", "rg.acct")                                              
    .on("bgt.sub", "=", "rg.sub")                                                
) 
query.join(clause)

[circulon]

@josephmancuso

Apologies for the lack of response on this .... Life is busy ATM ;)

This JoinClause looks to be ideal!

I'm assuming that as per my original question it will be able to handle arbitrary values?

[josephmancuso]

As in where clauses? Yes. #471

[circulon]

@josephmancuso

Nice work!
Simple, elegant, and obvious to read.