CakePHP：当有多个关联记录时，如何指定返回的字段

Question

I'm doing a find('all') with a fairly complex set of conditions:

array(
    'conditions' => array(
        (int) 0 => 'Attempt.test_id = 8',
        (int) 1 => 'Attempt.score > 60',
        (int) 2 => array(),
        (int) 3 => array(
            (int) 0 => 'Resume.has_file = 1'
        )
    ),
    'joins' => array(
        (int) 0 => array(
            'table' => 'attempts',
            'alias' => 'Attempt',
            'type' => 'LEFT',
            'conditions' => array(
                (int) 0 => 'Attempt.user_id = User.id AND Attempt.test_id != 5'
            )
        )
    ),
    'contain' => array(
        (int) 0 => 'Resume',
        (int) 1 => 'Attempt',
        (int) 2 => 'Tag'
    ),
    'group' => 'User.id',
    'limit' => (int) 1,
    'fields' => array(
        (int) 0 => 'User.id',
        (int) 1 => 'Resume.id'
    )
)

The returned data looks like this:

array(
    (int) 0 => array(
        'User' => array(
            'id' => '381'
        ),
        'Resume' => array(
            'id' => '15'
        ),
        'Attempt' => array(
            (int) 0 => array(
                'id' => '16072',
                'user_id' => '381',
                'test_id' => '8',
                'status' => 'complete'
            ),
            (int) 2 => array(
                'id' => '16073',
                'user_id' => '381',
                'test_id' => '8',
                'status' => 'complete'
            )

The query is taking forever and a day to run and I only need the User.id, so I'm trying to strip out the unneeded fields. It's working for the hasOne associations, but the hasMany doesn't match the syntax in the documentation. For example, 'limit'=>array('Attempt.id') throws errors because there is no $user['Attempt']['id']. Instead it's $user['Attempt'][#]['id'].

How do I do this? I'd also love to hear any other advice that will speed up this query.

Answer 1

Ok, here is what I would do.

first, I see that you only need User.id, however I see you that you are adding more associations in the contain, thus I would remove "Tag".

I see that you are doing a manual join of the type left, which is what CakePHP uses by default, yes I see you are setting an additional condition there, even though there is nothing wrong with it I would simply bind Attempt model on the fly with the aforementioned condition, that is

$this->User->unbindModel(array('hasMany' => array('Attempt'))); 
//I dont know if its a hasMany, I just assumed that
$this->User->bindMode(array('hasMany'=> array(
    'Attempt' => array('conditions' => array('Attempt.test_id <>' => 5)))));

Repeat with Resume model.

I see you are grouping, yet I don't see any aggregate function, why are you grouping in there? what do you need? Can it be solved with Hash::extrat()?

Another thing to look at: run the query directly in your Database console and see if there are indexes that could help you.

Lastly, I am not sure about your requirements but should both conditions (in Attempt and Resume models) be met? if that is the case then you need "inner join"