CODEWITHSUNDEEP
mysqlcakephpjoin
yossi
yossi

Reputation: 3164

CakePHP - SQL: joining tables

I have 4 tables:

stores {id, name, owner_id, city_id, ...}
store_managers {id, store_id, user_id, ...}   // since I'm not associating (i have my reasons) the table to the others by cake's association, the name is not by the cakePHP naming conventions. 
cities {id, name, ...}
users {id, name, ...}

Now.. in the stores_controller there is a function called 'stores_stats()' that collects some statistics about the stores owned by the user and shows it. The owner_id is coming from the $this->Auth->user('id')
I want to use a $this->Store->find('all') call to get the following information:

  1. The store record itself.
  2. The store managers of each store.
  3. City record for each store.
  4. The store record itself.

The users and the cities tables are associated with the stores table and a find('all') query returns the data correctly about them. my problem is with store_managers table.

How can I join the store_managers to the query? And, just to get it right - how do I write such query myself? The following query gives me something.. else..

SELECT *
FROM stores
LEFT JOIN cities ON stores.id = cities.id
LEFT JOIN store_managers ON store_managers.store_id = stores.id
WHERE stores.id = 122 AND stores.owner_id = 3

Upvotes: 0

Views: 1069

Answers (2)

Dave
Dave

Reputation: 29121

You'll probably want to use CakePHP's containable behavior. (read more about it here).

//Store Model
class Store extends AppModel {

var $actsAs = array('Containable');

//associations
//validation

//the method you call from the controller
function getStoreStats() {
    $this->recursive = -1;
    $params = array();
    $params['containable'] = array(
        'User' => array(
             'StoreManager',
        ),
        'City'
    );
    $data = $this->find('all', $params);
    return $data;
}
}

The idea is, you set recursive to -1, which limits the data you get back from the Store to itself. Then, using containable, you specify what other related data you want returned.

Make sure if you're reading the CakePHP book to verify you're on the right version (1.3, 2.0...etc).

Also, it's quite common to just set $this->recursive=-1; in the app model do it's the default for all.

Assuming all your relationships are set up correctly, and none of them are HABTM relationship, Containable should work great for you.

Upvotes: 1

koressak
koressak

Reputation: 191

The CakePHP solution to this would be implementing relation to the stores model. If there are many store managers, you can put this code into the Stores model:

$hasMany = array(
'StoreManagers' => array( 'className' => 'StoreManagers')
);

Or if there is only one store manager:

$hasOne = 'StoreManagers';

If you stick to the naming conventions, the find() call shall return array, where for each Store shoud be 'StoreManagers' item (or array), where all linked items would be fetched.

For more on the subject of joining tables see here

Upvotes: 0

Related Questions