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.

Simple joins not working properly

I always thought that all my queries run quite smoothly, but recently started fully looking at whats happening under the hood, and realized that joins are completely ignored.

Simple example. Every contact has a target user. Here I'm trying to load that contact with corresponding user in a single query.

class Contact extends \Phalcon\Mvc\Model
{
(...)
    public function initialize()
    {
        $this->hasOne('target_id', 'User', 'user_id', array('alias' => 'Target'));
    }
}

// And then...
 $c = $this->di->getModelsManager()
            ->createBuilder()
            ->from('Contact')
            ->join('User')
            ->andWhere('owner_id = 22')
            ->getQuery()
            ->execute();
        $cc = $c->getFirst();

        echo $cc->getTarget()->getEmail();

This generates two queries - one for contact with the correct join and one for the user later on.

  • SELECT contact.contact_id, contact.owner_id, contact.tagline FROM contact INNER JOIN user ON contact.target_id = user.user_id WHERE contact.owner_id = 22
  • SELECT user.user_id, user.email, user.password, user.salt, user.name, user.verified, user.created_at, user.updated_at, user.deleted_at FROM user WHERE user.user_id = :0 LIMIT :2

Is this a bug, or am I doing it wrong? Surely a single query should suffice here.



83.4k

Not a bug:

$cc = $c->getFirst(); // returns a Contact: SELECT contact.contact_id, contact.owner_id, contact.tagline FROM contact INNER JOIN user ON contact.target_id = user.user_id WHERE contact.owner_id = 22

$target = $cc->getTarget() // returns the related target: SELECT user.user_id, user.email, user.password, user.salt, user.name, user.verified, user.created_at, user.updated_at, user.deleted_at FROM user WHERE user.user_id = :0 LIMIT :2

$email = $target->getEmail() // returns the target's email from last query


9.5k

I totally get the structure, but I thought the whole point of the join was to load the related data in a single query. Is there an easy way to achieve that or do I need to go low sql and parse all data manually?



83.4k
Accepted
answer
edited Sep '14

It doesn't work that way, you're not asking for querying the columns of the joined model:

$c = $this->di->getModelsManager()
            ->createBuilder()
            ->columns(array('Contact.*', 'User.*'))
            ->from('Contact')
            ->join('User')
            ->andWhere('owner_id = 22')
            ->getQuery()
            ->execute();

$cc = $c->getFirst();

echo $cc->user->name, '<br>';
echo $cc->contact->contact_id, '<br>';


9.5k

Ha, that's interesting. I think it would be nice to get that into the phql docs since joins are practically useless without the column criteria. Thanks for clearing it up!