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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With