Laravel query left join

Question

I have a self referencing table with parent id and a child category_id, this query works has SQL:

SELECT a.id, a.name, b.name, a.created_at
            FROM shop_categories a
            LEFT JOIN shop_categories b ON (a.id = b.category_id)
            WHERE a.category_id = 0

And I'm trying to translate this to Laravel Query Builder language, I managed to come up with this, but it does not work, and I don't know what I'm doing wrong, my laravel query:

$shopcategories = DB::table('shop_categories as a')
            ->select('a.id', 'a.name','b.name','a.created_at')
            ->leftJoin('shop_categories as b', function ($join) {
                $join->on('a.id', '=', 'b.category_id');
            })
            ->where('a.category_id', '=', 0)
            ->get();

Thanks in advance.

Call toSql() instead of get() to see generated SQL query and check if it's what you expect. If not, please paste the generated query. — jedrzej.kurylo, Jan 03 '16 at 10:24

score 3 · Accepted Answer · answered Jan 03 '16 at 13:24

3

Use query builder in this manner:

DB::table('table1')
    ->leftJoin('table2', 'table1.columnX', '=', 'table2.columnY')
    ->where('table1.other_column', '=', 'someValue')
    ->select('table1.columnA as NewColumn1', 'table1.columnB as NewColumn2', 'table1.columnC as NewColumn3', 'table2.columnP4 as NewColumn', 'table2.columnQ as NewColumn5')
    ->get();

answered Jan 03 '16 at 13:24

Rajender Joshi

4,155
1
23
39

This doesn't work since its a self referencing table, (table1) and (table2) are the same table. – Bruno Jan 03 '16 at 17:50
This might help you http://stackoverflow.com/questions/24672629/laravel-orm-from-self-referencing-table-get-n-level-hierarchy-json – Rajender Joshi Jan 03 '16 at 17:59

Laravel query left join

1 Answers1