Hello, in my project, I have two various associations defined for my people table. The situation is as follows:
Each Ruling, which is issued by the authority, must have either Supervisor or Warrantor or both.
I have defined associations as follows:
PeopleTable.php:
RulingsTable.php:
Of course, due to belongsToMany associations, I have also two other tables:
rulings_supervisors (id, ruling_id, supervisors_id)
and
rulings_warrantors(id, ruling_id, warrantor_id)
Example:
My question is, is there is a possibility to create a virtual table, a join of rulings_supervisors and rulings_warrantors, i.e.:
rulings_supervisorsorwarrantors table(id, ruling_id, supervisorsorwarrantor_id)
and add another association like this without creating a real table join in my database:
PeopleTable.php
RulingsTable.php
Thank you very much!
Without going too deep in here I can definitely tell you, that you have a problem in your PeoplesTable
You can’t have multiple associations with the same name.
What I am referring to is your Rulings associations.
You need to use custom names for each of these associations like
$this->belongsToMany('WarrantorRulings', []);
$this->belongsToMany('SupervisorRulings', []);
$this->belongsToMany('SupervisorWarrantorRulings', []);
and then you can refer to those specific associations via e.g.
->contain(['WarrantorRulings']);
or whatever specific association you need.
The reason why the docs always use the table class name as a default is because then you don’t have to specify the className if the association name is the same as the table class name.
But since you already do that you can use custom association names.
Dear @KevinPfeifer thank you very much! I think you have found very important bug in my code. I will fix it immediately.
Nevertheless, I would like to ask you if there is a way to create a virtual table rulings_supervisorsorwarrantors as a join of my rulings_supervisors and rulings_warrantors tables? I.e. I do not want to create table physically in my database but let’s say I would like to create just a “read-only” join of my tables, like shown in my first post in this topic.
Thank you very much once more!
Now lets think about that… What would a “virtual table” help you here?
Generally speakting the junction table inside a belongsToMany association connects TableA with TableB so that TableA hasMany TableB but also the other way arround.
How would you define the IDs inside your “virtual table” to connect the entries from TableA to TableB? This doesn’t make sense at all and there is some major logic problem in how you want to structure this.
I do not want to create table physically in my database but let’s say I would like to create just a “read-only” join of my tables
Associations inside CakePHP are only a “representation” of how you can connect tables with each other so the correct SQL is generated. Try to think in a pure SQL way and see what you need from a database schema perspective first to achieve your goal.
After that we can then go on to see how this needs to be done in CakePHP.
CakePHP is NOT a solution arround database schema problems!
Thank you very much for your answer. Now it is all a bit clearer …
Simply said in MySQL, I need to create this virtual table:
And then use it for my association between Rulings and People …
Or is it possible to define sth like this? Simply said to define join table directly in association?
Hello one more time,
please why did you choose single form of Warrantor and plural form of Rulings?
$this->belongsToMany('WarrantorRulings', []);
$this->belongsToMany('SupervisorRulings', []);
$this->belongsToMany('SupervisorWarrantorRulings', []);
What would be the difference if I had used:
$this->belongsToMany('WarrantorsRulings', []);
$this->belongsToMany('SupervisorsRulings', []);
$this->belongsToMany('SupervisorsWarrantorsRulings', []);
?
Maybe I missed or forgot some basics. If so, I am sorry in advance.
