首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何查询postgresql/sql中数组字段的订阅

如何查询postgresql/sql中数组字段的订阅
EN

Stack Overflow用户
提问于 2018-06-08 04:09:39
回答 2查看 54关注 0票数 0

我的数据由可以被分配到多个类别(恐怖、戏剧、喜剧等)的书籍组成。用户可以订阅某个类别,我希望能够对具有指定类别的所有图书的books表执行分页查询。示例模式:

代码语言:javascript
复制
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连接表中查询图书。这是我第一次尝试暴力破解--它很难看,我甚至不确定由于循环的缘故,分页是否正确。有没有更好的方法来做这件事?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-06-08 19:11:11

你根本不需要循环,

代码语言:javascript
复制
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
票数 1
EN

Stack Overflow用户

发布于 2018-06-08 04:27:36

OFFSETLIMIT是经典的选择,但是当您遇到性能问题时,还会出现其他的注意事项:https://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-wayhttps://use-the-index-luke.com/sql/partial-results/fetch-next-page是如何实现高性能分页的一个很好的概述。

其结果是维护外部所需的页面状态,而不是偏移量/限制,通过在当前页面之前对releasedate进行过滤(在本例中)并将其限制为页面大小,使用WHERE过滤器直接获取相关行。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50749464

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档