Search code examples
phplaravelfluenteloquent

Laravel - Eloquent joining table with intermediary table

I'm trying to join tables using intermediary table, for example: I have portfolios table and packages table each row of the table will have many imgs which is stored in imgs table, I want to try make an intermediary table to join them so the imgs table will only have 2 collumns, here's the details :

portfolios table

portfolio_id
portfolio_name
description

packages table

package_id
package_name
price

imgs table

img_id
img_name

intermediary_imgs table

img_id
intermediary_id
portfolio_id
package_id

the if the img is only for portfolio, then in the intermediary_imgs table, package_id collumn will has value 0

I have achieved this using Fluent Query Builder :

DB::table('portfolios')
        ->select('portfolios.portfolio_id', 'portfolios.portfolio_name', 'imgs.img_id', 'imgs.img_name','imgs.intermediary_id')
        ->join('intermediary_imgs', 'portfolios.portfolio_id', '=', 'imgs_pivot.portfolio_id')
        ->join('imgs', 'intermediary_imgs.intermediary_id', '=', 'imgs.intermediary_id')
        ->where('portfolios.portfolio_id', '=', $id)
        ->get();

then I try to do it in Eloquent and its not working, what I have tried :

//i already specify the relationship in the each model(Portfolio, Packages, Intermediary, Img)
Portfolios::with('imgsIntermediary')->get()

then it will return all the intermediary_table data that references on specific portfolio_id

but I need to do another query to select imgs from the imgs table that fits with intermediary_id

is it a good practice to always do this? how to make this better?

note

im using intermediary table so when I need to create another table that needs img then I can just add collumn in the intermediary table with the table_id

ps: sorry for the bad english

Solution

  • I really would create a different table each time I need a different relationship. It's clean. Here, I would first of all separate the relationship between packages/imgs and portfolios/imgs into two different pivot tables: imgs_packages and imgs_portfolios.

    You will need to ensure that imgs and its singular form (img) are added to strings.php as they are not words in the English language. You will also need to ensure you have defined your relationships correctly in the Schema builder. (See: http://three.laravel.com/docs/database/schema) Also, this will be helpful: http://codehappy.daylerees.com/eloquent-orm

    I assumed you are using Laravel 3. I also assumed you have your migrations and models set up correctly. (If my answer isn't satisfactory, it's probably because the question doesn't include as much information as would be necessary)