CakePhp containable query with useless entries

Question

I'm stuck at a query in CakePhp using containable behaviour. So I have - Model Order HABTM Product

I would like taking orders in a specific state and those which have at least one Product in a specific restaurant.

In order you to understand better, here is what looks like the schema

(int) 110 => array(
    'Order' => array(
        'id' => '10',
        'customer_id' => '3',
        'state_id' => '6',
        'payment_id' => '3',
        'created' => '2012-11-10 12:23:03',
        'user_id' => '10',
        'date_delivery' => '2012-10-12 20:30:00',
        'km' => '2.76'
    ),
    'Product' => array(
        (int) 0 => array(
            'id' => '51',
            'category_id' => '2',
            'restaurant_id' => '10001',
            'title' => 'aaa',
            'description' => 'aaa',
            'price' => '8.2',
            'tva_id' => '2',
            'img' => 'aaa',
            'maj_img' => '2012-10-24 15:38:56',
            'ProductsOrder' => array(
                'id' => '57',
                'product_id' => '51',
                'order_id' => '10',
                'quantity' => '1',
                'price' => '8.2',
                'tva_id' => '2',
                'meal' => null
            )
)))

(ProductsOrder is the join table)

So, I'm using containable behaviour and here is my query :

$q = $this->Order->find('all', array(
        'conditions' => array(
            'Order.date_delivery BETWEEN ? AND ?' => array($dateDebut->format('Y-m-d H:i:s'), $dateFin->format('Y-m-d H:i:s')),
            'Order.state_id' => array(6, 8)),
        'order' => 'Order.date_delivery',
        'contain' => array(
            'Product' => array(
                'conditions' => array('Product.restaurant_id' => $id),
            )
        )
            ));

To problem is that the result of the query returning me some entries that I don't want. Here is one example is one that are part of result query and is not suppose do be there. Note that Product is an empty array.

109 => array(
    'Order' => array(
        'id' => '179',
        'customer_id' => '139',
        'state_id' => '6',
        'payment_id' => '3',
        'created' => '2012-11-18 22:29:16',
        'user_id' => '10',
        'date_delivery' => '2012-10-12 20:00:00',
        'coursier_id' => '19',
        'comment' => '',
        'km' => '10.7'
    ),
    'Product' => array()

You may save me if you have an idea of what do solve that ... Thank you

EDIT Dump SQL

SELECT COUNT(*) AS count FROM live_resto_manager.restaurants AS Restaurant WHERE Restaurant.id = 10001

SELECT Order.id, Order.customer_id, Order.state_id, Order.payment_id, Order.created, Order.user_id, Order.date_delivery, Order.coursier_id, Order.comment, Order.km FROM live_resto_manager.orders AS Order WHERE Order.date_delivery BETWEEN '2012-10-01 00:00:00' AND '2012-10-31 23:59:00' AND Order.state_id IN (6, 8) ORDER BY Order.date_delivery ASC

SELECT Product.id, Product.category_id, Product.restaurant_id, Product.title, Product.description, Product.price, Product.tva_id, Product.img, Product.maj_img, ProductsOrder.id, ProductsOrder.product_id, ProductsOrder.order_id, ProductsOrder.quantity, ProductsOrder.price, ProductsOrder.tva_id, ProductsOrder.meal FROM live_resto_manager.products AS Product JOIN live_resto_manager.products_orders AS ProductsOrder ON (ProductsOrder.order_id IN (99, 100, 102, 105, 101, 103, 104, 1, 58, 3, 106, 108, 2, 31, 107, 109, 110, 111, 59, 112, 114, 115, 117, 118, 61, 113, 33, 116, 60, 119, 120, 121, 122, 4, 62, 34, 123, 63, 124, 125, 5, 6, 126, 129, 127, 128, 130, 131, 133, 132, 134, 135, 138, 137, 139, 140, 141, 64, 136, 142, 143, 35, 36, 37, 144, 145, 146, 147, 149, 148, 150, 151, 152, 153, 7, 154, 155, 65, 156, 158, 159, 161, 8, 157, 162, 160, 163, 9, 165, 166, 66, 164, 168, 38, 167, 169, 39, 170, 171, 172, 173, 67, 174, 175, 68, 69, 176, 177, 178, 179, 10, 40, 180, 181, 182, 192, 183, 184, 186, 187, 70, 185, 189, 190, 12, 188, 191, 193, 13, 14, 41, 194, 11, 195, 15, 71, 196, 197, 200, 201, 72, 198, 199, 202, 16, 73, 203, 42, 204, 205, 74, 206, 207, 208, 209, 43, 210, 212, 44, 211, 45, 17, 76, 213, 214, 215, 75, 77, 216, 78, 217, 218, 219, 79, 220, 221, 222, 223, 80, 224, 46, 225, 81, 18, 229, 233, 226, 227, 228, 230, 231, 232, 234, 47, 236, 235, 237, 238, 239, 19, 242, 243, 244, 48, 49, 50, 240, 241, 245, 20, 51, 246, 247, 248, 250, 251, 253, 254, 256, 257, 258, 260, 262, 252, 255, 82, 249, 259, 261, 263, 265, 264, 266, 267, 83, 84, 269, 85, 268, 270, 271, 86, 272, 21, 87, 274, 273, 275, 90, 276, 277, 88, 89, 278, 279, 280, 281, 282, 284, 285, 287, 288, 283, 91, 22, 286, 289, 290, 294, 298, 24, 291, 292, 295, 296, 297, 25, 52, 92, 93, 23, 299, 300, 301, 302, 53, 32, 303, 304, 305, 306, 309, 307, 308, 54, 94, 293, 55, 310, 312, 311, 313, 314, 316, 315, 317, 318, 319, 95, 320, 321, 96, 56, 26, 322, 325, 323, 324, 326, 328, 329, 330, 327, 97, 27, 28, 29, 30, 98, 331, 333, 332, 334, 335, 57, 336, 337, 338) AND ProductsOrder.product_id = Product.id) WHERE Product.restaurant_id = 10001

Whoooo this is pretty ugly

Answer 1

I'll post my comment as the answer and elaborate a bit on it. First, the empty Product array is an intended result of the Containable behaviour. It's standard for Containable to return an empty array for a contained model when there are no results from the relevant database table.

The Order is found because it has a state_id of 6 and the date_delivery is in the requested range.

You want to filter by an associated HABTM model so that it doesn't return an Order when it doesn't have any Product attached. When using Containable this means you'll have to filter the Orders with empty Product arrays manually. Containable just "contains" the amount of associated model data that's returned, it doesn't exclude the parent model based on the conditions of an associated model.

You could also ignore the automagic Cake applies to related models and use the joins paramter of a find call, as detailed in the accepted answer on this question. There's also a bindModel trick, detailed in the anwser section of this thread.

There's also the Linkable behaviour which shares the structure of Containable, but actually performs joins. Please note that the code hasn't been updated for a long time, so it might not work correctly in CakePHP 2.* (there are 2.0 forks, like this one, but I can't say what the quality of the updated codebase is).