Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Doctrine: Query only where relationship doesn't exist?

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.

like image 216
Jake Wilson Avatar asked Dec 16 '22 10:12

Jake Wilson


1 Answers

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.

like image 191
prodigitalson Avatar answered Jan 05 '23 22:01

prodigitalson