Loading all related objects with PHQL in a single query

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?

Check the docs here: https://docs.phalconphp.com/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());
    }
}


4.5k
edited Mar '16

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

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.



44.7k

Caching should be efficient but you could also try extracting all of the keys from users in PHP and then performing the single SQL call to Media table and then regrouping them with PHP and then feeding each User's Media records into each User.