Doctrine: Query only where relationship doesn't exist?
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 JOIN
s 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.
Jake Wilson
Experienced in developing tools for 3D animation, motion capture, video game and movie production, web development, Android development, responsive design, etc...
Updated on June 04, 2022Comments
-
Jake Wilson almost 2 years
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 theid, 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.
-
Jake Wilson over 12 yearsOh... 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 over 12 yearsAh thanks. I was close but I was doing
= NULL
instead ofIS NULL
. MySQL mistake rather than Doctrine. But thanks for the tips on the joins as well!