Hello everyone, I’m developing a directory style site where businesses can be added, and there is a social element where users of the site can track these businesses. There is MANY_MANY relationship between users and businesses that represents a user tracking a business. I have to pull out results based on properties of the user, such as the trackers of a business that are male, female, new within the past week, and grouped by zipcode. I am able to easily get the first three done with the relation properties of my Business ActiveRecord model as follows:
'tracks' => array(self::HAS_MANY, 'Track', 'business_id'),
'trackers' => array(self::MANY_MANY, 'User', 'track(business_id,user_id)'),
'maleTrackers'=>array(self::HAS_MANY, 'User', 'user_id', 'through'=>'tracks', 'condition'=>"gender='M'"),
'femaleTrackers'=>array(self::HAS_MANY, 'User', 'user_id', 'through'=>'tracks', 'condition'=>"gender='F'"),
'newTrackers'=>array(self::HAS_MANY, 'Track', 'business_id', 'condition'=>"create_time>=DATE_ADD(now(),INTERVAL -7 DAY)"),
I’m doubtful that there is a way to get the results properly grouped by zipcode, as I have tried the following methods to no avail.
Here I tried to use the index property of a relation:
'trackersByZipcode'=>array(self::HAS_MANY, 'User', 'user_id', 'through'=>'tracks', 'index'=>'zip',),
This returns results of users indexed by zipcode but there is only one object for each index, rather than an array for each index as I would need.
I also tried to use group by techniques in the reulation as follows:
'trackersByZipcode'=>array(self::HAS_MANY, 'User', 'user_id', 'through'=>'tracks', 'select'=> 'count(`trackersByZipcode`.`id`) as xxz, `trackersByZipcode`.`zip`', 'group'=>'zip',),
But this gives me similar results to above where I will only have one user in each ‘grouped’ result.
I was wondering if it was at all possible to get an ActiveRecord result grouped in the way I am talking about. I think basically the functionality I am looking for here would best be achieved with the first method of using the index and then having an array under it rather than using an actual group by in the query. Has anyone tried this out? Maybe this would be a good way to extend the ActiveRecord class to return results like this?
Any suggestions or comments at all would be appreciated. Thanks a lot!