从多对多关系返回一组随机排序的不同对象
我正在使用 Django 1.3 和 Postgresql 8.4，并且我得到了如下所示的模型（删除了不相关的内容）：
class Service(models.Model):
name = models.CharField(max_length=80)
class Location(models.Model):
name = models.CharField(max_length=80)
services = models.ManyToManyField(Service, through='LocalService')
class LocalService(models.Model):
location = models.ForeignKey(Location)
service = models.ForeignKey(Service)
我试图获取一组不同的 Service 对象，按链接的 Location 对象的属性进行过滤，并随机排序。我首先尝试了这个：
Service.objects.filter(location__name__icontains='o').distinct().order_by('?')
...但这引发了这个异常：
数据库错误：对于 SELECT DISTINCT，必须出现 ORDER BY 表达式 在选择列表中
谷歌搜索后，我发现要在 SQL 级别实现这种结果，需要将 DISTINCT 和 ORDER BY 放在单独的查询级别中，即：使用子查询。如果我子查询一组不同的结果，我可以像这样随机排序它们：
SELECT *
FROM (
SELECT DISTINCT s.*
FROM profile_service s
JOIN profile_localservice ls
ON ls.service_id = s.id
JOIN profile_location l
ON ls.location_id = l.id
WHERE l.name LIKE '%o%'
) as temptable
ORDER BY RANDOM()
我是否需要使用 Manager.raw() 方法与此 SQL 查询来获取我的模型实例集，或者是否有更简单的方法来执行此操作在 Django API 中？
I'm using Django 1.3 with Postgresql 8.4 and I've got models like the following (irrelevant stuff removed):
class Service(models.Model):
name = models.CharField(max_length=80)
class Location(models.Model):
name = models.CharField(max_length=80)
services = models.ManyToManyField(Service, through='LocalService')
class LocalService(models.Model):
location = models.ForeignKey(Location)
service = models.ForeignKey(Service)
I'm trying to get a distinct set of Service objects, filtered by attributes of linked Location objects, and ordered randomly. I tried this first:
Service.objects.filter(location__name__icontains='o').distinct().order_by('?')
...but that throws this exception:
DatabaseError: for SELECT DISTINCT, ORDER BY expressions must appear
in select list
After Googling around, I discovered that to achieve this kind of result at the SQL level, you need to put the DISTINCT and the ORDER BY in separate query levels, ie: use a subquery. If I subquery a DISTINCT set of results I can randomly order them like this:
SELECT *
FROM (
SELECT DISTINCT s.*
FROM profile_service s
JOIN profile_localservice ls
ON ls.service_id = s.id
JOIN profile_location l
ON ls.location_id = l.id
WHERE l.name LIKE '%o%'
) as temptable
ORDER BY RANDOM()
Do I need to use the Manager.raw() method with this SQL query to get my set of model instances, or is there a simpler way to do this from within the Django API?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据您的具体要求，以下内容可能会起作用（并且可能比
ORDER BY Random()表现更好）。我不确定 Postgresql，但是对于 MySQL，除了小数据集之外的任何东西的随机排序都非常慢。Depending on your exact requirements the following might work (and potentially perform better than
ORDER BY Random()). I'm not sure about Postgresql, but with MySQL randomized ordering on anything but a tiny dataset is really slow.