cakePHP - cant pull model association via belongsTo in second recursion

Question

cakephp I try to get a find('all'...) on a model with many associations in cakePHP 1.3, which does have the filter criteria for the query in the second level of the recursion within the schema. Simply, it looks like this and I want to filter for the UserId:

Delivery belongsTo Order, Order belongsTo User.

Here are the assocs:

Order: var $belongsTo = array(

  'User' => array(
      'className' => 'User',
      'foreignKey' => 'user_id',
      'conditions' => '',
      'fields' => '',
      'order' => ''
  ),....

Delivery: var $belongsTo = array(

   'Order' => array(
       'className' => 'Order',
       'foreignKey' => 'order_id',
       'conditions' => '',
       'fields' => '',
       'order' => ''
   ),...

The resulting error is:

SQL Error: 1054: Unknown column 'User.id' in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 684]

Here the full query, just for fun:

SELECT Delivery.id, Delivery.order_id, Delivery.delivery_address_id, Delivery.deliver_date, Delivery.created, Delivery.modified, Delivery.deliver_run, Delivery.product_mix_id1, Delivery.product_mix_id2, Delivery.product_mix_id3, Delivery.product_mix_id4, Delivery.assembled, Delivery.shipped, Delivery.rated, Delivery.price, Delivery.product_lines_id, Order.id, Order.user_id, Order.product_lines_id, Order.order_date, Order.deliver_monday, Order.deliver_tuesday, Order.deliver_wednessday, Order.deliver_thursday, Order.deliver_friday, Order.deliver_saturday, Order.delivery_address_id, Order.payment_delay, Order.active, Order.cancle_date, Order.replaced_order_id, Order.created, Order.modified, DeliveryAddress.id, DeliveryAddress.delivery_company, DeliveryAddress.delivery_title, DeliveryAddress.delivery_first_name, DeliveryAddress.delivery_last_name, DeliveryAddress.delivery_street, DeliveryAddress.delivery_house_nr, DeliveryAddress.delivery_postal_code, DeliveryAddress.delivery_town, DeliveryAddress.delivery_country, DeliveryAddress.created, DeliveryAddress.deleted, DeliveryAddress.modified, ProductLine.id, ProductLine.name, ProductLine.description, ProductMix1.id, ProductMix1.name, ProductMix1.description, ProductMix1.image_small_path, ProductMix1.image_normal_path, ProductMix1.product_categories_id, ProductMix1.depricated, ProductMix1.created, ProductMix1.modified, ProductMix2.id, ProductMix2.name, ProductMix2.description, ProductMix2.image_small_path, ProductMix2.image_normal_path, ProductMix2.product_categories_id, ProductMix2.depricated, ProductMix2.created, ProductMix2.modified, ProductMix3.id, ProductMix3.name, ProductMix3.description, ProductMix3.image_small_path, ProductMix3.image_normal_path, ProductMix3.product_categories_id, ProductMix3.depricated, ProductMix3.created, ProductMix3.modified, ProductMix4.id, ProductMix4.name, ProductMix4.description, ProductMix4.image_small_path, ProductMix4.image_normal_path, ProductMix4.product_categories_id, ProductMix4.depricated, ProductMix4.created, ProductMix4.modified FROM deliveries AS Delivery LEFT JOIN orders AS Order ON (Delivery.order_id = Order.id) LEFT JOIN delivery_addresses AS DeliveryAddress ON (Delivery.delivery_address_id = DeliveryAddress.id) LEFT JOIN product_lines AS ProductLine ON (Delivery.product_lines_id = ProductLine.id) LEFT JOIN product_mixes AS ProductMix1 ON (Delivery.product_mix_id1 = ProductMix1.id) LEFT JOIN product_mixes AS ProductMix2 ON (Delivery.product_mix_id2 = ProductMix2.id) LEFT JOIN product_mixes AS ProductMix3 ON (Delivery.product_mix_id3 = ProductMix3.id) LEFT JOIN product_mixes AS ProductMix4 ON (Delivery.product_mix_id4 = ProductMix4.id) WHERE User.id = 1

Does anyone know why cake does not pull the second level, in this case the User model, when even recursive is set to 5?

Many thanks.

lxa lxa · Accepted Answer · 2011-07-16T16:27:09

EDIT: It just occurred to me that in your case you don't need 2nd level JOIN actually, as you can filter by Order.user_id (instead of User.id)! Do you see my point?

So probably you don't need solution below.

As far as I know, Cake never does 2nd level JOIN itself, so for filtering (conditions) on 2nd level (and deeper) I use joins.

For your example:

$options['joins'] = array(
    array(
        'table' => 'orders',
        'alias' => 'Order',
        'type' => 'LEFT',
        'conditions' => array(
            'Order.id = Delivery.order_id',
        )
    ),
    array(
        'table' => 'users',
        'alias' => 'User',
        'type' => 'LEFT',
        'conditions' => array(
            'User.id = Order.user_id',
            'User.some_field' => $someFilteringValue
        )
    )
);
$result = $this->Delivery->find('all', $options);

cakePHP - cant pull model association via belongsTo in second recursion

1 Answers