laravel join 4 tables

36
March 16, 2019, at 00:30 AM

I started to learn the Laravel 5.8 framework and got a problem. The problem relates more to designing a database. But a solution is needed for Laravel (preferably Eloquent ORM).

The task is this: There is an online flower shop. The site has a category "Bouquets". Bouquets have a certain size (S, M, L ...). The size consists of the number of flowers in the bouquet, the height of the bouquet, diameter. Moreover, each size can have one name but a different number of flowers, height and diameter.

For example:

  • size- "S": count 21, height 50cm, width 40cm.
  • size- "S": count 15, height 50cm, width 30cm.

Each size can belong to different bouquets (relationship to many to many).

The administrator can choose an existing size for a new bouquet or create a new size. He sets the name, count, height and diameter. In this case, one bouquet can have several sizes ("S", "M" ...).

The bouquets also have photos that should change after choosing the size. There is also a price that also changes with size changing.

Now tables and links:

bouquets_table

id
name

sizes_table

id
name // "S", "M" ...
count
height

prices

id
price

photos

id
photo

I created a many-to-many relationship between these bouquet_size tables

bouquet_id
size_id

In models Bouquet and Size prescribed belongsToMany ().

And now, actually, the main questions are: Is it possible to add price_id and photo_id fields to the intermediate table bouquet_size and get it all through Pivot (I think this is wrong).

How to link all 4 tables so that when choosing the size of a bouquet, a photo and price for this bouquet would be selected. I consider it wrong to assign each bouquet my price and photo, as I want one bouquet to have different sizes and different photos and price depending on the size.

If it is possible in the answer, tell me how to implement all this in models (code).

Thank you very much!

Answer 1

I think you may have answered your question yourself already. :) "Is it possible to add price_id and photo_id fields to the intermediate table bouquet_size and get it all through Pivot?" Yes, it is and I think it would be the most appropriate solution here. Like you said, you "want one bouquet to have different sizes and different photos and price depending on the size," which means the the photo and price depends on the bouquet and size pair. Therefore your pivot table could contain the bouquet_id, size_id, price_id and photo_id values.

You could create a BouquetSize pivot model and define a photo and price relationship on it:

public function photo()
{
    return $this->belongsTo('App\Photo');
}
public function price()
{
    return $this->belongsTo('App\Price');
}

Then you could defined your sizes relationship on your Bouquet model as follows:

public function sizes()
{
    return $this->belongsToMany('App\Size')
    ->using('App\BouquetSize')
    ->withPivot([
        'photo_id',
        'price_id',
    ]);
}

Then you would be able to access the price of a bouquet for a size like this, for example, >>> $bouquet->sizes()->first()->pivot->price.

See the "Defining Custom Intermediate Table Models" documentation for more information.

Answer 2

I think you should modify the classes a little bit so that your app makes sense easier. Leave sizes_table, photos, prices tables as is. Replace bouquets_table with two others.

// the master/main bouquet table
main_bouquets table
id
name
// variations table for each main bouquet with the specified color, price etc
bouquet_variations table
id
main_bouquet_id
price_id
size_id
photo_id

So now you each variation of the bouquet is stored in bouquet_variations table that should be represented with a different model and inside that you can add any customisation for each side, color, price and any other attribute you might think.

It may be a bit more work for now but trust me it will help you very much as your application grows.

READ ALSO
Multiple search from the database [duplicate]

Multiple search from the database [duplicate]

This question already has an answer here:

44
in laravel 5.8 is its possible to spoof the url bar whit out reloading the page

in laravel 5.8 is its possible to spoof the url bar whit out reloading the page

Is it possible in laravel to spoof an url whit out reloading the page, for example when you have an url like this webpage/post/1 and 1 standing for the id, that you then can make it webpage/post/{Post-Name} whit out using webpage/post/#{Post-Name} whitout...

19
How to set up routing for a php api script create.php?

How to set up routing for a php api script create.php?

I'm trying to setup an url in my laravel project from which my database can receive post requests, i have this script i made in php and i put in /public folder, it works fine when hosted in xampp but when i put it in my laravel project i get a not found error this is what my php script...

23
How to provide website statistics for page visits? [on hold]

How to provide website statistics for page visits? [on hold]

I have a website written in php and mysql, the website also have an admin panel for user interfaceIs there any quick and easy way to provide a website statistics in admin panel?

38