Ruby on Rails多模型JOIN查询：空关联字段的数据返回实现

Got it, let's work through this. The core issue here is ensuring that when we search across associated models, records where the shared/association fields are nil (like a Technician with no WorkContracts, or a WorkContract without a WorkContractType) don't get filtered out. Your existing helper works for User (which probably doesn't have complex associations you're searching across), so we'll adapt that pattern with LEFT JOINs and adjusted WHERE conditions.

First, let's assume your model associations look like this (adjust if your actual associations differ):

class Technician < ActiveRecord::Base
  has_many :work_contracts
end

class WorkContract < ActiveRecord::Base
  belongs_to :technician
  belongs_to :work_contract_type
end

class WorkContractType < ActiveRecord::Base
  has_many :work_contracts
end

Step 1: Build the Search Helper #

Create or update app/helpers/search_helper.rb with this method tailored to your models:

module SearchHelper
  def search_technicians(search_params = {})
    # Start with a LEFT JOIN to preserve all Technician records, even those without WorkContracts/Types
    query = Technician.left_joins(work_contracts: :work_contract_type)

    # Filter on Technician's own fields (example: name, replace with your actual fields)
    if search_params[:technician_name].present?
      query = query.where("technicians.name ILIKE ?", "%#{search_params[:technician_name]}%")
    end

    # Filter on WorkContract fields - add OR condition to include records with no WorkContract
    if search_params[:contract_number].present?
      query = query.where(
        "work_contracts.contract_number ILIKE ? OR work_contracts.id IS NULL",
        "%#{search_params[:contract_number]}%"
      )
    end

    # Filter on WorkContractType fields - add OR condition to include records with no WorkContractType
    if search_params[:contract_type_name].present?
      query = query.where(
        "work_contract_types.name ILIKE ? OR work_contract_types.id IS NULL",
        "%#{search_params[:contract_type_name]}%"
      )
    end

    # Avoid duplicate Technician records from multiple associated WorkContracts
    query.distinct
  end
end

Key Details Explained #

• left_joins instead of joins: joins uses INNER JOIN, which drops records where there's no matching association. left_joins (LEFT OUTER JOIN) keeps all Technician records, filling in nil for association fields when there's no match.
• OR [association_table].id IS NULL: This ensures that even if a Technician has no WorkContract (or a WorkContract has no Type), they still show up if they match other search criteria.
• distinct: Prevents duplicate Technician rows when a single Technician has multiple WorkContracts.

Step 2: Use the Helper in Your Controller #

Include the helper in your controller and call the method with search params:

class TechniciansController < ApplicationController
  include SearchHelper

  def index
    @technicians = search_technicians(params[:search])
  end
end

Make It Generic (For Reuse Across Models) #

If you want a helper that works for any model (like your existing User helper), here's a flexible version:

module SearchHelper
  def search_records(model, associations, search_params = {})
    query = model.left_joins(associations)

    search_params.each do |field_key, value|
      next if value.blank?

      # Split field into table and column (e.g., "work_contracts.contract_number")
      table_name, column_name = field_key.split('.')

      if table_name && column_name
        # Handle associated table fields - allow nil associations
        query = query.where(
          "#{table_name}.#{column_name} ILIKE ? OR #{table_name}.id IS NULL",
          "%#{value}%"
        )
      else
        # Handle model's own fields
        query = query.where("#{model.table_name}.#{field_key} ILIKE ?", "%#{value}%")
      end
    end

    query.distinct
  end
end

Usage Examples #

# Search Technicians with their associations
@technicians = search_records(Technician, {work_contracts: :work_contract_type}, params[:search])

# Search Users (your existing use case)
@users = search_records(User, [], params[:search])

Notes to Adjust for Your Project #

• Replace example fields (like name, contract_number) with your actual model column names.
• If using MySQL instead of PostgreSQL, replace ILIKE with LIKE (or use LOWER() for case-insensitive searches: LOWER(technicians.name) LIKE LOWER(?)).
• Double-check your model associations to ensure the left_joins chain matches your actual relationship structure.

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