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

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. As for the other part of your question, you might want to update your question with the SQL outputted by the find call and the values of the Product.restaurant_id and the two dates. Why shouldn't that Order appear in the result set? — mensch, Nov 22 '12 at 12:47
So, what do I have to do in order that an entry is not returned if Product will be empty ? I can check it in PHP code but this is not what I'm looking for. — azerto00, Nov 22 '12 at 14:19
I'm afraid this isn't possible with Containable and you'll have to loop over the array to filter the results. This article proposes a solution with `afterFind()`: http://nuts-and-bolts-of-cakephp.com/2008/08/06/filtering-results-returned-by-containable-behavior/ — mensch, Nov 22 '12 at 14:29
I see that my link is offline, I've posted an answer instead. — mensch, Nov 22 '12 at 14:47

score 1 · Accepted Answer · edited May 23 '17 at 11:48

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).

CakePhp containable query with useless entries

1 Answers1