Laravel : Combine two tables using Model relations

Question

i have two tables :: shops and attachments . Shops has all the details for shop and attachments with all the pictures for shop with ref_id as FK .

now for mobile app i have to list shops with Shop title and one image. I can achieve it using leftjoin as below.

Shop::select('shops.name as shop_name','attachments.name as picture')
            ->leftjoin('attachments','attachments.ref_id','=','shops.shop_id')
            ->paginate(10);

This returns just shop name and pictures , I want to achieve same results using relationships that i am not sure of how can i do that. Can someone please advise me on that

Edit

Shop Model

public function attachments(){
     return $this->hasMany(Attachment::class, 'ref_id', 'shop_id');
}

 dd(Shop::find(34)->attachments()->first());
 dd(Shop::with('attachments')->get());

using first dd returns me attachment associated with 34 and but with query is not working and returns just shop

score 2 · Accepted Answer · answered May 20 '22 at 08:28

2

First of all, add the attachments relationship function to the Shop model.

class Shop 
{
    ...

    public function attachments()
    {
        return $this->hasMany(Attachment::class, 'ref_id', 'shop_id');
    }
}

Then you can access shop attachments by using the ->attachments() on the Shop objects.

Example for getting all with pagination: (eager loading)

$shops = Shop::with('attachments')->paginate(10);

# access to first attachment of first shop
$shops->first()->attachments->first()->name

Note: It's better to use eager loading when you want to access all shops attachments to prevent the N+1 query problem

answered May 20 '22 at 08:28

A.Seddighi

1,695
1
20
43

Thanks for your reply , i did the same but its not returning me attachments data while ref_id is same as shop_id, is there anything that i am missing ? – stackoverflow account May 20 '22 at 09:32
Could you please share your complete query – A.Seddighi May 20 '22 at 09:33
1

its working thanks a lot , my mistake i was not looking relations index – stackoverflow account May 20 '22 at 09:46

paulin-crtn · Answer 2 · 2022-05-20T07:51:22.090

0

I guess you have to define a One to Many relation in your Shop model (because one shop can have multiple attachements).

Maybe something like this :

// In Shop model

public function attachments()
{
   return $this->hasMany(Attachment::class, 'ref_id', 'shop_id');
}

Then you can use it like this (you don't have to use the find, you can use a where if you want) :

$attachment = Shop::find(1)->attachments()->first();

https://laravel.com/docs/9.x/eloquent-relationships#one-to-many

edited May 20 '22 at 07:51

answered May 20 '22 at 07:43

paulin-crtn

325
2
9

Thanks for your reply , this simple hasMany relation i know but how do i do it with all records like shop::all() with attachments , also cant it be one object using relationship like result is returned by joins #original: array:7 [ "attachment_id" => 44 "name" => "6724370727.jpg" , "shop_name" :"name of shop "] where shop name from shops and other from attachments – stackoverflow account May 20 '22 at 07:57
I don't know, may be take a look at `with()` or `whereHas()` methods: https://dev.to/othmane_nemli/laravel-wherehas-and-with-550o – paulin-crtn May 20 '22 at 08:07

Laravel : Combine two tables using Model relations

2 Answers2