Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Model Relationships vs Joins

What is the advantage to using model relationships vs standard joins.

When using a model relationship a seperate query is is executed on the driver. In a large scale application this will create a bottle next as M^R where M is equal to the model and R represents the relationships.

The only advantage I can seem to come up with is cleaner code and ease of model access.

What am i missing?

A separate query is easier to cache, if related records don't change often, you have an opportunity to cache them.



1.7k

Could you point me in the right direction. So theoritically the relationships could be more benificial.



35.6k
Accepted
answer
$queryBuilder
   ->columns('rp.*')
   ->addFrom('Robots','r')
   ->join('RobotParts','rp.robot_id=r.id','rp')
   ->where('r.id=?0',array(1))
   ->getQuery()
   ->execute()
   ->getFirst();

This will generate a single query, but limit your caching options.

Robot::findFirst(1)->RobotParts->getFirst();

On the other hand, this will create two seperate queries, but both will be cachable independently. If you then execute something like this:

RobotParts::findFirst(array('conditions'=>'robot_id=?0','bind'=>array(1)));

No extra queries will be executed if caching is setup correctly.