sql - 限制和偏移具有左连接的搜索查询

Question

我有两个表:任务和页面。第一页有一个列多次引用第二个表，称为 page_number。我想获取第一个表的所有字段和相同 tasks.id 的所有页码 值。我获取这些数据的查询是这样的:

SELECT
TASKS.ID,TASKS.URL,TASKS.ASSIGNEE,PAGES.TASK_ID,PAGES.PAGE_NUMBER
FROM TASKS
INNER JOIN (SELECT * FROM TASKS ORDER BY TASKS.ID LIMIT ? OFFSET ?)
AS T ON (TASKS.ID=T.ID)
LEFT JOIN PAGES ON (TASKS.ID=PAGES.TASK_ID);

这行得通，我得到了这样的输出:

      id  |          url          | assignee | task_id | page_number
------+-----------------------+----------+---------+-------------
   15 | /vector.pdf           |          |      15 |           1
   15 | /vector.pdf           |          |      15 |           2
   23 | /raster.pdf           |          |      23 |           1
   23 | /raster.pdf           |          |      23 |           2
   23 | /raster.pdf           |          |      23 |           4
 1001 | https://everette.com  |          |    1001 |           1
 1001 | https://everette.com  |          |    1001 |           2
 1002 | https://scarlett.com  |          |    1002 |           1
 1002 | https://scarlett.com  |          |    1002 |           2
 1002 | https://scarlett.com  |          |    1002 |           3
 1002 | https://scarlett.com  |          |    1002 |           4
 1002 | https://scarlett.com  |          |    1002 |           5
 1002 | https://scarlett.com  |          |    1002 |           6
 1002 | https://scarlett.com  |          |    1002 |           7

但我遇到了其他要求:我需要在同一查询中获得 TASKS 表的总数(用于前端分页)，并且我需要能够搜索结果表(即查找表中的任何子字符串)。为了搜索表格，我这样做了:

SELECT 
TASKS.ID,TASKS.URL,TASKS.ASSIGNEE,PAGES.TASK_ID,PAGES.PAGE_NUMBER
FROM TASKS
INNER JOIN (SELECT * FROM TASKS ORDER BY TASKS.ID LIMIT ? OFFSET ?)
AS T ON (TASKS.ID=T.ID)
LEFT JOIN PAGES ON (TASKS.ID=PAGES.TASK_ID)
WHERE TASKS.URL  LIKE ?
OR CAST(TASKS.ID AS TEXT) LIKE ?

我确实得到了结果，但是 LIMIT 和 OFFSET 在 WHERE 子句之前在连接表上工作，所以我将 LIMIT 设置为 10 并将 OFFSET 设置为 5，尝试查找前 5 条记录中的记录但没有结果，因为匹配的记录在原始表中更远:

SELECT
TASKS.ID,TASKS.URL,TASKS.ASSIGNEE,PAGES.TASK_ID,PAGES.PAGE_NUMBER
FROM TASKS
INNER JOIN (SELECT * FROM TASKS ORDER BY TASKS.ID LIMIT 10 OFFSET 5)
AS T ON (TASKS.ID=T.ID)
LEFT JOIN PAGES ON (TASKS.ID=PAGES.TASK_ID)
WHERE TASKS.URL  LIKE '%everette%'
OR CAST(TASKS.ID AS TEXT) LIKE 99

实际输出:

 id | url | assignee | task_id | page_number
----+-----+----------+---------+-------------
(0 rows)

预期输出:

id  |          url          | assignee | task_id | page_number
------+-----------------------+----------+---------+-------------
1001 | https://everette.com  |          |    1001 |           1
1001 | https://everette.com  |          |    1001 |           2

我应该如何构造此查询，以便获得与 WHERE 子句匹配的所有记录，最多获得 limit 个任务？

顺便说一句，我正在使用 PostgreSQL。在此先感谢您的帮助。

Answer 1

您可以将该计数添加到列列表中:

SELECT ... column list ..., 
       (select count(*) from tasks) as total_task
FROM tasks
  JOIN (SELECT * FROM tasks ORDER BY tasks.id LIMIT ? OFFSET ?) AS T 
    ON tasks.id = t.id
  LEFT JOIN pages ON tasks.id pages.task_id;