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 item
+---------------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | item_code | varchar(255) | YES | MUL | NULL | | +----------------+--------------+------+-----+---------+---------------------------+
One ranking record may have multiple association to item table using first_item_id, second_item_id, third_item_id or forth_item_id fields. I want to retrieve ranking records with the corresponding item_code instead of the item.id. What would be the most efficient way to do this if i have a big number of data?
PS: There are 10 associations to the item.id as first_item_id … tenth_item_id. Im using Rails ActiveRecord ORM. Any workaround with that also fine.
Sample data ranking
SELECT id,search_text,first_item_id as first,second_item_id as second,third_item_id as third,forth_item_id as forth from rankings limit 10;
+----+-------------+-------+--------+-------+-------+ | 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 item data
SELECT id,item_code from items limit 5;
+--------+------------+ | id | item_code | +--------+------------+ | 1 | 125659 | | 2 | 125660 | | 3 | 125661 | | 4 | 125662 | +--------+------------+
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| +----+-------------+-------+--------+-------+-------+
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Joining the table multiple times (even many, many times) should not be a problem, as you are joining on the primary key, i.e. you have an index that will be used.
select r.id, r.search_text, i1.item_code as item_code_1, i2.item_code as item_code_2, i3.item_code as item_code_3, i4.item_code as item_code_4 from rankings r left join item i1 on i1.id = r.first_item_id left join item i2 on i2.id = r.second_item_id left join item i3 on i3.id = r.third_item_id left join item i4 on i4.id = r.forth_item_id order by r.id;
I am using outer joins here, because all your item columns are nullable.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0