Custom relation / sort

Yii 1.1 General Discussion
1

Basically I’m using CListView to display a messages inbox. Messages belong to a thread. The table structure is as follows:




tbl message

===========

id

thread_id

from_user_id

subject

body

date




tbl message_thread

==================

id

message_id

to_user_id

from_user_id

I have the following relation in my MessageThread model that retrieves the parent message of the thread:


'parent_message'=>array(self::BELONGS_TO, 'Message', 'message_id'),

So in my in inbox view I display the thread with ‘subject’ and ‘from_user_id’ from the parent message.

I now need to do the following:

  1. Retreive the latest message from each thread and display it’s ‘body’ field and ‘date’ field (display in inbox view)

  2. Enable sorting of threads by date (the date of the latest message)

The first task I have tried doing like this:


'message_latest'=>array(self::HAS_ONE, 'Message', 'thread_id', 'order'=>'j.date DESC', 'alias'=>'j'),

The problem with this is then the second task doesn’t work properly with this - i.e. sorting threads by date because the ‘message_latest’ relation already specifies a sort order, so this is conflicting when I perform the sorting in ClistView.

Anybody got any idea how I could go about this? I am using CActiveDataProvider in my model to generate the CListView.

2

You could try changing the ‘message_latest’ relation to




'message_latest'=>array(self::HAS_ONE, 'Message', 'thread_id', 'select'=>'MAX(date)', 'alias'=>'j')

Hopefully it will select the greatest date and eliminate the static order of the relation, which should allow sorting of the latest message’s date.

3

Hi mate, thanks for the reply. That sounds good but it’s not working for me - it’s giving me an error:

Active record "Message" is trying to select an invalid column "MAX(date)". Note, the column must exist in the table or be an expression with alias.

I have tried prefixing ‘date’ with the alias but again same error. Any ideas?

4

I figured it out - you have to do:

‘select’=>‘MAX(date) AS maxdate’

Now how do I use this maxdate variable in my CActiveDataProvider to perform the sorting?

5

Try adding ‘maxdate’ as a public property of the MessageThread class

6

Done that… didn’t make any difference. I’ve also tried loading that relation with CDbCriteria and this lets me use the maxdate field in my CActiveDataProvider, however it is only bringing back one thread in the inbox.

7

Maybe adding GROUP BY ‘thread_id’ will give you the results your looking for.

Check out this tutorial.

8

PERFECT. Many thanks bro!