How to do multiple JOINS at once in Rails ActiveRecord-CodePudding

I got these two tables where one table is having multiple foreign keys to the second table.

Table rankings

 ---------------- -------------- ------ ----- --------- ---------------- 
| Field          | Type         | Null | Key | Default | Extra          |
 ---------------- -------------- ------ ----- --------- ---------------- 
| id             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| search_text    | varchar(255) | YES  | MUL | NULL    |                |
| first_item_id  | bigint(20)   | YES  | MUL | NULL    |                |
| second_item_id | bigint(20)   | YES  | MUL | NULL    |                |
| third_item_id  | bigint(20)   | YES  | MUL | NULL    |                |
| forth_item_id  | bigint(20)   | YES  | MUL | NULL    |                |
 ---------------- -------------- ------ ----- --------- ----------------

Table items

 --------------------------- -------------- ------ ----- --------- ---------------- 
| Field                     | Type         | Null | Key | Default | Extra          |
 --------------------------- -------------- ------ ----- --------- ---------------- 
| id                        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| item_code                 | varchar(255) | YES  | MUL | NULL    |                |
 ---------------- -------------- ------ ----- --------- ---------------------------

Sample data rankings

 ---- ------------- ------- -------- ------- ------- 
| id | search_text | first | second | third | forth |
 ---- ------------- ------- -------- ------- ------- 
|  1 | test 1      |     1 |      2 |     3 |     4 |
|  2 | test 2      |     1 |      2 |     3 |     4 |
|  3 | test 3      |     1 |      2 |     3 |     4 |
|  4 | test 4      |     1 |      2 |     3 |     4 |
 ---- ------------- ------- -------- ------- -------

Sample data items

 -------- ------------ 
| id     | item_code  |
 -------- ------------ 
|      1 | 125659     |
|      2 | 125660     |
|      3 | 125661     |
|      4 | 125662     |
 -------- ------------

At the moment im retrieving the ranking data as follows :

@rankings = Admin::Ranking.all

Expected data

 ---- ------------- ------- -------- ------- ------- 
| id | search_text | first | second | third | forth |
 ---- ------------- ------- -------- ------- ------- 
|  1 | test 1      | 125659| 125660 | 125661| 125662|
|  2 | test 2      | 125659| 125660 | 125661| 125662|
|  3 | test 3      | 125659| 125660 | 125661| 125662|
|  4 | test 4      | 125659| 125660 | 125661| 125662|
 ---- ------------- ------- -------- ------- -------

The SQL query to retrieve the expected data :

SELECT 
  r.id,
  r.search_text,
  i1.item_code AS first,
  i2.item_code AS second,
  i3.item_code AS third,
  i4.item_code AS forth
FROM rankings r
LEFT JOIN items i1 ON i1.id = r.first_item_id
LEFT JOIN items i2 ON i2.id = r.second_item_id
LEFT JOIN items i3 ON i3.id = r.third_item_id
LEFT JOIN items i4 ON i4.id = r.forth_item_id
ORDER BY r.id;

Model classes are as follow:

items -> Item, rankings -> Ranking

Any suggestion on how to retrieve ranking data in expected format using Rails ActiveRecord?

CodePudding user response：

Create a separate association for each foreign key.

class Ranking < ApplicationRecord
  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

Then you can just include all of them and query as needed.

rankings = Ranking.all.includes(:first_item, :second_item, :third_item, :forth_item)
rankings.each do |ranking|
  ranking.first_item.item_code # example
end

This is the ActiveRecord way. If you want to get exactly only the item_code-s and nothing else, you could .select it instead.