Joining 3 tables relations and mergin the info

Hi there guys, my current tables that i have is related with translation.

Languages:
- id;
- name;

Products:
- id;
- online;
- price;

Products_Translations:
- id;
- product_id;
- name;
- description;
- language_id;

But there are 2 things that im trying to do. first thing is that i have a page that i retrieve all my products, and could do it easelly creating in my Model Product the relation with the "Products_Translations" and get the information, than would be just a case of working with it in a foreach loop, but i think a better scneario would be having all the products information in the same query and than retrieve all the information one a collection of objects where i would have from the "product" table the 'online', 'price' and from the table products_translation the 'name', 'description' and 'language_id'.

My previous code was like

Controller:

$products = Product::all();

View:

 @foreach($products as $product)

                                @foreach($product->translatedProducts as $prod)

some code

@endforeach
@endforeach

Product modal:

public function translatedProducts(){
        return $this->hasMany(ProductTranslation::class);
    }

In this approache my products information is kind of split in different places, the ideal scenario would be just in my case in the controller calling the products and receive all the collections objects with all the products details together.

Can someone could tell me whats the best approach?

mbpp

9 years ago

Level 3

I believe i found a solution, would be in my controller:

$products = DB::table('products')
            ->join('product_translations', 'products.id', '=', 'product_translations.product_id')
            ->select('products.*', 'product_translations.name', 'product_translations.description','product_translations.description','product_translations.locale')
            ->get();

But would still be better make this type of logic on the Model Product

iscromanpc

8 years ago

Level 1

Hello @mbpp.

Finally, where did you write that logic? In the controller or model?

I'm suffering with a query to 3 tables. Please, give a look to my post here and gimme some feedback from your experience:

https://laracasts.com/discuss/channels/eloquent/get-data-from-3-tables-with-relationship

Thank you very much.

Please or to participate in this conversation.