Rails using joins retrieve repeated column

Question

My association

class Website < ApplicationRecord
  has_many :settings
  has_one :shop
end

Setting and Shop tables value are:

2.6.1 :003 > Setting.all.pluck(:records)
=> [2, 2, 4, 0, 0, 0]

2.6.1 :003 > Shop.all.pluck(:records)
=> [4, 1, 1]

Using joins

Website.joins(:settings, :shop).where("websites.id = ?", 2).pluck("settings.records", "shops.records")

I am getting repeated value

[[2, 4], [2, 1], [2, 1], [2, 4], [2, 1], [2, 1], [4, 4], [4, 1], [4, 1], [0, 4], [0, 1], [0, 1], [0, 4], [0, 1], [0, 1], [0, 4], [0, 1], [0, 1]]

The result i want like

[[2, 4], [2, 1], [4, 1], [0, 0], [0, 0], [0, 0]]

How can i resolve the problem?

Thanks in advance :)

`Website.joins(:settings, :shop).where("websites.id = ?", 2).pluck("settings.records", "shops.records AND settings.website_id = ? AND shops.website_id = ?",2,2).pluck("settings.records", "shops.records")` try this — uzaif, Feb 27 '20 at 07:27
`ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND shops.website_id = ?, 2 FROM `websites` INNER JOIN `setting' at line 1)` syntax error — Nikund Lathiya, Feb 27 '20 at 08:20
`Website.joins(:settings, :shop).where("settings.website_id = ? AND shops.website_id = ?",2,2).pluck("settings.records","shops.records")` return same result — Nikund Lathiya, Feb 27 '20 at 08:23
Can you provide the results of these two queries: `Shop.pluck(:website_id, :records)` and `Setting.pluck(:website_id, :records)` ? — Sibevin Wang, Feb 27 '20 at 23:38
`Shop: [[2, 2], [2, 2], [2, 4], [2, 0], [2, 0], [2, 0]] and Setting: [[2, 4], [2, 1], [2, 1]]` — Nikund Lathiya, Feb 28 '20 at 04:46
Uh ... the record values you provided in the question and comment seem different, but I guess one of them is a typo. The join query would list all combination of settings and shops associated to the website whose id = 2 and `[[2, 4], [2, 1], [2, 1], [2, 4], [2, 1], [2, 1], [4, 4], [4, 1], [4, 1], [0, 4], [0, 1], [0, 1], [0, 4], [0, 1], [0, 1], [0, 4], [0, 1], [0, 1]]` is the expected result. I want to know why you expect that the query result is `[[2, 4], [2, 1], [4, 1], [0, 0], [0, 0], [0, 0]]` ? — Sibevin Wang, Feb 29 '20 at 00:26

score 0 · Answer 1 · answered Feb 29 '20 at 00:49

I am not sure why you expect the result is [[2, 4], [2, 1], [4, 1], [0, 0], [0, 0], [0, 0]](maybe it is a typo?), but if you want to get the non-repeat result from the join query, you can use group + distinct:

Website.joins(:settings, :shop).where("websites.id = ?", 2).group("settings.records", "shops.records").distinct.pluck("settings.records", "shops.records")
=> [[0, 1], [0, 4], [2, 1], [2, 4], [4, 1], [4, 4]]

Rails using joins retrieve repeated column

1 Answers1