Joining tables - should I make joins in model or in controller

Hello everybody. I have a query which return results from table join, see below:


Is there any way to create a virtual model join table and then just call $direps = $this->VirtualTableModel-> find() instead of writing join function in controller?

Thank you!

What you are looking for is defining the associations between tables.
https://book.cakephp.org/3/en/orm/associations.html

Basically you can define the association between 2 tables inside the src/Model/Table/PeopleTable.php and then reuse that associations wherever you like.

In your example you first need to add a hasMany() association between people and direps_rulings

    $this->hasMany( 'DirepsRulings', [
      'foreignKey' => 'ruling_id',
      'joinType' => 'INNER'
    ] );

And inside your Controller then can use that via

    $this->People->find()->contain(['DirepsRulings'])->all();

If you then add a similar association in your direps_rulings Table Class between your direps_rulings and ruling table you can then use that as well via

$this->People->find()->contain(['DirepsRulings' => ['Rulings']])->all();

So you always start with a Model $this->People and then define which directly related data you want.

$this->People->find()->contain(['DirepsRulings'])->all();

Loads only People which have related DirepsRulings (because its an INNER JOIN)

$this->People->find()->contain(['DirepsRulings' => ['Rulings']])->all();

The inner array is because Rulings is only related to the DirepsRulings Table but not to the People Table directly.

And to answer your question: Usually in a MVC framework one goes by the rule “fat models, skinny controllers”.

But model code should not contain “business logic”, this should still be in the controller (or if its too much in a separate service/utility class)

vs.

The Model layer represents the part of your application that implements the business logic.

Bit confused now :slight_smile:

1 Like

To be fair this topic is very opinionated because this is more about software design and how you structure your code. Both ways work and either one has its advantages and disadvantages.

I was going into the direction of what the service-layer plugin is talking about.
See GitHub - burzum/cakephp-service-layer: Service layer implementation for CakePHP

1 Like

But still your joins should be defined in your model so you can more easily re-use them wherever you need them :wink:

As I ‘grew up’ with the 3-tier architecture, I always/still have trouble where to put the business logic in MVC, so I’m always triggered when reading about this topic.

In my mind joins are definitely ‘model’, so I’m glad that’s not giving me a problem. Moreover, because I ‘bake’, joins get in the model by default.

Thank you very much KevinPfeifer. Your example is exactly what I was searching for. I like the “fat models, skinny controllers”. One have to say that these credos usually rely on deep experience of people, who creted them. They must have sense and justification.