How to select from subquery using Laravel Query Builder

Question

I d like to get value by the following SQL using Eloquent ORM   - SQL   SELECT COUNT    FROM    SELECT   FROM abc GROUP BY col1  AS a    Then I considered the following   - Code    sql   Abc  from  abc AS a  - gt groupBy  col1  - gt toSql      num   Abc  from  DB  raw  sql  - gt count     print  num    I m looking for a better solution   Please tell me simplest solution

User · Answer

I like doing something like this   Message  select      - gt from DB  raw    SELECT   FROM  messages                    WHERE  to id      Auth  id     AND  isseen    0                   GROUP BY  from id  asc  as  sub     - gt count      It s not very elegant  but it s simple

User · Answer

The solution of  JarekTkaczyk it is exactly what I was looking for  The only thing I miss is how to do it when you are using  DB  table   queries  In this case  this is how I do it    other   DB  table  DB  raw     sub- gt toSql     as sub    - gt select       something        DB  raw  sum  qty   as qty          foo         bar      other- gt mergeBindings   sub     other- gt groupBy  something     other- gt groupBy  foo     other- gt groupBy  bar    print  other- gt toSql     other- gt get      Special atention how to make the mergeBindings without using the getQuery   method

User · Answer

Laravel v5 6 12  2018-03-14  added fromSub   and fromRaw   methods to query builder   23476    The accepted answer is correct but can be simplified into   DB  query  - gt fromSub function   query         query- gt from  abc  - gt groupBy  col1        a  - gt count      The above snippet produces the following SQL   select count    as aggregate from  select   from  abc  group by  col1   as  a

User · Answer

Correct way described in this answer  https   stackoverflow com a 52772444 2519714 Most popular answer at current moment is not totally correct   This way https   stackoverflow com a 24838367 2519714 is not correct in some cases like  sub select has where bindings  then joining table to sub select  then other wheres added to all query  For example query   select   from  select   from t1 where col1      join t2 on col1   col2 and col3     where t2 col4     To make this query you will write code like    subQuery   DB  query  - gt from  t1  - gt where  t1 col1    val1     query   DB  query  - gt from DB  raw       subQuery- gt toSql        AS subquery        - gt mergeBindings  subQuery- gt getBindings      query- gt join  t2   function JoinClause  join         join- gt on  subquery col1    t2 col2         join- gt where  t2 col3    val3      - gt where  t2 col4    val4      During executing this query  his method  query- gt getBindings   will return bindings in incorrect order like   val3    val1    val4   in this case instead correct   val1    val3    val4   for raw sql described above   One more time correct way to do this    subQuery   DB  query  - gt from  t1  - gt where  t1 col1    val1     query   DB  query  - gt fromSub  subQuery   subquery     query- gt join  t2   function JoinClause  join         join- gt on  subquery col1    t2 col2         join- gt where  t2 col3    val3      - gt where  t2 col4    val4      Also bindings will be automatically and correctly merged to new query

User · Answer

From laravel 5 5 there is a dedicated method for subqueries and you can use it like this   Abc  selectSub function  q         q- gt select     - gt groupBy  col1        a  - gt count  a        or  Abc  selectSub Abc  select     - gt groupBy  col1     a  - gt count  a

User · Answer

In addition to  delmadord s answer and your comments   Currently there is no method to create subquery in FROM clause  so you need to manually use raw statement  then  if necessary  you will merge all the bindings    sub   Abc  where    - gt groupBy         Eloquent Builder instance   count   DB  table  DB  raw     sub- gt toSql     as sub         - gt mergeBindings  sub- gt getQuery       you need to get underlying Query Builder     - gt count      Mind that you need to merge bindings in correct order  If you have other bound clauses  you must put them after mergeBindings    count   DB  table  DB  raw     sub- gt toSql     as sub             - gt where     wrong      - gt mergeBindings  sub- gt getQuery       you need to get underlying Query Builder         - gt where     correct      - gt count

User · Answer

I could not made your code to do the desired query  the AS is an alias only for the table abc  not for the derived table   Laravel Query Builder does not implicitly support derived table aliases  DB  raw is most likely needed for this   The most straight solution I could came up with is almost identical to yours  however produces the query as you asked for    sql   Abc  groupBy  col1  - gt toSql     count   DB  table DB  raw    sql  AS a   - gt count      The produced query is   select count    as aggregate from  select   from  abc  group by  col1   AS a

[sql] How to select from subquery using Laravel Query Builder?

Examples related to sql

Examples related to laravel

Examples related to laravel-4

Examples related to eloquent

Examples related to query-builder