We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Custom Eager Loader

I am attempting to creat a custom eager loader that uses the model relationships to determine which of the hasOnes and belongsTo relationships that it needs to eagerly fetch from the database so that my query which returns 100 records each having four related models doesn't generate an additional 400 queries to fetch the related models.

It worked while I was building it, up to the point that I started to eager load the belongsTo relationships. When I define the relationships in the model's initialize function, I specify an 'eager' parameter in the options array of the relationship. if this eager parameter is set, my EagerBuilder, which extends Phalcon's Builder, adds the necessary joins.

Now, for whatever reason, I get an Unknown model or alias exception when referencing the fully qualified model name which I'm trying to add a join on. The builder generates the PHQL: 

SELECT AMAPI\Models\Note.*, AMAPI\Models\Customer.*, AMAPI\Models\Policy.*, AMAPI\Models\Notetype.*, AMAPI\Models\Employee.*, AMAPI\Models\Employee.* FROM [AMAPI\Models\Note] LEFT JOIN [AMAPI\Models\Customer] AS [customer] ON AMAPI\Models\Note.custid = AMAPI\Models\Customer.id LEFT JOIN [AMAPI\Models\Policy] ON AMAPI\Models\Note.polid = AMAPI\Models\Policy.id LEFT JOIN [AMAPI\Models\Notetype] ON AMAPI\Models\Note.notetype = AMAPI\Models\Notetype.id JOIN [AMAPI\Models\Employee] AS [touser] ON AMAPI\Models\Note.targetuser = AMAPI\Models\Employee.id JOIN [AMAPI\Models\Employee] AS [fromuser] ON AMAPI\Models\Note.user = AMAPI\Models\Employee.id WHERE (AMAPI\Models\Note.masterid = :masterid:) AND (AMAPI\Models\Note.open = :open:)

But it tells me that AMAPI\Models\Customer is unknown when it tries to execute the query. Any insight on this is greatly appreciated. I'd like to eventully get this eager loading library completed and put it on my github, because not eager loading relationships is one of the weakest parts of an otherwise phenomenal framework.

Thanks



8.5k

@spuy767 mind sharing your code, I'm also interested in this feature.



8.5k
edited Sep '14

I'm also trying to create a custom eager loader. I extended the fnd function where I can choose which relation to eager load:

//Eager load friends when retreiving users
$users = \user\User::find(array('limit' => 1000, 'width' => ['Friends']));
    public static function find($parameters = NULL){

        $result = parent::find($parameters);
        $in_parameters = [];
        foreach($result as $key => $value){
            $pointers[$value->getId()] = $key;
            $in_parameters[] = $value->getId();
        }

        if(isset($parameters['width'])){
            $modelName = 'User\User';
            $mm = Phalcon\DI::getDefault()->get('modelsManager');

            // initialize model relations metadata
            if( ! $mm->isInitialized( $modelName ) ) {
                $mm->load( $modelName );
            }

            foreach( $mm->getRelations( $modelName ) as $relation ) {
                $options = $relation->getOptions();
                $alias = isset( $options['alias'] ) ? $options['alias'] : $relation->getReferencedModel();

                if(in_array($alias, $parameters['width'])){
                    //Don't know why but their are some duplicates in the array;
                    unset($parameters['width'][array_search($alias, $parameters['width'])]);

                    $results = $mm->createBuilder()
                        ->from($relation->getReferencedModel())
                        ->inWhere($relation->getReferencedFields(), $in_parameters)
                        ->getQuery()
                        ->execute();

                    //now to we need to attach the records back to the correct models
                    foreach($results as $r){
                        //Get Id corresponding with Parent object
                        $id = call_user_func(array($r, 'get'.str_replace(' ','',ucwords(str_replace('_',' ',$relation->getReferencedFields())))));
                        //this is not correct yet. Needs to append to an array or something
                        $result[$pointers[$id]]->{lcfirst($alias)} = $r;
                    }
                }

            }
        }

        return $result;
    }

I think I did most of it correctly but when I loop the results afterwords nothing has changed. I hope someone could take a look at this.



23.6k
in reply to Boudewijn Geiger
edited May '15

I'm eager :p to solve it too. I was triying diferent methods but, as you already found, at the moment, it's only possible by overwriting find and findFirst. many static functions on model :( (by the way, it could be great to have model extending the query builder so find and findFirst are non static) At the __callstatic we can add a create instance and call method if exists....

Anyway, here is my attempt, same as yours with minor changes. What I found is that the Resultset var_dump is returning the models with the preloaded relationships but when we traverse it the results are "reset" to a simply object without the _related field.

@phalcon any clue about it? who did the ORM so we can contact and help with this?


    public static function find($parameters = NULL){

        $result = parent::find($parameters);
        $pointers = [];
        $in_parameters = [];

        foreach($result as $key => $value){
            $pointers[$value->id] = $key;
            $in_parameters[] = $value->id;
        }

        if(isset($parameters['with'])){
            $modelName = get_class();
            $mm = \Phalcon\DI::getDefault()->get('modelsManager');

            // initialize model relations metadata
            if( ! $mm->isInitialized( $modelName ) ) {
                $mm->load( $modelName );
            }

            foreach( $mm->getRelations( $modelName ) as $relation ) {
                $options = $relation->getOptions();
                $alias = isset( $options['alias'] ) ? $options['alias'] : $relation->getReferencedModel();

                if(in_array($alias, $parameters['with'])){
                    //Don't know why but their are some duplicates in the array;
                    unset($parameters['with'][array_search($alias, $parameters['with'])]);

                    $results = $mm->createBuilder()
                        ->from($relation->getReferencedModel())
                        ->inWhere($relation->getReferencedFields(), $in_parameters)
                        ->getQuery()
                        ->execute();

                    $related = array();
                    //now to we need to attach the records back to the correct models
                    foreach($results as $r){
                        //Get Id corresponding with Parent object
                        $id = $r->{$relation->getReferencedFields()};
                        $related[$id][] = $r;
                        $result[$pointers[$id]]->{lcfirst($alias)} = $related[$id];
                    }
                }
            }
        }

        return $result;
    }

P.D. Also, the other day I was reading Why Doctrine ORM is not suited for PHP and, even when I don't comulge with the opinion, I think that the point about as HTTP is an "stateless" protocol we don't really need lazy loading. But it really needs the eager loading, since we already know what we are returning on each call, no need to guess or load on demand.

I'm also trying to create a custom eager loader. I extended the fnd function where I can choose which relation to eager load: I think I did most of it correctly but when I loop the results afterwords nothing has changed. I hope someone could take a look at this.



23.6k

@spuy767 I had that problem with a custom query builder and ModelNamespaces, what I did is to use the "alias", if set, on the join conditions, as well as in the columns mehod


        $query = User::query()
            ->columns('booking.*')
            ->join('App\Club\Models\PlayingfieldBooking', 'booking.user_id = App\User\Models\User.id', 'booking')
            ->where('App\User\Models\User.id = :user_id:', array('user_id' => $this->id))
            ->andWhere('booking.start < NOW()');


23.6k

OK, so I found the trick. The ResultSet is just a pointer to the PDO result. So when we traverse it we move through the pdo result, despite what we filled or did during the find method.



6.4k

Hi, I've written this, by the moment can solve the problem