使用<@和NOT IN后ORDER BY RANDOM()失效问题求助

问题背景 #

开发Django REST API时，需要将product和service相关表的联合查询结果按随机顺序返回。查询需满足两个条件：

• 行的tags字段包含指定列表中的所有项
• 对应的product或service未被用户标记为“不感兴趣”
  查询逻辑可正常执行，但结果无法实现随机排序。

表结构（Django模型生成） #

TABLE `product` (
  id character varying(10) NOT NULL,
  tags jsonb NOT NULL,
  ...
)

TABLE `product_item` (
  id character varying(11) NOT NULL,
  id_product character varying(10) NOT NULL,  -- Foreign Key
  ...
)

TABLE `product_not_interested` (
  id character varying(12) NOT NULL,
  id_product character varying(10) NOT NULL,  -- Foreign Key
  id_user character varying(50) NOT NULL,     -- Foreign Key
  ...
)

TABLE `service` (
  id character varying(10) NOT NULL,
  tags jsonb NOT NULL,
  ...
)

TABLE `service_item` (
  id character varying(11) NOT NULL,
  id_service character varying(10) NOT NULL,  -- Foreign Key
  ...
)

TABLE `service_not_interested` (
  id character varying(12) NOT NULL,
  id_service character varying(10) NOT NULL,  -- Foreign Key
  id_user character varying(50) NOT NULL,     -- Foreign Key
  ...
)

原查询语句 #

(
  SELECT DISTINCT 
    ('product') AS "type", 
    (product.id) AS "id", 
    (product.title) AS "title", 
    (product_item.id) AS "product_item_id", 
    (product_item.type) AS "product_item_type", 
    (product_item.title) AS "product_item_title", 
    RANDOM() 
  FROM 
    "product_item" 
    INNER JOIN "product" ON (
      "product_item"."id_product" = "product"."id"
    ) 
  WHERE 
    (
      "product"."tags" <@ '[ <ITEMS> ]' 
      AND (
        "product_item"."id_product" NOT IN (
          SELECT 
            "n"."id_product" 
          FROM 
            "product_not_interested" AS "n" 
          WHERE 
            "n"."id_user" = '<USER ID>'
        )
      )
    ) 
  ORDER BY RANDOM() ASC LIMIT 10
) UNION (
  SELECT DISTINCT 
    ('service') AS "type", 
    (service.id) AS "id", 
    (service.title) AS "title", 
    (service_item.id) AS "service_item_id", 
    (service_item.type) AS "service_item_type", 
    (service_item.title) AS "service_item_title", 
    RANDOM() 
  FROM 
    "service_item" 
    INNER JOIN "service" ON (
      "service_item"."id_service" = "service"."id"
    ) 
  WHERE 
    (
      "service"."tags" <@ '[ <ITEMS> ]' 
      AND (
        "service_item"."id_service" NOT IN (
          SELECT 
            "n"."id_service" 
          FROM 
            "service_not_interested" AS "n" 
          WHERE 
            "n"."id_user" = '<USER ID>'
        )
      )
    ) 
  ORDER BY RANDOM() ASC LIMIT 10
)

问题原因 #

1. 子查询排序不影响最终结果：原写法中每个子查询单独使用ORDER BY RANDOM()，但UNION操作会忽略子查询的排序结果，合并后的结果顺序是不确定的。
2. 冗余的RANDOM()列：子查询中选择的RANDOM()列未参与最终排序，无法起到随机化整体结果的作用。

解决方案 #

将UNION后的结果作为子查询，在外层统一执行随机排序。同时可以优化NOT IN的写法，避免因子查询返回NULL导致的结果异常。

修改后的查询语句 #

SELECT * FROM (
  SELECT DISTINCT 
    'product' AS "type", 
    product.id AS "id", 
    product.title AS "title", 
    product_item.id AS "product_item_id", 
    product_item.type AS "product_item_type", 
    product_item.title AS "product_item_title"
  FROM 
    "product_item" 
    INNER JOIN "product" ON "product_item"."id_product" = "product"."id"
  WHERE 
    product.tags <@ '[ <ITEMS> ]' 
    AND NOT EXISTS (
      SELECT 1 
      FROM "product_not_interested" AS n 
      WHERE n.id_product = product_item.id_product 
        AND n.id_user = '<USER ID>'
    )
  LIMIT 10
  UNION
  SELECT DISTINCT 
    'service' AS "type", 
    service.id AS "id", 
    service.title AS "title", 
    service_item.id AS "service_item_id", 
    service_item.type AS "service_item_type", 
    service_item.title AS "service_item_title"
  FROM 
    "service_item" 
    INNER JOIN "service" ON "service_item"."id_service" = "service"."id"
  WHERE 
    service.tags <@ '[ <ITEMS> ]' 
    AND NOT EXISTS (
      SELECT 1 
      FROM "service_not_interested" AS n 
      WHERE n.id_service = service_item.id_service 
        AND n.id_user = '<USER ID>'
    )
  LIMIT 10
) AS combined_results
ORDER BY RANDOM();

关键修改点 #

• 移除子查询中的ORDER BY RANDOM()，仅保留LIMIT来控制每个类别返回的条数
• 将UNION结果包裹为子查询，在外层使用ORDER BY RANDOM()实现整体随机排序
• 用NOT EXISTS替代NOT IN，避免子查询返回NULL时导致的结果为空问题

内容的提问来源于stack exchange，提问作者aman