[laravel] Laravel Fluent Query Builder Join with subquery

Okay after hours of research and still using DB::select I have to ask this question. Because I am about to trough my computer away ;).

I want to get the last input of a user (base on the timestamp). I can do this with the 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 query from another post here on stackoverflow.

I have tried everything to do this with the fluent query builder in Laravel however with no 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 that is not helping much because I do not see how I could use a subquery there? Anyone who can light up my day?

This question is related to laravel laravel-4

The answer is


Query with sub query 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();

I am on Laravel 7.25 and I don't know if it supports on previous versions or not but Its pretty good.

Syntax for the function:

public function joinSub($query, $as, $first, $operator = null, $second = null, $type = 'inner', $where = false)

Example:

Showing/Getting the user ID and the total number of posts by them left joining two tables users and posts.

        return DB::table('users')
            ->joinSub('select user_id,count(id) noOfPosts from posts group by user_id', 'totalPosts', 'users.id', '=', 'totalPosts.user_id', 'left')
            ->select('users.name', 'totalPosts.noOfPosts')
            ->get();

Alternative:

If you don't wanna mention 'left' for leftjoin then you can use another prebuilt function

    public function leftJoinSub($query, $as, $first, $operator = null, $second = null)
    {
        return $this->joinSub($query, $as, $first, $operator, $second, 'left');
    }

And yeah, it actually calls the same function but it passes the join type itself. You can apply the same logic for other joins i.e. righJoinSub(...) etc.


I can't comment because my reputation is not high enough. @Franklin Rivero if you are using Laravel 5.2 you can set the bindings on the main query instead of the join using the setBindings method.

So the main query 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());

I was looking for a solution to quite a related problem: finding the newest records per group which is a specialization of a typical greatest-n-per-group with N = 1.

The solution involves the problem you are dealing with here (i.e., how to build the query in Eloquent) so I am posting it as it might be helpful for others. It demonstrates a cleaner way of sub-query construction using powerful Eloquent fluent interface with multiple join columns and where condition inside joined sub-select.

In my example I want to fetch the newest DNS scan results (table scan_dns) per group identified by watch_id. I build the sub-query 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 in the following way:

// 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:

Since Laravel 5.6.17 the sub-query joins were added so there is a native way to build the 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();

You can use following addon to handle all subquery related function 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();

I think what you looking for is "joinSub". It's supported from laravel ^5.6. If you using laravel version below 5.6 you can also register it as macro in your app 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.*']);

Examples related to laravel

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration Target class controller does not exist - Laravel 8 Visual Studio Code PHP Intelephense Keep Showing Not Necessary Error The POST method is not supported for this route. Supported methods: GET, HEAD. Laravel How to fix 'Unchecked runtime.lastError: The message port closed before a response was received' chrome issue? Post request in Laravel - Error - 419 Sorry, your session/ 419 your page has expired Expected response code 250 but got code "530", with message "530 5.7.1 Authentication required How can I run specific migration in laravel Laravel 5 show ErrorException file_put_contents failed to open stream: No such file or directory

Examples related to laravel-4

Parameter binding on left joins with array in Laravel Query Builder Laravel 4 with Sentry 2 add user to a group on Registration 'Malformed UTF-8 characters, possibly incorrectly encoded' in Laravel Can I do Model->where('id', ARRAY) multiple where conditions? how to fix stream_socket_enable_crypto(): SSL operation failed with code 1 Rollback one specific migration in Laravel How can I resolve "Your requirements could not be resolved to an installable set of packages" error? Define the selected option with the old input in Laravel / Blade Redirect to external URL with return in laravel laravel the requested url was not found on this server