Doctrine: Query only where relationship doesn't exist?

Question

I have two tables: Articles and Categories. Articles can have a single Category assigned to them. But they don't have to have a Category.

Schema:

Article:
  columns:
    title:
      type: string(255)
    content:
      type: string(255)
    category_id:
      type: integer(4)

Category:
  columns:
    name:
      type: string(255)
    article_id:
      type: integer(4)
  relations:
    Article:
      class: Article
      local: article_id
      foreign: id
      foreignAlias: ArticleCategories

I can query for all Articles that have categories assigned to them like this:

$articles= Doctrine_Query::create()
  ->from('Article a')
  ->leftJoin('a.Category c ON c.article_id = a.id')
  ->where('c.id > 0')
  ->execute();

It returns this:

Object->Array
(
  [0] => Array
  (
    [id] => string(1) "1"
    [title] => string(4) "test"
    [content] => string(4) "test"
    [Category] => Array
    (
      [0] => Array
      (
        [id] => string(1) "2"
        [name] => string(7) "testing"
      )
    )
  )
etc...

What I need to do is query for Articles where there is no Category relationship. I can't just say ->where('c.id = NULL') either because if there is no Category relationship, then there isn't any [Category] array returned in the object. It only returns the id, title and content. Also I can't say ->where(a.Category = NULL) because Category isn't a column of Article.

Any ideas?

UPDATE I made a mistake on the Schema and updated it. I know it doesn't really make sense for an Category to only have a relationship with a single Article, but in reality I'm not using Articles/Categories. I was just using those terms as examples.

prodigitalson · Accepted Answer · 2011-08-11T18:16:38.100

UPDATE:

So the easiest way if you want the article as the primary object is to do a leftJoin with a condition for the fk being null. LEFT JOINs always grabs the record on the left side of the join regardless of whether the right side of the join has a corresponding record. So without the where you essentially get a result of all articles. So we can then filter those for only articles that DONT have a category by using the where condition... very similar to before:

$articles = Doctrine_Query::create()
  ->from('Article a')
  ->leftJoin('a.Category c')
  ->where('c.article_id IS NULL')
  ->execute();

There is no reason to specify an on condition. Doctrine will figure this out base on the realtionship. Additionally you dont need to use a where for this type of filtereing use an innerjoin instead, the inner join will only select itemes where the relationship exists (i.e. there is a a.category_id = c.id) so the query you posted should actually be:

$articles = Doctrine_Query::create()
  ->from('Article a')
  ->innerJoin('a.Category c')
  ->execute();

To get the articles without any category you can look for a category_id of null on the article:

$articles= Doctrine_Query::create()
  ->from('Article a')
  ->leftJoin('a.Category c')
  ->where('a.category_id IS NULL')
  ->execute();

Id probably remove the join though because its not really necessary, unless you need the null columns in the result for some reason.

Oh... ya I guess it is that simple. Thanks. What would I do if the `relations:` was defined on the Category table instead of on the Article table? — Jake Wilson, Aug 11 '11 at 17:55
Ah thanks. I was close but I was doing `= NULL` instead of `IS NULL`. MySQL mistake rather than Doctrine. But thanks for the tips on the joins as well! — Jake Wilson, Aug 11 '11 at 19:16

Doctrine: Query only where relationship doesn't exist?

1 Answers1

Linked