CODEWITHSUNDEEP
cakephpjoincakephp-2.3
dav
dav

Reputation: 9267

inner join with containable cakephp

I have 3 tables, restaurants, orders, users. orders table has fields restaurant_id, status and user_id.

restaurant -> hasMany orders
orders belogsTo Restaurant
orders belongsTo users

I need to find only those restaurants that have order with status = 1, and at the same time need to fetch user's info.

So, I am making inner join with orders table,

$options['joins'] = array(
    array('table' => 'orders',
        'alias' => 'Order',
        'type' => 'INNER',
        'conditions' => array(
            'Restaurant.id = Order.restaurant_id',
        )
    )
);

But how Can I also get users' information along with this, because according to cake documentation http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables, allowed params here are table, alias, type, conditions. Is there a some way that I can embed the sql query of getting user's info in the final query? Yes, the alternative will be to write the custom query, but is not there a way to do with cake.

Thanks

update

The best solution in this case was to write custom sql query.

Upvotes: 1

Views: 9766

Answers (2)

Serge Rodovnichenko
Serge Rodovnichenko

Reputation: 558

List of orders with status 1 and with restaurant and user info:

$this->Restaurant->Order->find('all', array('conditions'=>array('Order.status'=>1), 'contain'=>array('Restaurant', 'User')))

Upvotes: 0

Dave
Dave

Reputation: 29121

Have you just tried an additional Join to get the Users as well?

Something like this:

$this->Restaurant->find('all', array(
    'joins' => array(
        array('table' => 'orders',
            'alias' => 'Order',
            'type' => 'INNER',
            'conditions' => array(
                'Restaurant.id = Order.restaurant_id',
                'Order.status = 1'
            )
        ),
        array('table' => 'users',
            'alias' => 'User',
            'type' => 'RIGHT',
            'conditions' => array(
                'Order.user_id = User.id'
            )
    ),

));

Note: What I usually find easiest (with complicated queries that you're having trouble figuring out) is to build the query 100% in SQL and get it working to your liking, then just re-build it in CakePHP.

Upvotes: 0

Related Questions