Left Join query in laravel

Question

how to query like

SELECT count(DISTINCT a.client_invoice_id) from client_invoice a LEFT JOIN document b on(a.client_invoice_id = b.relation_id_invoice) WHERE a.client_invoice_id IS NOT null and b.type_document = 'client_invoice'

in laravel version?

This is an inner join. If you like, you can prove this by running EXPLAIN EXTENDED.... followed by SHOW WARNINGS; — Strawberry
– Strawberry, Commented Mar 7, 2017 at 8:07

EddyTheDove · Accepted Answer · 2017-03-07 05:33:40Z

2

$result = DB::table('client_invoice as a')
->leftjoin('document as b', 'a.client_invoice_id', '=', 'b.relation_id_invoice')
->whereNotNull('a.client_invoice_id')
->where('b.type_document', '=', 'client_invoice')
->distinct()
->count();

edited Mar 7, 2017 at 5:33

answered Mar 7, 2017 at 5:20

EddyTheDove

13.2k2 gold badges40 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dwiky aliansyah Over a year ago

Thanks bro,but how query count(distinct) from those query ?

dwiky aliansyah Over a year ago

i have eror --SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.client_invoice' in 'where clause' (SQL: select distinct * from client_invoice as a left join document as b on a.client_invoice_id = b.relation_id_document where a.client_invoice is not null and b.type_document = client_invoice)-- did you know,bro ?

EddyTheDove Over a year ago

Check my update. add ->distinc() before ->count();

EddyTheDove Over a year ago

Sorry, replace a.client_invoice by a.client_invoice_id. Just add id at the end onwhereNotNull()

Mayank Pandeyz · Accepted Answer · 2017-03-07 05:19:35Z

1

Laravel left join:

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

Or you can use Raw Expression also like:

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

Laravel Left Join

Laravel Raw Expression

answered Mar 7, 2017 at 5:19

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

Collectives™ on Stack Overflow

Left Join query in laravel

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related