phpmysqllaravellaravel-5inner-query

Inner query inside select in laravel query builder?

How can I place inner query inside a select statement in laravel query builder. I want to convert following mysql query in to laravel query builder

select i1.client_id,count(*),(
    select SUM((ii.quantity*ii.rate))as total 
    from invoice_items ii 
    join invoices i on i.id = ii.invoice_id
    where i.client_id =i1.client_id)as sale
from invoices i1
group by i1.client_id

Any idea?

Solution

  • Hi you can create you query like with laravel query builder

    DB::select(DB::raw("i1.client_id,count(*),(
    select SUM((ii.quantity*ii.rate))as total 
    from invoice_items ii 
    join invoices i on i.id = ii.invoice_id
    where i.client_id =i1.client_id)as sale"))
->from('from invoices i1')
->groupBy('i1.client_id');

    hope this will work for you.