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

Issue

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

Solution

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();

Answered By - chrisShick