Laravel Fluent Query Builder Join Subquery

Question

Good after several hours of research and still using DB :: select. I have to ask this question. Because I'm going to drive away my computer;).

I want to get the last user login (base at timestamp). I can do it with raw sql

SELECT c.*, p.* FROM users c INNER JOIN ( SELECT user_id, MAX(created_at) MaxDate FROM `catch-text` GROUP BY user_id ) MaxDates ON c.id = MaxDates.user_id INNER JOIN `catch-text` p ON MaxDates.user_id = p.user_id AND MaxDates.MaxDate = p.created_at 

I got this request from another post here on stackoverflow.

I tried everything to do this with the free query designer in Laravel, but without success.

I know the manual says you can do this:

 DB::table('users') ->join('contacts', function($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get(); 

But this does not help, because I do not see how I can use the subquery there? Anyone who can light my day?

Answer 1

Good for all of you who have come here in desperation, looking for the same problem. I hope you find it faster than me; O.

This is how it is solved. JoostK told me on github that "the first argument to join is the table (or data) you are joining." And he was right.

Here is the code. Various tables and names, but you will understand, right? It t

 DB::table('users') ->select('first_name', 'TotalCatches.*') ->join(DB::raw('(SELECT user_id, COUNT(user_id) TotalCatch, DATEDIFF(NOW(), MIN(created_at)) Days, COUNT(user_id)/DATEDIFF(NOW(), MIN(created_at)) CatchesPerDay FROM 'catch-text' GROUP BY user_id) TotalCatches'), function($join) { $join->on('users.id', '=', 'TotalCatches.user_id'); }) ->orderBy('TotalCatches.CatchesPerDay', 'DESC') ->get(); 

Answer 2

I was looking for a solution to a rather related problem: finding the latest entries per group, which is a specialization of the typical largest n-per-group with N = 1.

The solution includes the problem you are working with here (for example, how to build a query in Eloquent), so I am posting it as it may be useful to others. It demonstrates a cleaner way to create a subquery using the powerful Eloquent interface, which has several join columns and where condition is inside the joined sub-select.

In my example, I want to get the latest DNS scan results ( scan_dns table) for each group defined by watch_id . I build the subquery separately.

The SQL I want Eloquent to generate:

 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' 

I did it as follows:

 // table name of the model $dnsTable = (new DnsResult())->getTable(); // groups to select in sub-query $ids = collect([1,2,3,4,5,42]); // sub-select to be joined on $subq = DnsResult::query() ->select('x.watch_id') ->selectRaw('MAX(x.last_scan_at) as last_scan') ->from($dnsTable . ' AS x') ->whereIn('x.watch_id', $ids) ->groupBy('x.watch_id'); $qqSql = $subq->toSql(); // compiles to SQL // the main query $q = DnsResult::query() ->from($dnsTable . ' AS s') ->join( DB::raw('(' . $qqSql. ') AS ss'), function(JoinClause $join) use ($subq) { $join->on('s.watch_id', '=', 'ss.watch_id') ->on('s.last_scan_at', '=', 'ss.last_scan') ->addBinding($subq->getBindings()); // bindings for sub-query WHERE added }); $results = $q->get(); 

UPDATE:

Beginning with Laravel 5.6.17 , subquery joins have been added , so there is a custom way to create a query.

 $latestPosts = DB::table('posts') ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at')) ->where('is_published', true) ->groupBy('user_id'); $users = DB::table('users') ->joinSub($latestPosts, 'latest_posts', function ($join) { $join->on('users.id', '=', 'latest_posts.user_id'); })->get(); 

Answer 3

Request with additional request in Laravel

 $resortData = DB::table('resort') ->leftJoin('country', 'resort.country', '=', 'country.id') ->leftJoin('states', 'resort.state', '=', 'states.id') ->leftJoin('city', 'resort.city', '=', 'city.id') ->select('resort.*', 'country.name as country_name', 'states.name as state_name','city.name as city_name', DB::raw("(SELECT GROUP_CONCAT(amenities.name) from resort_amenities LEFT JOIN amenities on amenities.id= resort_amenities.amenities_id WHERE resort_amenities.resort_id=resort.id) as amenities_name"))->groupBy('resort.id') ->orderBy('resort.id', 'DESC') ->get(); 

Answer 4

I think you are looking for "joinSub". Supported with laravel ^ 5.6. If you use a version of laravel below 5.6, you can also register it as a macro in the application’s service provider file. like this https://github.com/teamtnt/laravel-scout-tntsearch-driver/issues/171#issuecomment-413062522

 $subquery = DB::table('catch-text') ->select(DB::raw("user_id,MAX(created_at) as MaxDate")) ->groupBy('user_id'); $query = User::joinSub($subquery,'MaxDates',function($join){ $join->on('users.id','=','MaxDates.user_id'); })->select(['users.*','MaxDates.*']); 

Answer 5

I can not comment, because my reputation is not high enough. @Franklin Rivero, if you use Laravel 5.2, you can set bindings for the main request instead of joining using the setBindings method.

So the main request in @ ph4r05's answer would look something like this:

 $q = DnsResult::query() ->from($dnsTable . ' AS s') ->join( DB::raw('(' . $qqSql. ') AS ss'), function(JoinClause $join) { $join->on('s.watch_id', '=', 'ss.watch_id') ->on('s.last_scan_at', '=', 'ss.last_scan'); }) ->setBindings($subq->getBindings()); 

Answer 6

You can use the following addon to handle all the subquery-related functions from laravel 5. 5+

https://github.com/maksimru/eloquent-subquery-magic

 User::selectRaw('user_id,comments_by_user.total_count')->leftJoinSubquery( //subquery Comment::selectRaw('user_id,count(*) total_count') ->groupBy('user_id'), //alias 'comments_by_user', //closure for "on" statement function ($join) { $join->on('users.id', '=', 'comments_by_user.user_id'); } )->get();