Many-to-many relationships using a pivot table w/ natural foreign keys?

replied 9 years ago

I can't quite see in your tables how they are being related.

Might be teaching you to suck eggs, but...

Pivots want to have one field form one table and another from the other. Eg,

vendor_id and service_id

That way the can reference each other. Does that make sense? The ids are noted in mysql (for example) as primary keys and are indexed. If you go down the route you have, you'll probably need to make sure the keys you want to match on are indexed in mysql.

Last updated 2 years ago.

0

djtechonline

replied 9 years ago

First and foremost, thanks T2theC for your suggestion. I tried to add indexes to the vendors.code and services.code fields, but that did not work. Secondly, I apologize if this sounds confusing. I'm trying to avoid any egg sucking here :)

Basically, I want to get a list of service names (titles) for a particular vendor, which are related in my vendor_services table. This table holds the vendor "code" from the "vendors" table and the service "code" from the "services" table. A vendor can have many services and these services may be used by many vendors. I am using MySQL for this, if that matters. I want to avoid using surrogate foreign keys of IDs and want to use the natural foreign keys holding each code as a value.

So, for example if a $vendor "ABC Enterprises" with a code of "abc" provides "Consulting Services" with a code of "cs", the vendor_service table would hold "abc" and "cs" as an entry. I want to be able to get back that "Consulting Services" title through $vendor as $vendor->service->title I'm not sure how I can achieve this with an Eloquent model relationship.

Data in the vendor_services table would look something like:

vendor     service
_____      _____
abc            cs
xyz            cs
xyz            othersvc

(Sorry for the alignment issue). Hopefully you can see where I'm trying to get at with this. If anone else would like to pitch in, I'm all ears. Thanks in advance!

Last updated 2 years ago.

0

rickshawhobo

replied 9 years ago

Not sure why you don't want to use the surrogate keys. They already exist on the related tables.

Your pivot table should look like this

service_vendor (note the singular form and alphabetic order of the table name - this is the laravel convention)

It should have 2 fields

service_id
vendor_id

and it should be unique(service_id, vendor_id) in the schema

In the Vendor model...

return $this->belongsToMany('Service')

In the Service model...

return $this->belongsToMany('Vendor')

$services = Vendor::find(1)->services  => returns  a collection

foreach ($services as $service) {
echo $service->name;
}

Last updated 2 years ago.

0

djtechonline

replied 9 years ago

I appreciate the reply, rickshawhobo, but your solution goes back to using surrogate keys. The second parameter can be used to specify a pivot table name, should it not follow the Laravel naming convention, and the 3rd and 4th parameters are used to specify the foreign keys as in:

/* This does not work */
return $this->belongsToMany('Service','vendor_services','vendor','service'); //natural keys
/* This does, but is not  the desired key type */
return $this->belongsToMany('Service','vendor_services','vendor_id','service_id'); //surrogate keys

I still have not been able to get this to work with natural keys. I've tried removing the vendor_id and service_id from their parent tables' schemas and making their codes field the primary key in each, but I still could not get it to work. If I use the surrogate keys, however, it works as you indicate... but I am trying to use natural keys.

Is it not possible to use natural keys in Laravel?
Has anyone tried this successfully?

Last updated 2 years ago.

0

djtechonline

replied 9 years ago

Solution

I finally found the solution I was looking for. What I ended up doing was removing my auto_increment id fields from the vendors and services tables. I then changed the "code" field of each table to become the new PRIMARY KEY. Next, in my Vendor model and Service model, I specified the primary key for each as follows:

protected $primaryKey = "code";

Now, my relationship is working as expected and I can use something like this:

$vendor = Vendor::find('abc');
foreach($vendor->services as $vs){
  echo "<div>".$vs->title."</div>";
}

This finally gives me the data I needed.

Once again, thank you T2theC and rickshawhobo for your attempts at a solution.

Last updated 2 years ago.

0

Moderators