Find data in related table using Phalcon ORM

Question

Can i find data on related table using Phalcon ORM like this?

SELECT * 
FROM user 
LEFT JOIN media_manager ON media_manager.user_id = user.id
WHERE media_manager.upload_date > '2017-01-01';

Previously i used this in Phalcon ORM

$data = User::find();
foreach($data as $row){
    $data->MediaManager->file_location
}

But i dont know how to find data in MediaManager like PDO above.

Answer 1

I don't think the Phalcon ORM is powerful enough to allow you to specify this sort of thing in the conditions part of a find unless you were building this sort of thing by adding a leftJoin along the way so the conditions can refer to it. You can get a bit fancy with hacks regarding things that happen in a model's initialize and adjusting Phalcon to suit your needs, but the ORM would be looping it in PHP then rather than doing this with a query. The ORM is not a replacement for SQL, and I'd strongly suggest doing this sort of thing in SQL, not looping it in PHP.

Personally I'd approach this by adding a new static method to your model where you can "find" in a special way by allowing leftJoins. This looks like a good solution to me: How to run RAW SQL query in PhalconPHP Then you'd just adjust the query there and put a LEFT JOIN.

In other words:

<?php

use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class User extends Phalcon\Mvc\Model
{
    public static function advancedFind($conditions = 1, $params = null)
    {
        $sql = "SELECT u.*,m.* FROM user u LEFT JOIN media_manager m ON m.user_id = u.id
                WHERE $conditions";

        // Base model
        $user = new User();

        // Execute the query
        return new Resultset(null, $user, $user->getReadConnection()->query($sql, $params));
    }
}  

Then you'd do something like:

$data = User::advancedFind("m.upload_date > '2017-01-01'");