教义:仅查询关系不存在的地方 [英] Doctrine: Query only where relationship doesn't exist?
问题描述
模式:
列:
标题:
类型:字符串(255)
内容:
类型:字符串(255)
category_id:
类型:整数(4)
类别:
列:
名称:
类型:字符串(255)
article_id:
类型:整数(4)
关系:
文章:
类:文章
本地:article_id
foreign:id
foreignAlias:ArticleCategories
我可以查询所有分类给他们的文章,如下所示:
pre>
$ articles = Doctrine_Query :: create()
- > from('article a')
- > leftJoin('a.Category c ON c.article_id = a.id')
- >其中('c.id> 0')
- > execute();
返回:
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)
)
)
)
etc ...
我需要做的是查询没有类别关系的文章,我不能只是说 - > where('c.id = NULL')
因为如果没有类别关系,那么对象中没有返回任何 [Category]
数组,它只返回 id,title和内容
。我也不能说 - > where(a.Category = NULL)
,因为类别不是文章的列。
任何想法?
UPDATE
我在Schema上犯了一个错误,并更新了它。我知道一个类别只能与一个文章有关系真的没有意义,但实际上我没有使用文章/类别。我只是用这些术语作为例子。
更新:
因此,如果您希望文章作为主要对象,最简单的方法是使用fk为空的条件执行一个 leftJoin
。 LEFT JOIN
s 总是抓住连接左侧的记录,而不管连接的右侧是否具有相应的记录。所以没有你本质上得到所有文章的结果。所以我们可以通过使用与之前非常相似的条件来过滤那些只有DONT具有类别的文章:
- > from(' c.article_id IS NULL')
- > execute();
没有理由指定一个在
条件。这个学说将会在这个基础上得出结论。此外,您不需要使用这种类型的过滤器来使用内联,而内部连接只会选择存在关系的项目(即,有一个 a.category_id = c.id
),所以您发布的查询实际上应该是:
$ articles = Doctrine_Query :: create()
- > from('article a')
- > innerJoin('a.Category c')
- > execute();
要获取没有任何类别的文章,您可以查找 category_id
在
文章中的空白
:
$ articles = doctrine_Query :: create()
- > from('article a')
- > leftJoin('a.Category c')
- > where('a.category_id IS NULL')
- > execute();
ID可能会删除联接,因为它不是真的必要,除非您需要结果中的空列由于某种原因。
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.
这篇关于教义:仅查询关系不存在的地方的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!