CODEWITHSUNDEEP
mysqllaravelormeloquent
Maeh
Maeh

Reputation: 1764

Limit JOIN to 1

I am trying to pick out Shops with the specified $item_id.

An Item can have multiple Images, but I only want the first one.

The current code gives me only the first image, but it duplicates the item as many times as there are images.

Here's the code:

$shops = Shop::with(array('items' => function($query) use ($item_id) {

                    $query->select('items.id', 'items.name', 'items_images.path AS image');
                    $query->join('items_images', 'items.id', '=', 'items_images.item_id');
                    $query->where('items.id', '=', $item_id);

                }))
                ->get(array('shops.id', 'shops.shop_name', 'shops.lat', 'shops.lng'));

And the current output:

enter image description here

How can I avoid getting all the duplicates?

Upvotes: 0

Views: 31

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

I think this will work.. I haven't done much at all with this syntax but I think this is how it would work (assuming main is in the items_images table)

$shops = Shop::with(array('items' => function($query) use ($item_id) 
{

    $query->select('items.id', 'items.name', 'items_images.path AS image');
    $query->join('items_images', function($join)
    {
         $join->on('items.id', '=', 'items_images.item_id')
              ->where('items_images.main', '=', 1);
    })
    $query->where('items.id', '=', $item_id);
}))->get(array('shops.id', 'shops.shop_name', 'shops.lat', 'shops.lng'));

Found the DOCS on it... if you want to include it in the JOIN then it looks like you have to add a where to the JOIN

Upvotes: 1

Related Questions