能否在筛选和限制的查询集中获取最大ID而不获取数据?

huangapple go评论62阅读模式
英文:

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
&lt;pre&gt;&lt;code&gt;from django.db.models import Max

MyModel.objects.filter(foo=2).aggregate(&lt;b&gt;max=Max(&#39;id&#39;)&lt;/b&gt;)[&#39;max&#39;]&lt;/code&gt;&lt;/pre&gt;

&lt;pre&gt;&lt;code&gt;qs = MyModel.objects.filter(foo=2)
list(
    qs.order_by(&#39;id&#39;)[limit - 1 : limit].union(qs.order_by(&#39;-id&#39;)[:1], all=True)
)[0].id&lt;/code&gt;&lt;/pre&gt;
英文:

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 MyModels 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
...

参考:

如果表很大且性能很重要,一个在 (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:

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.

huangapple
  • 本文由 发表于 2023年5月14日 07:22:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76245239.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定