Preloading model relations

Question

Preloading model relations

Roman Nov '14

Created Nov '14	Last Reply Nov '14	Replies 4	Views 1056	Votes 0

Roman 21.7k

Nov '14

Hello!

What i have

I have User and Cars models:

class Users extends \Phalcon\Mvc\Model
{
    public function initialize() {
        $this->hasMany('id', 'Cars', 'ownerId');
    }
}
class Cars extends \Phalcon\Mvc\Model {}

Then, if i do this:

$users = Users::query()->limit(10)->execute();
foreach ($users as $user) {
    echo "$user->name\n";
    foreach ($user->getCars() as $car) {
        echo "\t$car->name\n";
    }
}

Problem

It works perfectly, but.. in SQL log i see smth like this:

SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='users';
DESCRIBE `users`;
SELECT `users`.`id`, `users`.`name` FROM `users` ORDER BY `users`.`id` DESC LIMIT 10 OFFSET 0;
SELECT IF(COUNT(*)>0, 1 , 0) FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='cars';
DESCRIBE `cars`;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;
SELECT `cars`.`id`, `cars`.`name`, `cars`.`ownerId` FROM `cars` WHERE `cars`.`ownerId` = :0;

Question

Yep, lazy load is really cool in some cases. But sometimes it breaks all optimizations. I've read whole tutorial but not found how can i tell ORM to preload some relations in one join-by-index-query?

$users = Users::query()->limit(10)->with('cars')->execute(); // Outer using – disables lazy only for this query
$this->hasMany('id', 'Cars', 'ownerId', ['with' => true]);   // Setup-based – disables lazy for this relation forever

And i want to get here JUST ONE sql query. Or, maybe, two.. but not N+1 queries (o.0) cuz selecting 100 users with 10-20 cars will literally kill my database server.

Global

Also, my use-case is to send user data with car data in json string to client (i'm writing api). Maybe, Phalcon has much more better way to do this? Something like to already existed asArray() method but with data from specified relations?

Thank you!

RompePC · Answer 1 · 2014-11-06T03:35:51-07:00

Something exactly similar was discussed here https://forum.phalcon.io/discussion/3482/model-hierarchy-avoiding-lazy-loading#C11448

The solution will be overwritting find(), to add at the end of it the queries for loading related records, or creating a new type of finding with the same logic, and call that function instead of find().

Oleg · Answer 2 · 2014-11-06T08:14:01-07:00

You can solve this problem setters/getters use in your models. When I translated Phalcon doc to Russian, I note this in Russian phalcon doc. https://docs.phalconphp.ru/ru/latest/reference/models.html#id2

example :


<?php

class Robots extends \Phalcon\Mvc\Model
{

    protected $id;

    protected $name;

    public function getId()
    {
        return $this->id;
    }

    public function setName($name)
    {
        if (strlen($name) < 10) {
            throw new \InvalidArgumentException('Имя слишком короткое');
        }
        $this->name = $name;
    }

    public function getName()
    {
        return $this->name;
    }

    public function initialize()
    {
        $this->hasMany("id", "RobotsParts", "robots_id");
    }

    /**
     * Return "robots parts" through one query
     *
     * @return \RobotsParts[]
     */
    public function getRobotsParts($parameters=null)
    {
        return $this->getRelated('RobotsParts', $parameters);
    }

}

RompePC · Answer 3 · 2014-11-06T08:19:56-07:00

Yeah, the magic get for getting related records is also there. But it isn't automatically called, so it has also to create his personal function where he calls it.

Oleg · Answer 4 · 2014-11-06T10:47:50-07:00

Public properties in model isn't best practice. If you will be use setters/getters we obtained more security code. For the relations get your code don't be longer. on public


$model->relation;

on model with getters/setters


$model->getRelation();

Many developers concerned for foreign keys, but did not pay attention to make a model of the desired quality :)

Roman · Answer 5 · 2014-11-06T11:38:08-07:00

Thanks for the response, RompePC and Oleg!

RompePC, oh, i've got it. This is not implemented in Phalcon because ot enough time/money or just don't fit into frameworks philsophy?

Oleg, thank you! Your method is much more readable, cool!

In this case i'll must do smth like this:

$cars = Cars::find('color = "red"');
$result = [];
foreach ($cars as $car) {
    $owner = $car->getUsers()->toArray();
    $manufacturer = $car->getManufacturers()->toArray();
    $carArr = $car->toArray();
    $carArr['owner'] = $owner;
    $carArr['manufacturer'] = $manufacturer;
    $result[$car->id] = $carArr;
}
echo json_encode($result);

Compared with:

// together setting in Cars model
$this->belongsTo('ownerId', 'Users', 'id', ['together' => true]);
$this->belongsTo('manufacturerId', 'Manufacturer', 'id', ['together' => true]);

// Somewhere in code
echo json_decode(Cars::find('color = "red"')->toArray());

To get multidimensional array with loaded relations. Right? Maybe, Phalcon has better and more readable way to do this?

Roman · Answer 6 · 2014-11-06T12:29:03-07:00

Trying to use answer above and get something wrong.

Models

class Users extends \Phalcon\Mvc\Model
{
    public $id;
    public $name;

    public function initialize()
    {
        $this->hasMany('id', 'Cars', 'ownerId');
    }

    /**
     * @param string|array $params
     * @return Cars[]
     */
    public function getCars($params=null)
    {
        return $this->getRelated('Cars', $params);
    }
}

class Cars extends \Phalcon\Mvc\Model
{
    public $id;
    public $name;
    public $ownerId;

    public function initialize()
    {
        $this->belongsTo('ownerId', 'Users', 'id');
    }

    /**
     * @param string|array $params
     * @return Users
     */
    public function getUser($params=null)
    {
        return $this->getRelated('Users', $params);
    }
}

First test

/** @type Users[] $users */
$users  = Users::find('id IN (1,2,3)'); // Each user has more than 3 cars
$result = array();
foreach ($users as $user) {
    $cars            = $user->getCars();
    $userArr         = $user->toArray();
    $userArr['cars'] = $cars->toArray();
    $result[]        = $userArr;
}
var_export($result); // Good

It passed, cool!

Second test

/** @type Cars[] $cars */
$cars   = Cars::find('id IN (10,11,12)'); // Each car has different owner
$result = array();
foreach ($cars as $car) {
    $user            = $car->getUser();
    echo "carId: $car->id | carOwnerId: $car->ownerId | userId: $user->id\n";
    $carArr          = $car->toArray();
    $carArr['owner'] = $user->toArray();
    $result[]        = $carArr;
}
var_export($result); // Eh.. nope (o.0)

Result of second test was:

carId: 10 | carOwnerId: 1 | userId: 1
carId: 11 | carOwnerId: 2 | userId: 1
carId: 12 | carOwnerId: 3 | userId: 1

Why user id is always from first car?