CakePHP: hasMany association not recognized

Question

Attempts belong to Users (Users have many Attempts).

In User.php (model)

public $hasMany = array (
    'Attempt'   =>  array(
        'className'     =>  'Attempt',
        'order' => 'modified DESC'
    ), 
);

In Attempt.php (model)

    public $belongsTo = array (
        'Test' => array ('className' => 'Test', 'foreignKey'=>'test_id', 'order' => 'Test.created DESC'),
        'User' => array ('className' => 'User', 'order' => 'User.created DESC')
    );

In a controller, I construct this query...

$joins = array(

        array( 
            'table'=>'attempts',
            'alias'=>'Attempt',
            'type'=>'LEFT',
            'conditions'=>array("Attempt.user_id = User.id AND Attempt.test_id != {$practice_test_id}")
        )

    );

$conditions[] = array('Resume.has_file = 1');

$search_options = array( 'conditions'=>$conditions, 
    //'joins' => $joins, //<--I'M FORCED TO USER THIS TO GET THIS TO WORK
    'contain' => array('Resume', 'Attempt', 'Tag'),
    'order' => array('Attempt.score'=> 'DESC'),
    'group' => 'User.id'
);
$paginator_settings = $search_options;
$paginator_settings['limit'] = 25;
$this->Paginator->settings = $paginator_settings;

$resume_display_array = $this->Paginator->paginate('User');

(It looks a bit strange here because I stripped out a lot of distracting logic that happens in between the construction of this query. Most of these conditions are built dynamically based on user input.)

When I leave out the $search_options['joins] this does not work. I get the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Attempt.score' in 'order clause'

The SQL dump looks something like this:

SELECT `User`.`id`
FROM `users` AS `User` 
LEFT JOIN `resumes` AS `Resume` ON (`Resume`.`user_id` = `User`.`id`) 
WHERE `User`.`id` IN (
    SELECT `User1`.`id` FROM `users` AS User1 
    LEFT JOIN `tags_users` AS TagUser ON (`User1`.`id`= `TagUser`.`user_id`) 
    LEFT JOIN `tags` AS Tag ON (`TagUser`.`tag_id`= `Tag`.`id`) 
    WHERE `Tag`.`id` = (2) ) AND `Resume`.`has_file` = 1 GROUP BY `User`.`id` 
ORDER BY `Attempt`.`score` DESC 
LIMIT 25

Why isn't the Attempt model getting included automatically due to its hasMany relationship with User?

Answer 1

try this it'll work

  $this->User->bindModel(array('hasMany' => array(
            'FollowUp' => array('className' => 'FollowUp', 'foreignKey' => 'publisher_id',
            'fields' => array('amount'),
        ),
        'SchoolFollow' => array('className' => 'SchoolFollow', 'foreignKey' => 'publisher_id',
            'fields' => array('amount'),
        ),
        'TravelerFollow' => array('className' => 'TravelerFollow', 'foreignKey' => 'publisher_id',
            'fields' => array('amount'),
        ),
        $mdl . 'Cashback' => array('className' => $mdl . 'Cashback', 'foreignKey' => 'publisher_id',
            'fields' => array('admin_comission_amount'),
            'conditions' => array('status' => 'done')
        ),
)));

Answer 2

I don't see a problem here. In fact, isn't the answer in your other question posted today?

But this is not the case with hasMany and hasAndBelongsToMany associations. Here is where forcing joins comes to the rescue. You only have to define the necessary joins to combine tables and get the desired results for your query.

So, from what I understand, you have to force the join relation when talking about a hasMany, right? That's what you are doing when using the $joins array. Since a User hasMany Attempt, then you'll have to force the join. Now, that probably won't be necessary if you where paginating from the Attempt's point of view. If you do a $this->Paginator->paginate('Attempt'); (changing the necessary relations and conditions), you wouldn't need to join the user table manually to get the data.

And so, if the Attempt doesn't join by itself (as per the docs says), then the order condition creates a problem because the Attempt data is retrieved in another query and the table reference is lost in the first query. Solution: do the join, cake is behaving as expected.

Answer 3

I think you've got a typo in your hasMany association -- className has a capital N.

I would also try testing $this->User->recursive = 2; $this->User->find('first');, just to see what it returns. If the results don't include Attempt, I would assume there's something wrong with how you set up the model relationships.