英文:
Is it possible to get max id in filtered and limited queryset without fetching?
问题
I can provide a translation of the relevant code portion:
假设我有一个数据库中的表,其中的行如下所示:
| ID | Foo |
| -- | --- |
| 19 | 1 |
| 20 | 1 |
| 38 | 2 |
| 44 | 1 |
| 50 | 2 |
| 61 | 1 |
我想要从类似这样的查询中获取最大的ID(在这个示例中是`50`):
```python
MyModel.objects.filter(foo=2).order_by('id').values_list('id')[:limit]
当然,limit
可以大于总行数。我可以通过获取整个查询集,将其转换为列表,并使用最后一项来实现这一点:
list(MyModel.objects.filter(foo=2).order_by('id').values_list('id')[:limit])[-1]
但是,我是否可以在数据库中执行此操作,而不必获取整个查询集?
原始SQL解决方案也可以。数据库是PostgreSQL。
更新:
我需要在有限集合内获取最大的ID,而不是整个表!例如,如果 limit == 1
,它将是 38
,如果 limit == 2
,它将是 50
,如果 limit == 500
,它仍然将是 50
。
Please note that the code is translated as requested, and the code comments are not included in the translation.
<details>
<summary>英文:</summary>
Suppose I have a table in db with rows like this:
| ID | Foo |
| -- | --- |
| 19 | 1 |
| 20 | 1 |
| 38 | 2 |
| 44 | 1 |
| 50 | 2 |
| 61 | 1 |
I want to get max id (`50` in this example) from queryset like this:
MyModel.objects.filter(foo=2).order_by('id').values_list('id')[:limit]
`limit`, of course, can be greater than total row number. I can do this by fetching all queryset, convert it to list and use the last item
list(MyModel.objects.filter(foo=2).order_by('id').values_list('id')[:limit])[-1]
but can I do this in database, without fetching entire queryset?
Raw sql solution is welcome too. DB is PostgreSQL.
**Update:**
I need max id of items inside limited set, not all table! For example if `limit == 1` if will be `38`, if `limit == 2` it will be `50` and if `limit == 500` it still will be `50`.
</details>
# 答案1
**得分**: 1
```python
<pre><code>from django.db.models import Max
MyModel.objects.filter(foo=2).aggregate(<b>max=Max('id')</b>)['max']</code></pre>
<pre><code>qs = MyModel.objects.filter(foo=2)
list(
qs.order_by('id')[limit - 1 : limit].union(qs.order_by('-id')[:1], all=True)
)[0].id</code></pre>
英文:
You can just use:
<pre><code>from django.db.models import Max
MyModel.objects.filter(foo=2).aggregate(<b>max=Max('id')</b>)<b>['max']</b></code></pre>
this will make a single query that will only return the greatest id
for all MyModel
s with foo=2
.
As for getting the n-th, you can work with a union queryset, like:
<pre><code>qs = MyModel.objects.filter(foo=2)
list(
qs.order_by('id')[limit - 1 : limit].union(qs.order_by('-id')[:1], all=True)
)[0].id</code></pre>
This will retrieve at most two records, one with the limit, and the last one. We then cast these to a list and return the first of the two. If the first one with the limit is missing, we get the last one (the second queryset). But these are retrieved in the same database query.
答案2
得分: 0
以下是翻译好的部分:
如果你只需要最大的 id
,这很基本:
SELECT max(id) AS id
FROM tbl
WHERE foo = 2;
如果你需要更多列或整行:
SELECT *
FROM tbl
WHERE foo = 2
ORDER BY id DESC
LIMIT 1;
如果 id
可以是 null
,你可能会想要:
...
ORDER BY id DESC NULLS LAST
...
参考:
- https://stackoverflow.com/questions/9510509/sort-by-column-asc-but-null-values-first/9511492#9511492
如果表很大且性能很重要,一个在 (foo, id DESC)
上的多列 索引 是理想的。(或者如果 id
可以是 null
,那就是 (foo, id DESC NULLS LAST)
。)只在 (foo, id)
上几乎同样好。如果对于相同的 foo
只有很少的行,只在 (foo)
上通常就足够了。
英文:
If all you need is the max id
, it's very basic:
SELECT max(id) AS id
FROM tbl
WHERE foo = 2;
If you need more columns or the whole row:
SELECT *
FROM tbl
WHERE foo = 2
ORDER BY id DESC
LIMIT 1;
IF id
can be null
, you'll probably want:
...
ORDER BY id DESC NULLS LAST
...
See:
- https://stackoverflow.com/questions/9510509/sort-by-column-asc-but-null-values-first/9511492#9511492
If the table is big and performance matters, a multicolumn index on (foo, id DESC)
would be ideal. (Or (foo, id DESC NULLS LAST)
if id
can be null
.) Just on (foo, id)
is almost as good. And if there are only few rows for the same foo
, just on (foo)
is typically good enough.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论