Relations, Select, Aliased Column

Archives Yii 1.0.x Discussions
1

So I have a query I am trying to Yii-ify.




SELECT links.*, (SUM(votes.karma_delta) - 1) AS karma

FROM links, votes

WHERE links.id = votes.link_id

GROUP BY votes.link_id

ORDER BY (SUM(votes.karma_delta) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC

LIMIT 0, 100

My Link model has it’s proper relations, ie:




'user' => array(self::BELONGS_TO, 'User', 'user_id'),

'category' => array(self::BELONGS_TO, 'Category', 'category_id'),

'votes' => array(self::HAS_MANY, 'Vote', 'link_id'),

But I do not know how to access and/or write the SELECT section, specifically the karma which is just the sum for my votes.karma_delta.

When I run my code thus far:




$criteria = new CDbCriteria;

$criteria->select = 'links.*, (SUM(votes.karma_delta)) AS karma';

$criteria->condition = 'links.id = votes.link_id';

$criteria->group = 'votes.link_id';

$criteria->order ='(SUM(votes.karma_delta) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC';

$links = Link::model()->with('votes')->findAll($criteria);

I am greeted with a Yii error. a CDbException




Active record "Link" is trying to select an invalid column "links.*". Note, the column must exist in the table or be an expression with alias.

What am I doing wrong?

2

You need to define an alias for ‘votes’




'votes' => array(self::HAS_MANY, 'Vote', 'link_id', 'alias'=>'votes'),

/Tommy

3

Same error. My logs indicate that the query I am sending is:




SELECT (SUM(votes.karma_delta)) AS karma, `links`.`id` AS `t0_c0`, `votes`.`id` AS `t1_c0`, `votes`.`link_id` AS `t1_c1`, `votes`.`user_id` AS `t1_c2`, `votes`.`created` AS `t1_c3`, `votes`.`modified` AS `t1_c4`, `votes`.`karma_delta` AS `t1_c5` FROM `links`  LEFT OUTER JOIN `votes` `votes` ON (`votes`.`link_id`=`links`.`id`) WHERE (links.id = votes.link_id) GROUP BY votes.link_id ORDER BY (SUM(votes.karma_delta) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC

Which is valid when I enter it in phpMyAdmin.

4

Oh and my mistake, this should be 1.1x discussion.

5

Gave the below code a shot as well, still no go. I had read that perhaps putting my select in an array might help.

http://code.google.com/p/yii/issues/detail?id=648




$links = Link::model()->with('votes')->findAll(array(

	'select' => array('links.*', 'SUM(votes.karma_delta) AS karma'),

	'condition' => 'links.id = votes.link_id',

	'group' => 'votes.link_id',

	'order' => 'ORDER BY (SUM(votes.karma_up) - SUM(votes.karma_down)) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC',

));

Again, the ultimate goal is to Yii-ify this




SELECT links.*, (SUM(votes.karma_delta)) AS karma

FROM links, votes

WHERE links.id = votes.link_id

GROUP BY votes.link_id

ORDER BY (SUM(votes.karma_delta) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC

LIMIT 0, 100
6

WELL. I sort of solved my issue by findAllBySql instead of findAll and just typing out the full query. Not as classy but works. Still wonder why ‘select’ => array(‘links.*’); causes issues.

7

I wonder how you delete a reply…