0

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'),
            ),
        )
    )
);
bowlerae
  • 924
  • 1
  • 14
  • 37
  • "_I've tried adding the following lines to the HABTM array but it didn't work_"... "Doesn't work" can mean anything, what exactly happens? – ndm Nov 03 '17 at 08:58
  • @ndm, good point. It didn't break the query as in displayed an error message or anything like that. It had no affect on the query. My query still ran but the order was unaffected. – bowlerae Nov 06 '17 at 18:07
  • You may want to try it with a more recent CakePHP version and/or in a fresh project with the same CakePHP version that you are currently using, to make sure it's not a problem specific with your app. Using the `order` option in the association configuration that way should work fine (it does for me). – ndm Nov 07 '17 at 22:20

0 Answers0