Simple joins not working properly

Question

Simple joins not working properly

nazwa Sep '14

Created Sep '14	Last Reply Sep '14	Replies 2	Views 707	Votes 0

nazwa 15.1k

Sep '14

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.

Phalcon
98.9k

Accepted
answer

edited Sep '14
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>';

Phalcon · Answer 1 · 2014-09-28T18:00:12-07:00

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

nazwa · Answer 2 · 2014-09-28T18:47:45-07:00

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?

nazwa · Answer 3 · 2014-09-28T21:13:38-07:00

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!