Models Relationships vs JOIN

Hello

I saw that there are some models relationships (http://docs.phalconphp.com/en/latest/reference/models.html#relationships-between-models) in Phalcon framework, so I tested it and saw in profiler that it makes child queries inside cycle. So it makes nice object but also tons of simple queries.

Is there are some alternative methods that use id collector and find them with IN SQL operator? And what are suggestions (maybe benchmarks?) about using table-relations or JOIN'ing.

Cheers.



7.2k

I also found this behavior. Good idea for developers - implementation lazy queries. I create manually such requests through PHQL now, unfortunately.



7.2k

I was wrong! RTFM ( it's for me)

Lazy load solution For example: - we have table Discussion, and Discussion has many Replays. ```php $discussion = Discussions::findFirst([ 'conditions' => 'id=2' ]); $replays = $discussion->getReplays(); ``` Queries (log from MariaDB)

140114 16:55:22 14 Connect [email protected] as anonymous on forum 14 Query SELECT discussions.id, discussions.title, discussions.topic, discussions.categories_id, discussions.users_id, discussions.created_at, discussions.slug FROM discussions WHERE discussions.id = 2 LIMIT 1 14 Query SELECT replays.id, replays.body, replays.discussions_id, replays.users_id, replays.created_at, replays.updated_at FROM replays WHERE replays.discussions_id = '2' 14 Quit

```
Query

php $d = Discussions::findFirst([ 'conditions' => 'id=2' ]); $replays = $d->getReplays(); echo '<pre style="font-size:11pt">'; vardump($replays->toArray()); echo "</pre>"; ``` Result ```php array (size=3) 0 => array (size=6) 'id' => string '10' (length=2) 'body' => string 'message to discussion ' (length=22) 'discussionsid' => string '2' (length=1) 'usersid' => string '7' (length=1) 'createdat' => string '0' (length=1) 'updatedat' => string '0' (length=1) 1 => array (size=6) 'id' => string '11' (length=2) 'body' => string 'message to discussion ' (length=22) 'discussionsid' => string '2' (length=1) 'usersid' => string '11' (length=2) 'createdat' => string '0' (length=1) 'updatedat' => string '0' (length=1) 2 => array (size=6) 'id' => string '13' (length=2) 'body' => string 'message to discussion ' (length=22) 'discussionsid' => string '2' (length=1) 'usersid' => string '10' (length=2) 'createdat' => string '0' (length=1) 'updated_at' => string '0' (length=1) ``` Log ```php ``` 140114 17:18:14 22 Connect [email protected] as anonymous on forum 22 Query SELECT discussions.id, discussions.title, discussions.topic, discussions.categories_id, discussions.users_id, discussions.created_at, discussions.slug FROM discussions WHERE discussions.id = 2 LIMIT 1 22 Query SELECT replays.id, replays.body, replays.discussions_id, replays.users_id, replays.created_at, replays.updated_at FROM replays WHERE replays.discussions_id = '2' 22 Quit



7.2k

In continuation. I read the manual again :) We will automatically solve the problem of lazy loading, if we use stters & getters in model. Like : ```php <?php

namespace Forum\Apps\Models;

use Forum\Apps\Library\Aux as Aux;

/** * Description of Discussions * * @author oleh */ class Discussions extends \Phalcon\Mvc\Model {

/**
 * @Primary
 * @Identity
 * @Column(type="integer", nullable=false)
 *
 * @FormOptions(type=hidden)
 */
protected $id;

/**
 * @Column(type="string", nullable=false)
 *
 * @FormOptions(type=text, length=128)
 */
protected $title;

/**
 * @Column(type="string", nullable=false)
 *
 * @FormOptions(type=textarea)
 */
protected $topic;

/**
 * @Column(type="integer", nullable=false)
 *
 * @FormOptions(type=hidden)
 */
protected $categories_id;

/**
 * @Column(type="integer", nullable=false)
 *
 * @FormOptions(type=hidden)
 */
protected $users_id;

/**
 *
 * @Column(type="integer", nullable=false)
 *
 * @FormOptions(type=hidden)
 */
protected $created_at;

/**
 *
 * @Column(type="string", nullable=false)
 *
 * @FormOptions(type=hidden)
 */
protected $slug;

/**
 * get id
 * @return int 
 */
public function getId() {
    return $this->id;
}

/**
 * Get title
 * @return string
 */
public function getTitle() {
    return $this->title;
}

/**
 * Get Topic
 * @return string 
 */
public function getTopic() {
    return $this->topic;
}

/**
 * Get categories_id
 * @return int 
 */
public function getCategoriesId() {
    return $this->categories_id;
}

/**
 * Get user_id
 * @return int 
 */
public function getUserId() {
    return $this->users_id;
}        

/**
 * get created_at
 * @return  date
 */
public function getCreatedAt() {
    return $this->created_at;
}

/**
 * get slug
 * @return string 
 */
public function getSlug() {
    return $this->slug;
}

/**
 * Set id
 * @return int 
 */
public function setId($id) {
    $this->id = $id;
}

public function setTitle($title) {
    $this->title = $title;
}

public function setTopic($topic) {
    $this->topic = $topic;
}

public function setCategoriesId($categories_id) {
    $this->categories_id = $categories_id;
}

public function setUserId($users_id) {
    $this->users_id = $users_id;
}        

public function setCreatedAt($created_at) {
    $this->created_at = $created_at;
}

public function setSlug($slug) {
    $this->slug = $slug;
}


public function initialize() {
    $this->belongsTo('categories_id', "Forum\Apps\Models\Categories", "id", [
        'alias' => 'category',
    ]);
    $this->belongsTo('users_id', "Forum\Apps\Models\Users", "id", [
        'alias' => 'author',
    ]);
    $this->hasMany('id', 'Forum\Apps\Models\Replays', 'discussions_id', [
        'alias' => "replays"
    ]);
}

public function beforeValidation() {

    $this->setCreatedAt(time());
    $this->setSlug(Aux::slugMake($this->getTitle()));
}

public function getReplays($parameters=NULL) {

    return $this->getRelated('replays', $parameters);

}

public function getCategory($parameters=NULL) {

    return $this->getRelated('category', $parameters);

}


public function getAuthor($parameters=NULL) {

    return $this->getRelated('author', $parameters);

}

} ``` Ars longa, vita brevis :)

Your example is about one-to-many which is ok in this case. But imagine the next situation, here are 3 tables:

products
product_images
product_specs

And there are one-to-one relationships in models of this 3 tables.

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; } ```

Or just create custom method in Products Model and JOIN all necessary tables:

$products = Products::getComplete();
foreach ($products as $product)
{
    $product->name;
    $product->imageFile;
    $product->specVolume;
}


7.2k

Yes, in this case it holds. But, in my opinion, we can choose another way. We may create the special methods with our quiries in model. We may use Query Builder or PHQL in these methods. These methods can return one of Phalcon\Mvc\Model\Resultset. Then we will be able to collect the result of a single request. What is your opinion?