如何执行JOIN查询不忽略NULL值？PostgreSQL+Rails实现方案咨询

针对你的需求——当John调用接口时显示他的所有产品，且必须展示所有分类（无论该分类下是否有产品），这里提供两种最优且高效的方案：

一、Rails ActiveRecord方案（推荐，贴合Rails生态） #

首先确保你的模型关联正确配置：

# app/models/user.rb
class User < ApplicationRecord
  has_many :products
end

# app/models/product.rb
class Product < ApplicationRecord
  belongs_to :user
  belongs_to :category, optional: true # 适配products表中category_id为NULL的情况
end

# app/models/category.rb
class Category < ApplicationRecord
  has_many :products
end

接下来是核心查询代码，通过一次数据库查询获取所有数据，避免N+1问题：

# 假设当前用户是John（实际项目中建议用devise等的current_user）
current_user = User.find_by(name: 'John')

# 执行左外连接查询，整理成需要的格式
categories_with_products = Category.left_outer_join(:products)
                                   .where(products: { user_id: current_user.id })
                                   .select(
                                     'categories.id', 
                                     'categories.name as category_name', 
                                     'products.id as product_id', 
                                     'products.name as product_name'
                                   )
                                   # 按分类名称分组
                                   .group_by { |record| record.category_name }
                                   # 转换分组结果，过滤掉无产品的条目
                                   .transform_values do |records|
                                     records.reject { |r| r.product_id.nil? }.map do |r|
                                       { id: r.product_id, name: r.product_name }
                                     end
                                   end
                                   # 最终转成要求的数组格式
                                   .map { |category, products| { category: category, products: products } }

方案优势： #

• 利用left_outer_join确保所有分类被保留，即使没有对应产品
• 仅一次数据库查询，避免N+1性能问题
• 用Rails内置的集合方法整理数据，代码可读性高，符合Rails开发习惯

二、纯SQL方案（极致性能，适合需要底层控制的场景） #

如果需要更极致的性能，可以直接用SQL在数据库层面生成目标JSON结构，减少Ruby内存处理：

SELECT 
  c.name AS category,
  -- 聚合产品为JSON数组，过滤掉无产品的情况
  COALESCE(
    json_agg(
      CASE WHEN p.id IS NOT NULL THEN json_build_object('id', p.id, 'name', p.name) ELSE NULL END
    ) FILTER (WHERE p.id IS NOT NULL),
    '[]'::json
  ) AS products
FROM categories c
-- 左外连接products，仅关联当前用户（John的id为1）的产品
LEFT OUTER JOIN products p 
  ON c.id = p.category_id 
  AND p.user_id = 1 -- 替换为实际的当前用户id
GROUP BY c.id, c.name
ORDER BY c.id;

在Rails中使用该SQL： #

可以通过find_by_sql直接获取结果：

current_user = User.find_by(name: 'John')
categories_data = Category.find_by_sql([
  <<~SQL,
    SELECT 
      c.name AS category,
      COALESCE(
        json_agg(
          CASE WHEN p.id IS NOT NULL THEN json_build_object('id', p.id, 'name', p.name) ELSE NULL END
        ) FILTER (WHERE p.id IS NOT NULL),
        '[]'::json
      ) AS products
    FROM categories c
    LEFT OUTER JOIN products p 
      ON c.id = p.category_id 
      AND p.user_id = ?
    GROUP BY c.id, c.name
    ORDER BY c.id;
  SQL
  current_user.id
])

# 转换为要求的哈希数组格式
result = categories_data.map { |item| { category: item.category, products: JSON.parse(item.products) } }

方案优势： #

• 数据库层面直接生成目标结构，减少Ruby端的数据处理开销
• FILTER子句精准过滤无效数据，避免生成包含NULL的产品条目
• 性能最优，适合数据量较大的场景

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