Yii2在sql查询中使用“AS变量”并在视图中显示它

zhuan:https://ask.csdn.net/questions/866034

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();
	{1}

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)

 

•  点赞
• 写回答
• 关注问题
• 收藏
• 复制链接分享
• 邀请回答

3条回答

•  dongxing8766 6年前

  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;

   点赞评论复制链接分享

   
•  duanlingzei0170 6年前

  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.

   点赞评论复制链接分享

   
•  doudou3716 6年前

  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();
  	{1}

  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();
  	{1}