Constraining a pivot realtionship?

I have a drinks table and a suppliers table, there is a many to many relationship between them, with a drink_supplier table.

The drink_supplier table has an extra field on it called role. This is a FK, to a roles table. The roles table has a title field on it.

In Laravel, I wish to write a constrained relationship on the Drink model so that I can get the suppliers of the drink but when the supplier acted in a certain role.

Drinks

id| title
1 | Cola

Suppliers

id| title     |
1 | Acme Inc. |

Roles

id| title
1 | Producer
2 | Reseller

Drink Supplier

drink_id | supplier_id | role_id
1        | 1           | 2

Drink Model:

public function suppliers(): BelongsToMany {
    return $this->belongsToMany(Suppliers::class)
}

public function resellers(): BelongsToMany
{
     return $this->suppliers()->wherePivot('supplier_role_id', 2);
}

The above is fine, but I want to be able to amend the resellers relationship, so instead of passing an int I can pass the string Reseller.

How can I do this inside of the relationship definition?

I can't seem to find anyway to constrain a pivot table, using a relationship defined on the pivot table itself.

Tray2

2 years ago

Level 73

Pivot tables usually only contains the foreign keys linking them together with the id in the source table. Since you only have two roles, you can either define a constant containing the values.

define("PRODUCER", 1);
define("RESELLER", 2);

Then you can use them in your code instead of the id.

echo PRODUCER;

Or you can use an ENUM

https://www.atatus.com/blog/enums-in-php/

panthro

2 years ago

Level 4

@Tray2 That could get rather messy, defining the data in two places (DB and App).

Tray2

2 years ago

Level 73

@panthro Depends on how many values you got.

You can always define them in the model

class Role extends Model
{
	const PRODUCER = 1;
    const RESELLER = 2
}

Then you can just

echo Role::PRODUCER;

Please or to participate in this conversation.