CODEWITHSUNDEEP
phpmysqlyii2
Jane08
Jane08

Reputation: 127

Yii2 using "AS variable" in sql query and display it in view

dear colleagues! I have query:

$sql = "SELECT node.name,node.node_id,node.lft,(COUNT(prnt.name) - 1) AS depth
            FROM nested_category AS node,
            nested_category AS prnt 
            WHERE node.lft BETWEEN prnt.lft AND prnt.rgt 
            GROUP BY node.name
            ORDER BY node.lft";
                    $item = NodeMenu::findBySql($sql)->all();

I can display node.name like this:

foreach($item as $it =>$i)
    {
       echo $i->name;
    }

The qestion is how can I display depth? When I'm trying to do the same as with node.name:

foreach($item as $it =>$i)
    {
       echo $i->depth;
    }

I get errorException: Array to string conversion. print_r($item); doesn't contain depth. Please help me out, I couldn't find the solution)

Upvotes: 0

Views: 1408

Answers (3)

topher
topher

Reputation: 14860

There is no variable or magic attribute depth in your model. Add one and it will be set when models are being populated from the result of the query.

class NodeMenu extends ... {
    public $depth;

Upvotes: 1

Pavel Bariev
Pavel Bariev

Reputation: 2626

The problem is: when you get array with findBySql()->all() you get an array of ActiveRecord models. And your model does not have 'depth' attribute as it is dynamically added from sql query. I think you've got two ways here:

  1. Get array of plain db data using 'asArray()' method:

    $items = NodeMenu::findBySql($sql)->asArray()->all();
foreach ($items as $item) {
   echo $item['depth'];
}

    The negative side is: this will give you db data only but not models. Notice how we treat $item as array now.

  2. If you need models you should only add 'depth' attribute into model class body:

    public $depth;

    That's all! All your other code will work fine. The negative side: if you want to add many calculated fields to sql query you have to fill your model with additional attributes you don't need elsewhere.

Upvotes: 0

Rich Sala
Rich Sala

Reputation: 93

When you say as depth you are naming the table produced by the select and not (COUNT(prnt.name) - 1). You need this and use d in your display instead of depth:

$sql = "SELECT node.name,node.node_id,node.lft,(COUNT(prnt.name) - 1)d AS depth
            FROM nested_category AS node,
            nested_category AS prnt 
            WHERE node.lft BETWEEN prnt.lft AND prnt.rgt 
            GROUP BY node.name
            ORDER BY node.lft";
                    $item = NodeMenu::findBySql($sql)->all();

Or you could just remove the as and keep your display statement the same:

$sql = "SELECT node.name,node.node_id,node.lft,(COUNT(prnt.name) - 1) depth
            FROM nested_category AS node,
            nested_category AS prnt 
            WHERE node.lft BETWEEN prnt.lft AND prnt.rgt 
            GROUP BY node.name
            ORDER BY node.lft";
                    $item = NodeMenu::findBySql($sql)->all();

Upvotes: 0

Related Questions