Conditional Unique table column in laravel migration

hi, I'm quite new to laravel. I have a problem with my data but I dunno how to deal with it, so here are my questions.

let say, I have branch and greenhouse table that has one to many relationship, so 1 branch have many greenhouses.

branches
| id |
| branch_name |

and

greenhouses
| id |
| greenhouse_code |
| branch_id |

when I create a new greenhouse_code that must be unique let say GH1 for branch_id 1, I cant create another GH1 but in a different branch_id.

I want my greenhouse_code to be unique for every 1 branch_id. For example, if I have code greenhouses GH1 and GH2 on branch_id 1, I will no longer be able to create GH2 in the same branch_id except GH3 and so on, but I can create GH1, GH2, and so on in branch_id 2 or in a different branch_id.

how do I do this? thanks in advance.

lbecket

4 years ago

Level 39

It sounds like you're describing a many-to-many relationship, not a one-to-many. If a branch can have many greenhouses, but a given greenhouse can also be associated to many branches, then this is many-to-many and you'll need a pivot table to relate the two. So, branches table contains unique branches, greenhouses table contains unique greenhouses, and branches_greenhouses contains the respective foreign keys from each to establish the relationships.

This is documented here: https://laravel.com/docs/master/eloquent-relationships#many-to-many

1 like

diazfarindra

4 years ago

Level 1

@lbecket thanks for answering the question, but I know when to use many-to-many. in my case, I just want one branch that has many greenhouses, not greenhouse has many branches too.

siangboon

4 years ago

Best Answer

Level 54

I think you can just see the one-to-many example from the documentation, just in additional step would be create a custom rule to checking the existing code in that particular branch for store and update, the rest of the relationship query should be no different as you are binding the relationship with the branch id and greenhouse id, not the code....

can refer the custom rule or closure https://laravel.com/docs/8.x/validation#custom-validation-rules

1 like

diazfarindra

4 years ago

Level 1

@siangboon I think that's a pretty good idea, thanks for answering the question, I will give it a try

diazfarindra

4 years ago

Level 1

@siangboon now its work, I found that in the database, we cant make conditional unique constraint, so laravel validation solve this problem, thanks

Please or to participate in this conversation.