CODEWITHSUNDEEP
phpcakephp
Jeremy G
Jeremy G

Reputation: 568

cakephp - one find() query to rule them all

I have the following model relationships defined:

class Publication extends AppModel {

    var $name = 'Publication';

    var $hasAndBelongsToMany = array(
        'Author'=>array(
            'className'=>'Author'
        )
    );     
}

class Author extends AppModel {

    var $name = 'Author';

    var $hasAndBelongsToMany = array(
        'Publication'=>array(
            'className'=>'Publication'
        )
    );

    var $belongsTo = array(
        'College' => array (
            'className' => 'College'
        )
    );      
}

class College extends AppModel {

    var $name = 'College';

var $hasMany = array(
        'Department'=>array(
            'className'=>'Department'
        )
    );      
}

class Department extends AppModel {

    var $name = 'Department';

var $belongsTo = array(
        'College'=>array(
            'className'=>'College'
        )
    );      
}

The database tables are set up correctly (join tables for the HABTM, etc.). I am trying to find the one DB query to rule them all. I want to create a query that will find all of the publications with the associated authors, colleges, departments, etc. After getting data from a form, I have tried to run queries like this:

 $conditions = array(
            "Author.id" => $this->data['authors'],
            "Publication.year" => $this->data['year']                                
            );
$publications = $this->Publication->find('all', array('conditions' => $conditions));

This throws SQL errors saying that Author.id is not a valid field. Now, this is because a join with the 'authors' db table has not been completed by the time Author.id is being searched for. BUT, if I do this:

$pubs = $this->Publication->find('all', array('conditions' => $conditions));

then I get an array that has all of the Publications with all of the associated Authors (though, not the associated Colleges for some reason).

My question is this: what do I need to do to make the tables join before Author.id is searched for? I've attempted to use bindModel, containable, subqueries, but cannot get those to work for some reason (probably a ID10T error).

Thanks for any advice!

Edit: The result of the following call:

$this->Publication->recursive = 2;
$pubs = $this->Publication->find('all');

are as follows:

Array (

[0] => Array ( [Publication] => Array ( [id] => 1 [title] => TestArticle [year] => 2011 [type_id] => 3 ) [Type] => Array ( [id] => 3 [type_name] => Journal Articles ) [Author] => Array ( [0] => Array ( [id] => 2 [firstname] => Jeremy [lastname] => Gustine [middle] => A [faculty] => 0 [college_id] => 4 [AuthorsPublication] => Array ( [id] => 3 [author_id] => 2 [publication_id] => 1 ) [College] => Array ( [id] => 4 [college_name] => Letters, Arts, and Sciences ) ) [1] => Array ( [id] => 3 [firstname] => George [lastname] => Obama [middle] => A [faculty] => 0 [college_id] => 6 [AuthorsPublication] => Array ( [id] => 2 [author_id] => 3 [publication_id] => 1 ) [College] => Array ( [id] => 6 [college_name] => School of Public Affairs ) ) [2] => Array ( [id] => 2 [firstname] => Jeremy [lastname] => Gustine [middle] => A [faculty] => 0 [college_id] => 4 [AuthorsPublication] => Array ( [id] => 1 [author_id] => 2 [publication_id] => 1 ) [College] => Array ( [id] => 4 [college_name] => Letters, Arts, and Sciences ) ) ) ) [1] => Array ( [Publication] => Array ( [id] => 2 [title] => TestBook [year] => 2010 [type_id] => 1 ) [Type] => Array ( [id] => 1 [type_name] => Books ) [Author] => Array ( [0] => Array ( [id] => 7 [firstname] => Sony [lastname] => Stuff [middle] => L [faculty] => 0 [college_id] => 5 [AuthorsPublication] => Array ( [id] => 4 [author_id] => 7 [publication_id] => 2 ) [College] => Array ( [id] => 5 [college_name] => Nursing and Health Science ) ) ) ) )

Hopefully that is somewhat readable...

Upvotes: 1

Views: 423

Answers (1)

Rikesh
Rikesh

Reputation: 26421

Your second find is correct

i.e. $pubs = $this->Publication->find('all', array('conditions' => $conditions));

just above that try using recursive 2 (below code) to get related College too.

$this->Publication->recursive = 2;

Upvotes: 1

Related Questions