Build a way to use subqueries for joins

[josephmancuso]

Describe the feature as you'd like to see it
Need a way to build a query like this:

SELECT * FROM `scan_dns` AS `s`
INNER JOIN (
  SELECT x.watch_id, MAX(x.last_scan_at) as last_scan
  FROM `scan_dns` AS `x`
  WHERE `x`.`watch_id` IN (1,2,3,4,5,42)
  GROUP BY `x`.`watch_id`) AS ss
ON `s`.`watch_id` = `ss`.`watch_id` AND `s`.`last_scan_at` = `ss`.`last_scan`

What do we currently have to do now?
I don't believe we have a way to pass a builder to a join to create a subquery

Additional context
Found this query on Stack overflow and would like a way to do this via the query builder, if possible.

Open for discussion

• Is this a breaking change?

[josephmancuso]

This ticket is open for discussion on how this would be implemented with the query builder, if it is implemented at all.

[josephmancuso]

Possibly related, or requires, #436

[josephmancuso]

Maybe propose a new join_sub method? which should take the following signature: join_sub('other_table', 'subquery_alias')

builder.join_sub('scan_dns', 'ss', lambda query: (
    query.select('watch_id').max('x.last_scan_at as last_scan').where_in('watch_id', [1,2,3,4,5,42]).group_by('watch_id')
), 'watch_id', '=', 'ss.watch_id')

Will need to figure out how to do the "AND" there

[josephmancuso]

After we have implemented new joining logic using a new JoinClause class, I think we can do the same thing here using a JoinQuery class.

The example would look something like this:

from masoniteorm.expressions import JoinQuery

clause = (
    JoinQuery('scan_dns as x', as='ss')
    .select('x.watch_id')
    .max('x.last_scan_at as last_scan')
    .where('x.watch_id', [1,2,3,4,5,42])
    .group_by('x.watch_id')

query.join(clause)

This should be fairly simple to implement but a little harder to get the query builder part working unless we had a pass through method and somehow attached a query builder object to the JoinQuery