Loading Models with INNER JOIN

Question

Let's say I have a Phalcon\Mvc\Model that I load using ::findFirst($id).

How can I swap in a custom query that would load the model row and do INNER JOIN on some other table?

Thanks!

Answer 1

I'm sure you can use the Query builder for simple joins like:

<?php

//Getting a whole set
$robots = $this->modelsManager->createBuilder()
    ->from('Robots')
    ->join('RobotsParts')
    ->orderBy('Robots.name')
    ->getQuery()
    ->execute();

//Getting the first row
$robots = $this->modelsManager->createBuilder()
    ->from('Robots')
    ->join('RobotsParts')
    ->orderBy('Robots.name')
    ->getQuery()
    ->getSingleResult();

Or PHQL example from the documentation:

<?php

$phql = "SELECT Robots.*
    FROM Robots JOIN RobotsParts p
    ORDER BY Robots.name LIMIT 20";
$result = $manager->executeQuery($phql);

By default, an INNER JOIN is assumed. You can specify the type of JOIN in the query though.

Reference: http://docs.phalconphp.com/en/latest/reference/phql.html#creating-queries-using-the-query-builder

Then I'd overload model's findFirst() method to utilize the above code and assign result values to model's properties.

Answer 2

You are able to do this, you need to use the query static method on a Model which extends the Phalcon MVC Model class.

    $followingUsers = Users::query()
        ->leftJoin('Common\WideZike\Models\UsersFollowers', 'Common\WideZike\Models\Users.id = Common\WideZike\Models\UsersFollowers.followingId')
        ->where('Common\WideZike\Models\UsersFollowers.followerId = :userId:', array('userId' => $user->getId()))
        ->orderBy('Common\WideZike\Models\UsersFollowers.addedDate DESC')
        ->execute();

Hope this helps!

Answer 3

$activations = UserActivations::query()
    ->columns("UserActivations.id")
    ->leftJoin("Deals", "d.id = UserActivations.dealId", "d") 
    ->where("UserActivations.state = 1")
    ->execute();