Query, Join and accessing results. Not performing single query.

Question

Query, Join and accessing results. Not performing single query.

Henrique Otavio Feb '16

Created Feb '16	Last Reply Feb '16	Replies 3	Views 693	Votes 0

Henrique Otavio 3.8k

Feb '16

Hello, I'm here because I can't figure out how to manage this. It's more like an improvement on application, that is working as planed. But it's performing more than desired number of SQL queries.

Thats why I want to use join to reduce the number of queries. Supposing that I have the code:

Assuming that I have a well configured Robot Model, with all hasMany and belongsTo defined...

On controller listAction(), I have the code to fetch all the Robots:

$robotsQuery = Robots::query();
$robotsQuery->join("RobotBrand"); //The related brand of the robot, oneToMany 1..N

$this->view->allRobots = $robotsQuery->execute(); // Passing all the robots to the view

And then In the view, I list the robots:

<ul>
{% for robot in allRobots %}
    <li>{{ robot.name }} - {{ robot.RobotBrand.name }}</li>
{% endfor %}
</ul>

This code worked well, but when I took a look at the log of database executed queries, I notice that for each robot, another Query to select the brand was executed.

What am I doing wrong. The desired effect would be to bring together with each robot, at least the one to many related data.

With that I would be able to execute a single query to bring all the robots with brand data included.

Thankyou !

Henrique Otavio
3.8k

Accepted
answer

Feb '16

I'm using version 2.0.9. @jymboche when you access the content of $qb->Office->something. How many SQL is executed behind the scenes !? In my case, when I do this kind of thing, for each result (that I access this way $aModel->anotherModel) behind the scenes a SELECT is executed to get data from another model. That was the point of the question.

In futher study, I noticed that I was misunderstanding the use of Models.

"If you want to access $aModel->AnotherModel->somePropertie of course you want all the data of AnotherModel."

For listing purposes, on a table for example, you may not need all the data from the related model. So:

$robotsQuery = Robots::query();
$robotsQuery->columns("id_robot, name, RobotBrand.name as robot_brand"); //List all columns that will be shown on view
$robotsQuery->join("RobotBrand"); //The related brand of the robot, oneToMany 1..N
$this->view->allRobots = $robotsQuery->execute();

On view

<ul>
{% for robot in allRobots %}
    <li>{{ robot.name }} - {{ robot.brand_name }}</li>
{% endfor %}
</ul>

This is the right way to get data for listing without consuming too much from database unnecessarily.

Thankyou !

jymboche · Answer 1 · 2016-02-11T14:29:49-07:00

I'm on 1.3x and always do joins with the query builder:

        $qb = $this->modelsManager->createBuilder()
            ->columns(["o.*", "c.*"])
            ->from([
                "c"=>"Common\\Models\\Company"
            ])
            ->leftJoin("Common\\Models\\Office",
                "c.id = o.companyId",
                "o");

        $rows = $qb->getQuery()->execute();

jymboche · Answer 2 · 2016-02-11T17:12:20-07:00

The above example i posted executes one query and the models are accessible via:

    foreach($rows as $row) {
        $row->o->id;
        $row->c->id;
    }

But yes, you can limit the columns by specifying them in the columns array. Generally a single query is preferred, especially when your database isn't on the same server.

Wojciech Ślawski · Answer 3 · 2016-02-13T04:42:51-07:00

You are joining RobotBrand, but you dont select any data from it. So when you access columns in view phalcon need to do query.

Henrique Otavio · Answer 4 · 2016-02-13T05:14:28-07:00

Thanks @Jurigag. I understood at the end what phalcon does behind the scenes, and it really makes sence. By trying to access the robot.RobotBrand I`m saing "hey give me all the data from RobotBrand". But thanks, this answers the question.