我的数据由可以被分配到多个类别(恐怖、戏剧、喜剧等)的书籍组成。用户可以订阅某个类别,我希望能够对具有指定类别的所有图书的books表执行分页查询。示例模式:
CREATE TABLE IF NOT EXISTS books (
bookid uuid PRIMARY KEY,
categories uuid[],
name text,
author text,
releasedate timestamp
);
CREATE TABLE IF NOT EXISTS categories (
categoryid uuid PRIMARY KEY,
name text
);
CREATE TABLE IF NOT EXISTS users (
userid uuid PRIMARY KEY,
name text
);
CREATE TABLE IF NOT EXISTS catsubscribers (
categoryid uuid REFERENCES categories,
userid uuid REFERENCES users,
PRIMARY KEY (categoryid, userid)
);
CREATE TABLE IF NOT EXIST bookcats (
bookid uuid REFERENCES books,
categoryid uuid REFERENCES categories,
PRIMARY KEY (bookid, categoryid)
);
CREATE OR REPLACE FUNCTION getBooksBySubscribedCategory(me uuid, ofst integer, lim integer)
RETURNS TABLE(bookid uuid, categories uuid[], name text, author text, releasedate timestamp) AS $$
DECLARE cid RECORD;
BEGIN
FOR cid IN (SELECT categoryid FROM catsubscribers WHERE userid = me)
LOOP
RETURN QUERY SELECT bks.bookid, bks.categories, bks.name, bks.author, bks.releasedate
FROM (SELECT bookid, categories, name, author, releasedate FROM books WHERE bookid IN (SELECT bookid FROM bookcats WHERE categoryid = cid ) AS bks
ORDER BY releasedate DESC
OFFSET ofst
LIMIT lim;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
此函数获取用户订阅的类别数组,循环遍历每个类别,并在bookcat连接表中查询图书。这是我第一次尝试暴力破解--它很难看,我甚至不确定由于循环的缘故,分页是否正确。有没有更好的方法来做这件事?
发布于 2018-06-08 19:11:11
你根本不需要循环,
RETURN QUERY
SELECT bks.bookid, bks.categories, bks.name, bks.author, bks.releasedate
from books bks join bookcats bc on bc.bookid = bks.bookid
join catsubscribers cs on cs.categoryid = bc.categoryid
where cs.userid = me
group by bks.bookid, bks.categories, bks.name, bks.author, bks.releasedate
limit lim offset ofst
发布于 2018-06-08 04:27:36
OFFSET
和LIMIT
是经典的选择,但是当您遇到性能问题时,还会出现其他的注意事项:https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way和https://use-the-index-luke.com/sql/partial-results/fetch-next-page是如何实现高性能分页的一个很好的概述。
其结果是维护外部所需的页面状态,而不是偏移量/限制,通过在当前页面之前对releasedate
进行过滤(在本例中)并将其限制为页面大小,使用WHERE
过滤器直接获取相关行。
https://stackoverflow.com/questions/50749464
复制相似问题