PostgreSQL多关联表条件查询：精准匹配图书标题与作者

问题原因分析 #

• 第一种AND连接的SQL：同一行book_details记录无法同时匹配标题和作者的param规则，条件永远不成立，返回空结果。
• 第二种OR+GROUP BY的SQL：会返回满足标题或作者任一条件的图书ID，无法过滤出同时满足两个条件的记录，因此得到多余结果。

原生PostgreSQL查询方案 #

方案1：自连接book_details表 #

通过两次连接book_details，分别匹配标题和作者条件，确保同一图书同时满足两个要求：

SELECT b.book_id
FROM books b
JOIN book_details bd_title ON b.book_id = bd_title.book_id
JOIN book_details bd_author ON b.book_id = bd_author.book_id
WHERE 
  -- 匹配标题条件
  bd_title.param_1 = 'A' 
  AND bd_title.param_2 = 'A' 
  AND bd_title.param_3 = 'B' 
  AND bd_title.param_4 = 'C' 
  AND bd_title.param_value ILIKE '%muni%'
  -- 匹配作者条件
  AND bd_author.param_1 = 'A' 
  AND bd_author.param_2 = 'B' 
  AND bd_author.param_3 = 'B' 
  AND bd_author.param_4 = 'A' 
  AND bd_author.param_value ILIKE '%dawn%';

方案2：子查询取交集 #

先分别筛选出满足标题、作者条件的图书ID集合，再取交集：

SELECT book_id
FROM books
WHERE book_id IN (
  SELECT book_id FROM book_details 
  WHERE param_1='A' AND param_2='A' AND param_3='B' AND param_4='C' AND param_value ILIKE '%muni%'
)
AND book_id IN (
  SELECT book_id FROM book_details 
  WHERE param_1='A' AND param_2='B' AND param_3='B' AND param_4='A' AND param_value ILIKE '%dawn%'
);

方案3：GROUP BY + HAVING 过滤 #

通过GROUP BY聚合图书ID，再用HAVING确保该图书同时匹配两个条件：

SELECT b.book_id
FROM books b
JOIN book_details bd ON b.book_id = bd.book_id
WHERE 
  (bd.param_1='A' AND bd.param_2='A' AND bd.param_3='B' AND bd.param_4='C' AND bd.param_value ILIKE '%muni%')
  OR 
  (bd.param_1='A' AND bd.param_2='B' AND bd.param_3='B' AND bd.param_4='A' AND bd.param_value ILIKE '%dawn%')
GROUP BY b.book_id
-- 若每个图书的标题、作者属性各只有一条记录，用COUNT(*) = 2更高效
HAVING COUNT(DISTINCT (bd.param_1, bd.param_2, bd.param_3, bd.param_4)) = 2;

Laravel 查询构建器实现 #

对应方案1的写法 #

$bookIds = DB::table('books')
    ->join('book_details as bd_title', 'books.book_id', '=', 'bd_title.book_id')
    ->join('book_details as bd_author', 'books.book_id', '=', 'bd_author.book_id')
    ->where([
        ['bd_title.param_1', '=', 'A'],
        ['bd_title.param_2', '=', 'A'],
        ['bd_title.param_3', '=', 'B'],
        ['bd_title.param_4', '=', 'C'],
        ['bd_title.param_value', 'ILIKE', '%muni%'],
        ['bd_author.param_1', '=', 'A'],
        ['bd_author.param_2', '=', 'B'],
        ['bd_author.param_3', '=', 'B'],
        ['bd_author.param_4', '=', 'A'],
        ['bd_author.param_value', 'ILIKE', '%dawn%'],
    ])
    ->pluck('books.book_id');

对应方案2的写法（子查询版） #

$bookIds = DB::table('books')
    ->whereIn('book_id', function ($query) {
        $query->select('book_id')
            ->from('book_details')
            ->where([
                ['param_1', '=', 'A'],
                ['param_2', '=', 'A'],
                ['param_3', '=', 'B'],
                ['param_4', '=', 'C'],
                ['param_value', 'ILIKE', '%muni%'],
            ]);
    })
    ->whereIn('book_id', function ($query) {
        $query->select('book_id')
            ->from('book_details')
            ->where([
                ['param_1', '=', 'A'],
                ['param_2', '=', 'B'],
                ['param_3', '=', 'B'],
                ['param_4', '=', 'A'],
                ['param_value', 'ILIKE', '%dawn%'],
            ]);
    })
    ->pluck('book_id');

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