Loading all related objects with PHQL in a single query

Question

Loading all related objects with PHQL in a single query

yossiha Mar '16

Created Mar '16	Last Reply Mar '16	Replies 2	Views 911	Votes 0

yossiha 7.7k

Mar '16

Hey,

I am trying to get objects that are connected by a relationship in a single query with the builder, and I wonder whether it's possible or not.

Let's say that I have a model called "users" and each user can have many "media" records. I'm trying to get a row from a query, where each row has all of the user's media records attached, instead of multiple rows that each contain the user data and the media information.

        $objects = $this->modelsManager->createBuilder()
            ->from('Users')
            ->join('Media')
            ->where('Users.id=1 AND Media.user_id=Users.id')
            ->columns(array('Users.*', 'ProfilesMedia.*'))
            ->getQuery()
            ->execute();

            foreach($objects as $object) {
            //access $object['Media'][0]['id']
            //access $object->id
            }

I could use groupBy for media.id and sort with user.id, and then do some processing to the data..

What's the best practice method to approach this?

Lajos Bencz · Answer 1 · 2016-03-07T12:31:36-07:00

Check the docs here: https://docs.phalcon.io/en/latest/reference/models.html#defining-relationships

class Users extends Model
{
    public function initialize() {
        $this->hasMany('id','Media','user_id');
    }
}

class Media extends Model
{
    public function initialize() {
        $this->belongsTo('user_id','Users','id');
    }
}

$objects = $this->modelsManager->createBuilder()
        ->from('Users')
        ->where('Users.id=1')
        ->getQuery()
        ->execute();
// or just
$objects = Users::find("id%2=0 OR whatever='condition'");

foreach($objects as $user) {
    foreach($user->Media as $media) {
        var_dump($media->toArray());
    }
}

yossiha · Answer 2 · 2016-03-07T13:23:09-07:00

The problem with this approach, as I last tested it, is that for every related object you get a query. This will result in 1 query for the users, and another query for each user (or in the worse case, a query for every media object..)

I am trying to avoid multiple simple queries to the database.

Please check this:

By showing 50 products on page - it get more +2 extra queries inside cycle, so in total there are 150 rows and cool object: php $products = Products::find(); foreach ($products as $product) { $product->name; $product->images->filename; $product->specs->volume; }

from this thread

Lajos Bencz · Answer 3 · 2016-03-07T15:06:29-07:00

Sorry, haven't fully understood your problem. Unfortunately, there is no way to pull in ORM sub-results with a single select...

If performance is the goal, start using cached results. Having separate sub queries actually makes it more efficient for caching, since its broken down into smaller pieces.

If data consistency is important, you could apply some form of locking, depending on your db backend and engine.