基于Postgres的Rails has_many :through关联查询单条匹配/未匹配记录

Hey there! Let's work through this problem to get the clean, single-row-per-group result you're looking for. First, let's make sure our Rails model associations are set up correctly—this will make querying much smoother.

Step 1: Confirm Model Associations #

First, define the proper has_many :through relationships in your Rails models to mirror the database structure:

Group Model #

class Group < ApplicationRecord
  belongs_to :user
  has_many :favourites_groups, dependent: :destroy
  has_many :favourites, through: :favourites_groups
end

FavouritesGroup Model #

class FavouritesGroup < ApplicationRecord
  belongs_to :group
  belongs_to :favourite
  # Enforce the unique index constraint at the model level too
  validates :group_id, uniqueness: { scope: :favorite_id }
end

Favourite Model #

class Favourite < ApplicationRecord
  belongs_to :user
  has_many :favourites_groups, dependent: :destroy
  has_many :groups, through: :favourites_groups
end

Step 2: Optimized SQL Query #

Your original SQL returned duplicate rows because the DISTINCT ON clause included too many fields (group_id, favorite_id, product_id). This meant multiple rows per group were retained if there were multiple favorites linked to it.

We need to only deduplicate by group ID and ensure that if a group has a matching favorite (for the given product), that row is prioritized over the NULL row. Here's the fixed SQL:

SELECT DISTINCT ON (groups.id)
  groups.id AS group_id,
  groups.name AS group_name,
  favorites.id AS favorite_id,
  favorites.product_id AS product_id
FROM groups
LEFT JOIN favourites_groups ON favourites_groups.group_id = groups.id
LEFT JOIN favorites ON favorites.id = favourites_groups.favorite_id 
  AND favorites.product_id = :target_product_id
WHERE groups.user_id = :target_user_id
ORDER BY groups.id, favorites.id DESC;

Key Improvements: #

• DISTINCT ON (groups.id): Guarantees only one row per group is returned.
• ORDER BY groups.id, favorites.id DESC: Ensures rows with a matching favorite (non-NULL favorite_id) are prioritized over NULL rows (since NULL values sort lower than any integer in Postgres).

Step 3: Rails ActiveRecord Implementation #

If you prefer using Rails' ActiveRecord (more maintainable than raw SQL), here's how to translate the above query:

# Replace with your actual user_id and product_id values
target_user_id = 100
target_product_id = 1002

Group
  .select(
    'groups.id AS group_id',
    'groups.name AS group_name',
    'favorites.id AS favorite_id',
    'favorites.product_id AS product_id'
  )
  .left_joins(favourites_groups: :favourite)
  .where(groups: { user_id: target_user_id })
  .distinct_on(:id)
  .order(:id, 'favorites.id DESC')

How This Works: #

• left_joins(favourites_groups: :favourite): Performs nested left joins to include all groups, even those without linked favorites.
• distinct_on(:id): Rails' wrapper for Postgres' DISTINCT ON clause, targeting the group ID.
• The order clause ensures matching favorite rows are picked first for each group.

Why Your Original Query Failed #

Your original DISTINCT ON (group_id, favorite_id, product_id) meant any combination of those three fields would be treated as a unique row. For groups with multiple linked favorites, this resulted in multiple rows (one for each favorite, including NULLs). By narrowing DISTINCT ON to only the group ID and adding a proper sort, we ensure each group returns exactly one row—either with matching favorite data or NULLs if no match exists.

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