I have these 3 tables:
cols
groups | id | ... | ...
groups_users | group_id | user_id | created
users | id | ... | ...
In my Group model, I have a HABTM relation with the User model (using the alias Member).
public $hasAndBelongsToMany = array(
'Member' => array(
'className' => 'User',
'joinTable' => 'groups_users',
'foreignKey' => 'group_id',
'associationForeignKey' => 'user_id',
'unique' => true,
),
);
This works well if I'm just trying to get all of the members of a group and even order by fields in the users table such as their id or username. However, I have a situation where I need to order the results by the join table. I would like to display the members that have joined the group most recently, so 'groups_users.created' => 'DESC'
I've tried adding the following lines to the HABTM array but it didn't work
'with' => 'GroupsUser',
'order' => array('GroupsUser.id' => 'DESC')
I also created a model for GroupsUser like so but this also did not work
class GroupsUser extends AppModel {
public $name = "GroupsUser";
var $useTable = "groups_users";
public $order = array(
'GroupsUser.created' => 'DESC',
'GroupsUser.id' => 'DESC'
);
The following DOES WORK but I want to get away from calling the GroupsUser model, I would like to stick to using the HABTM Member relation.
$this->Group->GroupsUser->find('all',
array(
'limit' => 20,
'order' => array('GroupsUser.created' => 'DESC'),
'conditions' => array(
'GroupsUser.group_id' => $group_id,
'not' => array(
'OR' => array(
array('User.username' => NULL),
array('User.username' => 0),
array('User.username' => '')
)
)
),
'contain' => array(
'User' => array(
'fields' => array('User.username'),
),
)
)
);