Eloquent with() as a left join

My table articles have a nullable column "supplier_id", meaning not all articles have a supplier. When I run:

$articles = Article::with('supplier')->get();

I like to get ALL my articles, no matter if supplier_id is null or an integer. How can I achieve that using Eloquent?

LaryAI

3 years ago

Level 58

To achieve this, you can use a left join instead of an inner join. You can do this by passing a closure to the with method and using the leftJoin method to join the suppliers table. Here's an example:

$articles = Article::with(['supplier' => function ($query) {
    $query->leftJoin('suppliers', 'articles.supplier_id', '=', 'suppliers.id');
}])->get();

This will return all articles, including those without a supplier. The supplier relationship will be null for articles without a supplier.

Note that you'll need to adjust the join condition to match your table structure.

muuucho

3 years ago

Level 11

It gives the following error:

Not unique table/alias: 'suppliers' (Connection: mysql, SQL: select * from suppliers left join suppliers on articles.supplier_id = suppliers.id where suppliers.id in (1, 2))

I like have a collection where the "supplier_id" holds an array (orobject) of the supplier. If not I will have conflicts e.g. articles.name and suppliers.name

muuucho

3 years ago

Level 11

This is what I want in Eloquent:

$articles = DB::table('articles')
            ->select('articles.*', 'suppliers.id as supplier_id', 'suppliers.name as supplier_name')
            ->leftJoin('suppliers',  'articles.supplier_id',  '=',  'suppliers.id')
            ->orderBy('name')
            ->get();

JussiMannisto

3 years ago

Best Answer

Level 50

Your original query should get all Articles. If an Article doesn't have a supplier, then $article->supplier will be null. Are you seeing something different?

1 like

muuucho

3 years ago

Level 11

You are right @jussimannisto . The error was in y view. Instead of echo out

{{ $article->supplier->name }}

I am now echoing

{{ $article->supplier->name  ?? ''}}

Please or to participate in this conversation.