CActiveRecord relations()

Yii 1.1 General Discussion
1

After many hours of searching and trying without success, I need help in understanding if what I want is possible…or if there is a preferred method to achieve what I want.

I have a user table (tbl_user) and timeclock table (tbl_timeclock) used to record user clock punches (clock IN or clock OUT). The User CActiveRecord relation to Timeclock is HAS_MANY keyed on user_id. Easy.

What I want to achieve is a way to get the User records with a scope and to only return the users that are currently clocked IN. A user is clocked IN if the most recent clock punch (tbl_timeclock query ordered descending by the datetime field) is IN.

So ideally I could return all the clocked IN users with something like:


$dataProvider = new CActiveDataProvider(User::model()->onlyUsersClockedIN());

I don’t know how to write the relations() in the User model to handle this. Nor, can I think of a clean way to code this.

Any ideas?

(I LOVE Yii!)

2

I didn’t understand enough to code it for you, but I suggest you to come up with a SQL query that get what you want.

Then, try to build a Yii scope that mimics that logic. If you can’t, post the SQL here and we’ll try to help you.

3

Originally I was thinking this couldn’t be put into a single query, but a little poking around on MySQL forums gave me


select * from (SELECT *

FROM (SELECT *

FROM tbl_timeclock

ORDER BY clock_time DESC) AS s

GROUP BY user_id) as ss where clock_event='IN'

Now, how best to put this into a model?