Using a nested array within a find method

Question

I am trying to find a way to do a find in cakePHP based on an array I have received from another find.

Structure: I have a number of sites in my database. I also have a number of businesses. Each business hasOne site, but not all sites belong to a business. I am trying to get a list of all orders in a sale that go to a site that belongs to a business. I have already done a find method to get all the sites that belong to a business, which gets returned in a nested array ($businesses) which looks like this:

array(
(int) 0 => array(
    'Business' => array(
        'id' => '17',
        'name' => 'Name',
        'code' => 'Code',
        'discount' => 'Discount',
        'site_id' => 47
    )
),
(int) 1 => array(
    'Business' => array(
        'id' => '19',
        'name' => 'Name',
        'code' => 'Code',
        'discount' => 'Discount',
        'site_id' => '108'
    )
),

Now, I want to use the data in this array as part of another find method. Something like this:

$this->Order->find('all', array(
            'contain'=>array("Info"),
            'conditions'=>array('Order.sale_id' => $sale, 'Order.site_id' => $businesses),
            'order'=> array('last_name' => 'ASC')));

The problem is that when I do it this way, I get an Array to string conversion error because $businesses is a nested array, so Cake converts this to

AND `Order`.`site_id` IN (Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array)

How do I access the nested arrays so that I can compare the site_id on the order to the site_id of each business in my array?

user221931 · Accepted Answer · 2013-10-22 23:15:21Z

2

You don't need all that information returned from find() to run your next query.

What you basically need is SQL's IN expression and an array of IDs which should be put as something like:

$this->Order->find('all', array(
    'conditions'=>array(
        'Order.site_id' => Hash::extract($businesses, '{n}.Business.site_id')
     )
));

Hash::extract() will create an array of the ids contained in the supplied path {n}.Business.site_id of $businesses and cakephp will notice that you have an array there so it will create an SQL's IN expression to tell your database that you want the id to be one of those given.

You should make your previous finds return only what you need (i.e. ids and primary keys) in order to reduce the burden on the DB.

edited Oct 22, 2013 at 23:15

answered Oct 21, 2013 at 22:23

user221931

1,8521 gold badge13 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

scrowler Over a year ago

You don't need to specify IN with Cake, if your comparison is an array it assume it.

Joshua Zollinger Over a year ago

Now it's returning an empty array. As for the previous find, I used that find in another method. I just passed the result to this function since it had the information I needed.

user221931 Over a year ago

If you need it keep it :) If you don't get any results it means there are no data matching the conditions. Check if your conditions are right or not and if the produced SQL is what you're aiming for. But the general idea is what I have in my answer.

Joshua Zollinger Over a year ago

Belay that last comment. I was searching for two conditions that didn't coincide in the range I was checking. This worked great. Thank you!

Collectives™ on Stack Overflow

Using a nested array within a find method

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related