Laravel Fluent Query Builder Join with subquery

Question

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       - gt join  contacts   function  join                 join- gt on  users id         contacts user id  - gt orOn                  - gt 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

User · Accepted Answer

Ok for all of you out there that arrived here in desperation searching for the same problem. I hope you will find this quicker then I did ;O.

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

Here is the code. Different table and names but you will get the idea 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();

User · Answer

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  - gt leftJoinSubquery      subquery   Comment  selectRaw  user id count    total count         - gt groupBy  user id        alias    comments by user        closure for  on  statement   function   join           join- gt on  users id         comments by user user id         - gt get

User · Answer

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   - gt getTable        groups to select in sub-query  ids   collect  1 2 3 4 5 42        sub-select to be joined on  subq   DnsResult  query       - gt select  x watch id       - gt selectRaw  MAX x last scan at  as last scan       - gt from  dnsTable     AS x       - gt whereIn  x watch id    ids      - gt groupBy  x watch id     qqSql    subq- gt toSql        compiles to SQL     the main query  q   DnsResult  query       - gt from  dnsTable     AS s       - gt join          DB  raw        qqSql     AS ss            function JoinClause  join  use   subq                 join- gt on  s watch id         ss watch id                    - gt on  s last scan at         ss last scan                    - gt addBinding  subq- gt getBindings                           bindings for sub-query WHERE added               results    q- gt 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                      - gt select  user id   DB  raw  MAX created at  as last post created at                       - gt where  is published   true                     - gt groupBy  user id      users   DB  table  users           - gt joinSub  latestPosts   latest posts   function   join                 join- gt on  users id         latest posts user id              - gt get

User · Answer

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               - gt joinSub  select user id count id  noOfPosts from posts group by user id    totalPosts    users id         totalPosts user id    left               - gt select  users name    totalPosts noOfPosts               - gt 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- gt 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

User · Answer

Query with sub query in Laravel   resortData   DB  table  resort           - gt leftJoin  country    resort country         country id           - gt leftJoin  states    resort state         states id           - gt leftJoin  city    resort city         city id           - gt 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   - gt groupBy  resort id           - gt orderBy  resort id    DESC          - gt get

User · Answer

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       - gt from  dnsTable     AS s       - gt join          DB  raw        qqSql     AS ss            function JoinClause  join                 join- gt on  s watch id         ss watch id                    - gt on  s last scan at         ss last scan                   - gt setBindings  subq- gt getBindings

User · Answer

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               - gt select DB  raw  user id MAX created at  as MaxDate                - gt groupBy  user id      query   User  joinSub  subquery  MaxDates  function  join              join- gt on  users id       MaxDates user id      - gt select   users     MaxDates

[laravel] Laravel Fluent Query Builder Join with subquery

Examples related to laravel

Examples related to laravel-4