如何使用Rails ActiveRecord实现多JOIN查询获取关联同表的格式化数据

第一步：配置模型关联 #

首先在Admin::Ranking模型中声明4个指向Item的关联，匹配表结构的外键字段：

# app/models/admin/ranking.rb
class Admin::Ranking < ApplicationRecord
  # 外键默认和关联名匹配，无需额外指定foreign_key
  belongs_to :first_item, class_name: 'Item'
  belongs_to :second_item, class_name: 'Item'
  belongs_to :third_item, class_name: 'Item'
  belongs_to :forth_item, class_name: 'Item'
end

第二步：执行查询 #

提供两种常用写法，均可实现需求：

写法1：基于关联查询（更符合Rails规范） #

@rankings = Admin::Ranking
  .select(
    :id,
    :search_text,
    'first_items.item_code AS first',
    'second_items.item_code AS second',
    'third_items.item_code AS third',
    'forth_items.item_code AS forth'
  )
  .left_joins(:first_item, :second_item, :third_item, :forth_item)
  .order(id: :asc)

写法2：手动指定JOIN别名（和原生SQL逻辑完全对齐） #

@rankings = Admin::Ranking
  .select(
    'rankings.id',
    'rankings.search_text',
    'i1.item_code AS first',
    'i2.item_code AS second',
    'i3.item_code AS third',
    'i4.item_code AS forth'
  )
  .joins('LEFT JOIN items i1 ON i1.id = rankings.first_item_id')
  .joins('LEFT JOIN items i2 ON i2.id = rankings.second_item_id')
  .joins('LEFT JOIN items i3 ON i3.id = rankings.third_item_id')
  .joins('LEFT JOIN items i4 ON i4.id = rankings.forth_item_id')
  .order('rankings.id ASC')

结果使用说明 #

查询返回的Admin::Ranking实例可以直接访问first/second/third/forth属性获取对应的item_code值，调用@rankings.as_json可直接输出和预期结构完全一致的数组数据。

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