CakePHP show all books whose author is not in the authors table

Question

I can't seem to wrap my head around the CakePHP ORM model...

I have a table Authors (with Author.ID) and a list of books (with Book.AuthorID) - a lot of the books have an AuthorID which doesn't exist in the Authors Table (this is by design and expected)

For statistical reasons I would like to list all Books which have an AuthorID and the AuthorID isn't found in the Authors table.

I could load all books into memory and lookup the id's by hand - but there are ~4000 books. I'd like to do this in a ORM way (left outer join?)

Thanks, MC

So your question is how to define a left join with the CakePHP query builder, or are you asking whether you should use a left join in the first palce? — ndm, Apr 05 '17 at 14:19
I think [this](http://stackoverflow.com/questions/39558115/find-by-conditions-on-associated-model-in-cakephp-3) question might be of some use too — AronNeewart, Apr 05 '17 at 16:26

chrisShick · Accepted Answer · 2017-04-05T16:33:30.277

This is a pretty simple task with the orm. As @ndm mentioned in the comments, you can do this with a left join which is the default of the belongsTo association.

In the BooksTable make sure the association is added in the initialization method:

 public function initialize(array $config)
 {
    parent::initialize($config);

    $this->setTable('books');
    $this->setDisplayField('id');
    $this->setPrimaryKey('id');

    $this->belongsTo('Authors', [
        'foreignKey' => 'author_id'
    ]);
 }

In your Books controller (if that is the controller you are doing things in):

$books_without_authors = $this->Books
             ->find()
             ->contain(['Authors'])
             ->where(['Authors.id IS NULL'])
             ->all();

$books_with_authors = $this->Books
             ->find()
             ->contain(['Authors'])
             ->where(['Authors.id IS NOT NULL'])
             ->all();

If you are going to be doing this from multiple controllers then the DRY way to do it is as an association:

 public function initialize(array $config)
 {
    parent::initialize($config);

    $this->setTable('books');
    $this->setDisplayField('id');
    $this->setPrimaryKey('id');

    $this->belongsTo('Authors', [
        'foreignKey' => 'author_id'
    ]);
    $this->belongsTo('WithAuthors', [
        'className' => 'Authors',
        'foreignKey' => 'author_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('WithoutAuthors', [
        'className' => 'Authors',
        'foreignKey' => 'author_id',
        'conditions' => ['Authors.id IS NULL']
    ]);
 }

You can then call these in your controller

$with_authors = $this->Books
   ->find()
   ->contains(['WithAuthors'])
   ->all();

$without_authors = $this->Books
   ->find()
   ->contains(['WithoutAuthors'])
   ->all();

CakePHP show all books whose author is not in the authors table

1 Answers1