zen of coding

'fields', 'conditions' and associated models in CakePHP 1.2

Sometimes you see code that tries to do something like this:

$this->Company->find('all', array('conditions' =>array('Profile.name'=>'test'),
                                            'fields'=>array('Profile.id, Profile.name')))

Of course most of the time you’ll hear: “Well, this doesn’t work!”
Yet, some people will disagree: “Works for me!”

So, what’s really going on here?

Whether the above syntax works or not, would depend on your model association. Or, more specifically, whether or not an SQL JOIN is built.

If we assume that Company hasOne Profile, then the above find() will build a query like:

[sourcecode language=”sql”]
SELECT
Profile.id, Profile.name
FROM
`companies`
AS
`Company`
LEFT JOIN
`profiles` AS `Profile`
ON
(`Profile`.`company_id` = `Company`.`id`)
WHERE
`Profile`.`name` = ‘test’
[/cc]

… which is perfectly legal.

Of course if you attempt to do this for hasMany or HABTM, the query is going to fail.
You’ve probably guessed that the same works for belongsTo.

  • Willey1986

    Doesn’t work for me…

    When I try something like

    $this->Company->find(‘all’, array(‘conditions’ =>array(‘Profile.name’=>’test’),
    ‘fields’=>array(‘Profile.id, Profile.name’)))

    I get Unknown column ‘Profile.id’ in ‘field list’

  • @Willey1986

    And what is the relationship between the models and the type of your DB?

  • hey teknoid,

    is there any workaround for this?

    say for the hasMany association?
    couldn’t find anything yet.

  • @bach

    Yes, there are a few. Look for “forcing a join” on this blog for more info, or article about ad-hoc joins on the bakery.

%d bloggers like this: