belongsTo from one table to multiple tables

@MohamedTammam Thank you, will have a look.

@SigalZ I agree with @mohamedtammam an item is an item regardless of it being a blend or a bean.

I suggest giving these two posts a read

The value of a good database model

Database design part two

Hi @mohamedtammam I don't 100% understand these relationships. I hope you don't mind helping me a bit more.

I don't understand how will I get the product name from the order_items table for example.

How will it know if it needs to find the name in the beans table or the blends table?

I tried a few things, and I guess you can see by my tries that I don't really understand this: 1.

//In OrderItem Model
public function product()
{
        return $this->morphTo();
 }

//In Bean and Blend Models:
 public function orderItems()
 {
        return $this->morphMany(OrderItem::class, 'product_id');
  }

I also tried:

//In Bean and Blend models:
 public function orderItems()
 {
        return $this->morphMany(OrderItem::class, 'product');
  }

Going through all the order_items for an order and try to get the product's name:

$items = OrderItem::where('order_id', 1)->get();

foreach($items as $item) {
       echo "<p>" . $item->product->name . "</p>";
}

I also tried:

foreach($items as $item) {
       echo "<p>" . $item->product . "</p>";
}

With all the tries I get this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_items.' in 'where clause' (SQL: select * from order_items where order_items.`` = 2 limit 1)

Can you please help?

@Tray2 Thank you, but I did not say an item is not an item. I asked how do I get the item's name when an item can be in 2 different tables. It can be in a table called beans and in a table called blends.

@SigalZ Make sure to add item_id and item_type columns in your order_items table.

If you want to make their names become product_id and product_type you can do it like that.

//In OrderItem Model
public function product()
{
        return $this->morphTo(__FUNCTION__, 'product_type', 'product_id');
 }
//In Bean and Blend Models:
 public function orderItems()
 {
        return $this->morphMany(OrderItem::class, 'product');
  }

@SigalZ First you need to change order_items table column to support polymorphic relationship. In your migration file, you need to update, product_id column to like this below

$table->morphs('productable', 'produtable_index');

It will generate two columns, producatable_type, productable_id.

1. productable_type column store model,
2. productable_type column store model primary key.

In your 'OrderItem` model, add relationship like this

public function productable()
{
     return $this->morphTo();
}

In your Bean & Blend models, add relationship like this

public function OrderItems(): MorphMany
    {
        return $this->morphMany(OrderItem::class, 'produtable');
    }

If order_id is primary key in you order_items table, then you can use it with eargar loading to fetch related tables data also.

OrderItem::with('produtable')->firstWhere('order_id', 1);

Hope this will help you.

@SigalZ It should only be one table, to keep it simple.

@Tray2 Thank you, but I'm not sure it will make things simpler.

Is there a way I can contact you privately?

@SigalZ Read the One to many with different properties here https://tray2.se/posts/database-design-part-2 and it will give you a hint on how to use a single table.

@Tray2 Thank you but I'm interested in a personal online help with my database and the issues I am facing.

That's why I wanted to chat with you privately and see if you are interested in a paid sessions.

@Tray2 hi again. I read your article. Very well written, thank you very much. But, I now have new questions. I hope nobody minds me asking here.

What do you do when some of your products have relationships with other tables. Then you can't store it as property/value.

For example, I have bean product that has relationship to roast_types, countries and stickers tables.

Blend product that has the same fields as bean but not the roast_types and countries relationships.

And Coffee_flask that does not have relationship to any table.

So I created product types table as well now and a beans table but I'm getting all mixed up again.

Also, how do you create a form for the user to create a product when you don't know which fields each product has. Product_properties table doesn't specify the field types.

So, I guess you can see, I'm getting very confused trying to use your way.

Thank you for taking the time to read my questions and answering.

@SigalZ I would probably create one table with allowed properties for each kind of product and then keep the product properties like described above.

Otherwise you could use a polymorphic relationship between the product and any tables related to each of them.

https://laravel.com/docs/9.x/eloquent-relationships#polymorphic-relationships

@Tray2 Thx, I'm not sure I'm following you.

one table with allowed properties for each kind of product and then keep the product properties like described above.

I have products table. Keep the product properties like described above? But then again, where do I keep the relationships fields.

Agh, never mind, Thx for trying to help.

@Maria30 Thank you, will check it.