英文:
Pagination with joins and where clauses on them
问题
Here's the translated content:
问题
想象一下有一张包含多篇文章的表格。每篇文章都有一个作者，可以有多个标签。
在获取这些文章时，它们应该包含标签和作者信息。
这可以通过在文章表上连接标签和作者来实现。
SELECT *
FROM article
LEFT JOIN tag ON article.id = tag.article_id
但现在我想要实现分页。
我尝试过的
SELECT *
FROM article
LEFT JOIN tag ON article.id = tag.article_id
LIMIT 7
但是，如果我有一篇文章有6个标签，另一篇只有3个标签怎么办？我会得到一篇文章带有所有6个标签，但另一篇只有一个标签。
我读了一些博客文章因为我陷入了困境。
当我限制行数时，我会丢失数据。
现在我读到要进行单独的请求。但首先，我只想执行一个查询，更重要的是我想要筛选我的查询。例如：返回所有带有特定标签的文章，但使用分页。
SELECT *
FROM article
LEFT JOIN tag ON article.id = tag.article_id
WHERE tag.name = "TV Show"
LIMIT 7
英文:
The problem
Imagine having a table full of articles. Each article has one author and can have many tags.
When fetching those articles, there should have the tags + author on them.
This is achieved by joining the tags and author on the articles table.
SELECT *
FROM article
LEFT JOIN tag ON article.id = tag.article_id
But now I want to implement pagination.
What I've tried
SELECT *
FROM article
LEFT JOIN tag ON article.id = tag.article_id
LIMIT 7
But now, what if I have an article with 6 tags and one with 3? I get the one article with all 6 tags, but the other article with just one tag.
I've read some blog posts because of I got stuck.
When I limited the rows, I lose data.
Now I've read to do separate requests. But first of all, I only want to do one query and more importantly I want to filter my queries. E.g.: Return all articles with a certain tag, but use pagination.
SELECT *
FROM article
LEFT JOIN tag ON article.id = tag.article_id
WHERE tag.name = "TV Show"
LIMIT 7
答案1
得分: 1
Here is the translated content:
由于您需要有关与文章相关的标签的完整信息，您需要分两步进行操作：
- 查询所需页面的文章列表，使用您的筛选器。类似于以下SQL查询：
SELECT *
FROM article a
WHERE EXISTS (
SELECT 1
FROM tag t
WHERE a.id = t.article_id
AND t.name = :p_tag)
AND a.name LIKE '%'||:p_article_name||'%'
ORDER BY a.publish_date
LIMIT 7
- 从步骤1中选择文章的标签列表。类似于以下SQL查询：
SELECT *
FROM tag t
WHERE t.article_id IN (:p1, :p2, :p3, :p4, :p5, :p6, :p7)
其中 :pN 对应文章的ID。
或者，您可以在单个查询中执行这两个步骤，将它们合并起来，但我个人认为这不是最佳选择，因为您将需要在应用程序端对此数据集进行汇总。
英文:
Since you need full information on tags associated with articles, you'll need
to do it in two step:
- Query list of articles for desired page employing your filters. Something like
SELECT *
FROM article a
WHERE EXISTS (
SELECT 1
FROM tag t
WHERE a.id = t.article_id
and t.name = :p_tag)
AND a.name like '%'||:p_article_name||'%'
ORDER BY a.publish_date
LIMIT 7
- Select list of tags for articles from step 1. Something like:
SELECT *
FROM tag t
WHERE t.article_id in (:p1, :p2, :p3, :p4, :p5, :p6, :p7)
Where :pN is a corresponding article id.
Alternatively you can do it in a single query, combining those two steps:
WITH articles_page(
SELECT *
FROM article a
WHERE EXISTS (
SELECT 1
FROM tag t
WHERE a.id = t.article_id
and t.name = :p_tag)
AND a.name like '%'||:p_article_name||'%'
ORDER BY a.publish_date
LIMIT 7
)
select *
from articles_page a
left join tags t
on (a.id = t.article_id)
But IMHO, it is not the best choice, as you'll need to aggregate this dataset on the application side.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论