Model find with "with"

Yii 1.1 General Discussion
1

Hi.

So, I’m trying to make a forum for a website I’m building but I’m kinda stuck now…


SELECT  `t`.`id` AS  `t0_c0` ,  `t`.`topic_id` AS  `t0_c1` ,  `t`.`category_id` AS  `t0_c2` ,  `t`.`subcategory_id` AS  `t0_c3` ,  `t`.`title` AS  `t0_c4` ,  `t`.`content` AS  `t0_c5` ,  `t`.`date` AS  `t0_c6` ,  `t`.`time` AS  `t0_c7` ,  `t`.`user_id` AS  `t0_c8` ,  `user`.`id` AS  `t1_c0` ,  `user`.`username` AS  `t1_c1` ,  `user`.`password` AS  `t1_c2` , `user`.`firstname` AS  `t1_c3` ,  `user`.`lastname` AS  `t1_c4` ,  `user`.`birthday` AS  `t1_c5` ,  `user`.`registered` AS  `t1_c6` ,  `user`.`last_seen` AS  `t1_c7` ,  `user`.`image` AS  `t1_c8` ,  `user`.`role` AS  `t1_c9` 

FROM  `forum_replies`  `t` 

LEFT OUTER JOIN  `users`  `user` ON (  `t`.`user_id` =  `user`.`id` ) 

AND (

`t`.`id` =  `user`.`id`

)

WHERE (

topic_id =1

)

ORDER BY DATE DESC , TIME DESC 

LIMIT 1

Is there a way to change


`t`.`id` = `user`.`id`

to be


`t`.`user_id` = `user`.`id`

? Currently I’m using following code:


$lp = ForumReplies::model()->with('user')->find(array('condition' => 'topic_id=:topic_id', 'params' => array(':topic_id' => $topic_id), 'order' => 'date desc, time1 desc', 'limit' => 1 ));

if I change the order to asc, I’ll get the first users username, but if I use desc it gives me a “null” in “$lp->user” …

If you wonder what I’m trying to do is that I want to make a “Jump to last post” from the listing of all topics under a category. Been stuck on this one for quite a while now and it seems that I can’t get it to work. :( Any help with this would be awesome!

2

If you are looking for a last post, why not just use a SELECT MAX ?

3

Good point, didn’t have a thought about it! But, can I use model()->with(“user”)->findBySql() or do I have to use another? Any tips how you would’ve putted the code together?

Edit:

I figured it out, but how can I get


AND (

`t`.`id` =  `user`.`id`

)

removed? that line destroys the whole sql query…

4

If you are getting t.id = user.id then your relation is not set up correctly.

5

Thanks for the reply. I managed to fix it after alot of hairstrings flying away from my head. Though, I think this is a big workaround but it works great so far :)