Ad

Laravel Query Left Join

- 1 answer

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.

Ad

Answer

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();
Ad
source: stackoverflow.com
Ad