I am trying to return the latest rows of data from a table for certain users.
I want to display this using CActiveDataProvider.
I have 2 queries I am showing the users, a summary version and a long version. The long version is all the records, the summary is the latest record for each matched user. I use CActiveDataProvider with pagination with the long version, so want to also use it on the summary one and display it with the same View.
This is my query I produced in MySql which works in an sql editor
select t.* FROM `Comment` t, (
SELECT UserID, Type, MAX(`Comment`.CreationDateTime) as expr1
FROM
`Comment`
INNER JOIN Follow
ON Follow.FollowingID = `Comment`.UserID
WHERE
`Comment`.GoalID = 105 AND
(Follow.FollowerID = 4 OR `Comment`.UserID = 4 )
GROUP BY
`Comment`.UserID,
`Comment`.Type
) b
where t.CreationDateTime = expr1 AND t.UserID= b.UserID AND t.Type = b.Type
I have tried many combinations of CDbCriteria and CActiveDataProvider, but dont seem to be able to get this to work.
Does anyone know how to get this query to display in Yii ?
Note, the reason for the Select within Select was because querying the row and max(creationdate) returns the first row details in the table matching the WHERE clause and the max(creationdate) for the criteria, but it doesnt return the row details for the max(creationdate) row itself.
Very stuck on this and appreciate any advice