Laravel中的三向数据透视表或复杂关系 [英] Three-way pivot table or complex relationship in Laravel

问题描述

我有两个表，分别是发货和客户.在现实世界中，可以通过三种方式将客户与货物联系起来:作为开票人，目的地和/或起点.

I have two tables, shipments and customers. In the real world, a customer can be related to a shipment in three ways: as the biller, the destination and/or the origin.

所以我的问题是，我是否有一个包含三列的数据透视表，一列用于shipping_id，一列用于customer_id，一列用于relationship_type id?还是我有单独的表格?我不确定如何最好地解决这个问题，因为这是我所遇到的第一个此类问题.

So my question here is, do I have a pivot table with three columns, one for the shipment_id, one for the customer_id, and one for the relationship_type id? Or do I have separate tables? I'm not sure how best to approach this as it's the first of it's kind that I've run up against.

推荐答案

我在几周前就遇到了这个问题，我想出了一个解决方案.

I faced this couple weeks ago and I came up with a solution.

假设一个客户可以对不同的客户具有不同的关系 出货量.

Assuming that one customer can have different relations to different shipments.

首先，您需要针对客户角色的新模型，显然该模型将是关系模型.

First of all you need a new model for customer roles obviously that model it will be Relation model.

第一种方法:您可以通过使用多个枢纽分析表来解决此问题，该枢纽分析表虽然有效，但它并不是一个很好的数据库设计.我首先像这样解决了它，但意识到它不是db的最佳选择.

First approach: You could solve this by using more than one pivot table which works but its not a good database design. I solved it first like this but realized its not optimal choice when it comes to db.

第二种方法:您可以通过将数据透视表定义为模型来解决此问题，但是即使我知道它可以工作并且可以解决问题，我也没有尝试过这种方法.

Second approach: You could solve this by defining pivot table as a model, but I havent tried that way even though I know it works and its a solution.

更好的方法:对三个模型使用一个数据透视表.在这种情况下，您必须在定义关系示例时定义数据透视表:

Better approach: use one pivot table for three models. In that case you have to define pivot table when you define a relationship example :

客户模型:

public function relations()
{
    return $this->belongsToMany(Relation::class, 'customer_relation_shippment');
}

关系模型:

public function customers()
{
    return $this->belongsToMany(Relation::class, 'customer_relation_shippment');
}

以及其他模型.

现在说您要添加与客户的关系. 让我们抓住第一位客户和第一批货，并说我们想添加一个关系作为开票人:

now lets say you want to add a relation to a customer. Lets grab first customer and first shipment and say we want to add a relation as a biller:

$customer = Customer::first();

$shipment = Shipment::first();

$relation = Relation::where('name','biller')->get();

$customer->relations()->attach($shipment->id, ['relationship_type'=>$relation->id]);

通过仅使用一个数据透视表，对诸如CRUD之类的模型执行操作当然要复杂一些，但是在数据库设计/优化方面，这当然是正确的选择！请注意，我在处理了一个类似的现实世界问题后得出了这个结论，与使用多个枢轴相比，它转变了更快的数据库交互方式.

By using only one pivot table of course its a bit more complex to perform operations towards those models like CRUD, but when it comes to database design/optimazation of course it is the right choice! Note that I came to this conclusion after dealing with a similar real world issue and it turned way more faster db interaction then using more than one pivot.

这篇关于Laravel中的三向数据透视表或复杂关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！