CakePhp Find

Question

Hi, can anyone tell me what is the best way to do something like, from a db as

Table 1
-id
-data
-foreign_key_id_table2

Table 2
-id
-data
-foreign_key_id_table3

Table 3
-id
-data

I need to select all items of table one linked to all items in table 2 of a given table 3 entry something like

select table1.data from table1 where table1.foreign_key_id_table2 in (select table2.id from table2 where table2.foreign_key_id_table3 = X)

but i need to do that with cakephp and as i'm new with it i can't figure out how

please help :)

Answer 1

I'd recommend using Cake's find() method for models. All you have to do is create the appropriate model associations and Cake will do the work for you. In your case, it looks like the Table1 model belongsTo Table2 and Table2 belongsTo Table3.

Table2, then, would have a hasOne or hasMany relationship with Table1 and Table 3 would have the same relationship with Table2.

To provide a real world scenario, consider the ubiquitous blog application. A User can create Posts and a Post can have many Comments:

Database

users
 - id
 - data

posts
 - id
 - user_id
 - data

comments
 - id
 - post_id
 - data

User Model

class User extends AppModel {
   $hasMany = array ( 'Post' );

   // ...
}

Post Model

class Post extends AppModel {
   $hasMany = array ( 'Comment' );
   $belongsTo = array ( 'User' );

   // ...
}

Comment Model

class Comment extends AppModel {
   $belongsTo = array ( 'Post' );

   // ...
}

Once you have the associations in place, just call the find() method of whichever object you're interested in and Cake will return the associated records of other classes as well.

Answer 2

Ok, I'll try and help with the "teach a man how to fish".

Cake models represent database tables. Each model can have associations defined, such as belongsTo, hasMany, hasOne, etc.. When you define an association, cake recognizes it and gets all the related data you need.

For example, let's say you have a model Post and a model Comment. If you set up an association between them (Post-hasMany-Comment, and Comment-belongsTo-Post), cake will work the magic every time you do a Post->find() or Comment->find()

So what does this mean? If you do something like this:

$data = $this->Post->findByTitle('your post title');

the $data variable will contain not only your Post data, but also all the associated comments. You can always see what's in there by calling debug($data);

The level of "depth" cake will go with the related data can be set by using the recursive property / parameter, or by using the built-in Containable behaviour.

Another benefit of defining associations between model (to answer your comment on Rob's answer) is that you don't need to put all your models in the $uses var. In fact, that would be the wrong thing to do.

Believe it or not, but once you've defined your associations properly, this will work:

class PostsController extends AppController
{
    // not really needed, cake detects it automagically
    var $uses = array('Post');

    function index()
    {
        $comments = $this->Post->Comment->find('all');
    }
}

Sweet, isn't it?

In your case, it would be something like this:

$this->Table1->find
    (
        'all',
        array
        (
            'conditions' => array
            (
                'Table2.foreign_key_id_table3' => X
            )
        )
    );

There are many other useful tips when you're working with cake's built-in functions and classes, so I suggest you take some time to learn cake properly, it will make your life easier.