CakePHP 1.3 contain not building associations correctly

Question

this is my first question here. I'm having an issue with containable behavior in cakephp 1.3. The issue is as follows: I have a Deliverable model with the following associations:

class Deliverable extends AppModel {
  var $name = 'Deliverable';
  var $displayField = 'name';
  var $actsAs = array('Containable');

  var $belongsTo = array(
    'Asset' => array(
        'className' => 'Asset',
        'foreignKey' => 'asset_id',
        'dependent' => false
    ),
    'DelRouteName' => array(
        'className' => 'DelRouteName',
        'foreignKey' => 'del_route_name_id',
    ),
    'AccessGroup' => array(
        'className' => 'AccessGroup',
        'foreignKey' => false,
        'conditions' =>
            'Deliverable.role_id = AccessGroup.id'
    )
);

  var $hasOne = array(
    'Artifact' => array(
        'className' => 'Artifact',
        'foreignKey' => 'deliverable_id',
        'dependent' => true,
    )
);

  var $hasMany = array(
    'Delivery' => array(
        'className' => 'Delivery',
        'foreignKey' => 'deliverable_id',
        'dependent' => true,
        'order' => 'Delivery.gmt_created',
    )
);

And here is the configuration of the pagination settings:

$this->paginate['Deliverable'] = array_merge($this->paginate['Deliverable'], array(
        'limit' => $this->getSysPref('items_per_page', 20),
        'conditions' => array(
            'Deliverable.role_id' => $this->myRoleSetIds(),
            'Deliverable.asset_id' => $asset['Asset']['id'],
            'Delivery.gmt_created >' => $keep_time
        ),
        'order' => 'Deliverable.name',
        'contain' => array(
            'AccessGroup',
            'Delivery' => array(
                'conditions' => array(
                    'Delivery.gmt_created >' => $keep_time
                ),
                'limit' => 1,
                'order' => 'Delivery.gmt_created DESC'                      
            ),
            'DelRouteName'
        )
    ));
    $deliverables = $this->paginate('Deliverable');

As you can see, the containable behavior is attached, the associations defined, and the associated models contained (in the pagination configure section).

This issue I'm having is that it is able to successfully query the contained models "AccessGroup" and "DelRouteName", yet it doesn't fetch the associated "Delivery" data. The error only shows up if I set a condition on the parent model (Deliverable) targeting the child model (Delivery), which I've done before and is perfectly legal. I get this SQL error and as you can see the table deliveries is not joined at all, yet access_groups and del_route_names are.

Here is the SQL that cake builds:

SELECT "Deliverable"."id" AS "Deliverable__id", "Deliverable"."name" AS 
"Deliverable__name", "Deliverable"."artifact_name" AS "Deliverable__artifact_name", 
"Deliverable"."type" AS "Deliverable__type", "Deliverable"."asset_id" AS 
"Deliverable__asset_id", "Deliverable"."del_route_name_id" AS 
"Deliverable__del_route_name_id", "Deliverable"."artifact_id" AS 
"Deliverable__artifact_id", "Deliverable"."status" AS "Deliverable__status", 
"Deliverable"."delivery_id" AS "Deliverable__delivery_id",                         
"Deliverable"."deliverable_id" 
AS "Deliverable__deliverable_id", "Deliverable"."gmt_created" AS 
"Deliverable__gmt_created", "Deliverable"."locked" AS "Deliverable__locked", 
"Deliverable"."frozen" AS "Deliverable__frozen", "Deliverable"."cloaked" AS 
"Deliverable__cloaked", "Deliverable"."role_id" AS "Deliverable__role_id", 
"DelRouteName"."id" AS "DelRouteName__id", "DelRouteName"."name" AS                     
"DelRouteName__name", 
"DelRouteName"."type" AS "DelRouteName__type", "AccessGroup"."id" AS "AccessGroup__id", 
"AccessGroup"."name" AS "AccessGroup__name", "AccessGroup"."dn" AS "AccessGroup__dn", 
"AccessGroup"."filters" AS "AccessGroup__filters", "AccessGroup"."paths" AS 
"AccessGroup__paths" FROM "deliverables" AS "Deliverable" LEFT JOIN "del_route_names" 
AS "DelRouteName" ON ("Deliverable"."del_route_name_id" = "DelRouteName"."id") LEFT     
JOIN "access_groups" AS "AccessGroup" ON ("Deliverable"."role_id" = "AccessGroup"."id")      
WHERE "Deliverable"."role_id" = ('0') AND "Deliverable"."asset_id" = '2' AND 
"Delivery"."gmt_created" > '1361391936'   ORDER BY "Deliverable"."name" ASC  LIMIT 50

which gives me this error (using postgresql)

Warning (2): pg_query() [http://php.net/function.pg-query]: Query failed: ERROR:  missing FROM-clause entry for table "Delivery"

I'm at a loss here, so any help would be greatly appreciated. Let me know if you need more information as well.

Using a psql command line I added the clause `LEFT JOIN "deliveries" as "Delivery" ON ("Delivery"."deliverable_id" = "Deliverable.id) WHERE "Delivery"."gmt_created" > 1` and the query works. (I just used one for an example) — Andrew Senner, Mar 22 '13 at 20:42

score 0 · Accepted Answer · answered Mar 23 '13 at 04:28

0

You cannot condition contained models from your main model. CakePHP uses separate queries for each model when doing contain(). If you need to add conditions that are cross-model, you'll need to use JOINs.

answered Mar 23 '13 at 04:28

Dave

28,833
23
113
183

I'm curious, as this is not what I've learned. The way cake builds the queries allow you to supply conditions on the main model which target child models, as long as they are contained. Here I am paginating a HABTM which targets the child (join table) model, but is attached to the parent model... This works perfectly fine: – Andrew Senner Mar 25 '13 at 15:36
`$this->paginate['Asset'] = array_merge($this->paginate['Asset'], array( 'conditions' => array( 'Asset.role_id' => $this->myRoleSetIds(), 'ProductsAsset.product_id' => $id ), 'contain' => array( 'ProductsAsset', 'AccessGroup' ) )); $assets = $this->paginate('Asset');` – Andrew Senner Mar 25 '13 at 15:37
The above code works perfectly and filters the main model based on the child model's product_id. – Andrew Senner Mar 25 '13 at 15:38
However, you fix does work... I'm curious as to why the cross-model conditions work "sometimes" but not others (as in this case). – Andrew Senner Mar 25 '13 at 15:43
One other thing, if what you say is true, how come when I supply conditions on the main model which target the 'AccessGroup' model, the query runs fine? e.g.: `'conditions' => array( 'Deliverable.role_id' => $this->myRoleSetIds(), 'Deliverable.asset_id' => $asset['Asset']['id'], 'AccessGroup.name' => 'This is a test', 'Delivery.gmt_created >' => $keep_time );` I have no 'join' included for AccessGroup, yet the conditions are applied to the main model.... – Andrew Senner Mar 25 '13 at 16:11
Sorry for the spam: CakePHP automatically performs the JOIN for hasOne and belongsTo, so you can supply criteria targetted at child models to the parent model, without adding the 'joins' index. However, if the association is hasMany or HABTM, the join must be performed manually. – Andrew Senner Mar 25 '13 at 16:16

score 0 · Answer 2 · answered Mar 25 '13 at 15:45

The updated code which now works thanks to the help of Dave.

$this->paginate['Deliverable'] = array_merge($this->paginate['Deliverable'], array(
            'limit' => $this->getSysPref('items_per_page', 20),
            'conditions' => array(
                'Deliverable.role_id' => $this->myRoleSetIds(),
                'Deliverable.asset_id' => $asset['Asset']['id'],
                'Delivery.gmt_created >' => $keep_time
            ),
            'joins' => array(
                array(
                    'table' => 'deliveries',
                    'alias' => 'Delivery',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'Deliverable.id = Delivery.deliverable_id'
                    )
                )
            ),
            'order' => 'Deliverable.name',
            'contain' => array(
                'AccessGroup',
                'Delivery' => array(
                    'conditions' => array(
                        'Delivery.gmt_created >' => $keep_time
                    ),
                    'limit' => 1,
                    'order' => 'Delivery.gmt_created DESC'                      
                ),
                'DelRouteName'
            )
        ));

CakePHP 1.3 contain not building associations correctly

2 Answers2

Linked