django使用别名进行自我连接查询 [英] django self join query using aliases
问题描述
我试图使用queryset来执行以下查询而不使用原始SQL。任何想法怎么办?
am trying to use queryset to perform the following query without using raw SQL. any idea how can do that?
select * from category_main a, category_list b, category_main c where b.main_id=c.id and a.id=c.parent_id
UPDATED
以下是我的模型
class Main(models.Model):
slug = models.SlugField()
is_active = models.BooleanField(default=True)
site = models.ForeignKey(Site)
parent = models.ForeignKey('self', blank=True, null=True, limit_choices_to={'parent' : None})
class Meta:
unique_together = (("slug", "parent"))
def __unicode__(self):
return self.slug
class List(models.Model):
main = models.ForeignKey(Main)
slug = models.SlugField(unique=True)
is_active = models.BooleanField(default=True)
parent = models.ForeignKey('self', blank=True, null=True)
def __unicode__(self):
return self.slug
更新
我刚刚设法找到一个对我来说是这样的查询,我用下面的建议加入主要的主要的父母,从那里我加入列表与主列表使用以下
Hi, I just managed to find a query that does that for me, I used advised below to join main with main's parent and from there I joined list with main list using the below
Main.objects.select_related('main', 'parent').filter(list__is_active=True, maini18n__language='en', list__listi18n__language='en').query.__str__()
'SELECT `category_main`.`id`, `category_main`.`slug`, `category_main`.`is_active`, `category_main`.`site_id`, `category_main`.`parent_id`, T5.`id`, T5.`slug`, T5.`is_active`, T5.`site_id`, T5.`parent_id` FROM `category_main` INNER JOIN `category_maini18n` ON (`category_main`.`id` = `category_maini18n`.`main_id`) INNER JOIN `category_list` ON (`category_main`.`id` = `category_list`.`main_id`) INNER JOIN `category_listi18n` ON (`category_list`.`id` = `category_listi18n`.`list_id`) LEFT OUTER JOIN `category_main` T5 ON (`category_main`.`parent_id` = T5.`id`) WHERE (`category_maini18n`.`language` = en AND `category_list`.`is_active` = True AND `category_listi18n`.`language` = en )'
返回的查询映射了我需要的所有内容,接受它不被添加到select语句,是有一种方法,所以我可以强制它从category_list中选择列。
the returned query mapped everything I need, accept its not being added to the select statement, is there a way so i can force it to select columns from category_list.* ?
推荐答案
这基本上是你想要的:
lists = List.objects.select_related('main', 'parent')
请注意,您必须在select_related这里明确说明关系,因为您的父
关系具有 null = True
不遵循默认值。
Note you have to explicitly state the relationships to follow in select_related here, because your parent
relationship has null=True
which isn't followed by default.
这将为您提供一组列表
对象,但预取相关的主
和列表
对象,您可以正常引用,而不再重新触发db。
This will give you a set of List
objects, but pre-fetch the related Main
and List
objects which you can reference as normal without hitting the db again.
这篇关于django使用别名进行自我连接查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!