I have three tables
Now, I want to find records for companies who have code 2,3 and 5 only.
I am trying with
Comapny.joins(:codes).where('codes.n_code' IN ? , [2,3,5])
but it will result all companies with only n_code = 2 or n_code = 3 or n_code = 5.
I want companies with n_code 2,3 and 5 with all these three values.
None of the previous answers is accurate. If you need to get records that have all specified values, but not only some of the values, you don't need to use WHERE n_code IN (2,3,5) condition, because it will get all records that have at least one of the values, but not all of them at the same time. Also, additional condition HAVING count(codes.n_code) = 3, as suggested earlier, is not a solution, as it does not guarantee that these 3 values are actually 2, 3 and 5.
I am not good at Ruby, so I will give you an example in plain SQL how you can get records that have all of the values:
SELECT company_codes.company_id
FROM company_codes
JOIN codes ON codes.id = company_codes.code_id
GROUP BY company_codes.company_id
HAVING array_agg(codes.n_code) @> ARRAY[2, 3, 5]
How it works: in HAVING section you aggregate all n_code values for a company into an array (note that the query is grouped by a company) and check that it contains the array on the right (contains all of its values).
You could do something like this:
required_codes = [2, 3, 5]
Company.joins(:codes)
.where(codes: { n_code: required_codes })
.group('companies.id')
.having('count(*) = ?', required_codes.count)
Maybe your group clause has to look a bit different depending on your table and field naming.
What it does is:
Let do the query like this:
required_codes = [2, 3, 5]
sub_query = %Q{
SELECT company_codes.company_id
FROM company_codes
JOIN codes
ON codes.id = company_codes.code_id
WHERE codes.n_code IN (#{required_codes.join(',')})
GROUP BY company_codes.company_id
HAVING COUNT(DISTINCT codes.n_code) = #{required_codes.size}
}
Company.where("id IN (#{sub_query})"
Explanation:
sub_query just gets the company_id which has all required_codes and the distinct codes after grouping must equal the required_codescompany_id, we can easily query Company
codestable three times. Please, correct me if I am wrong..joinbecause of rails associations rails is smart enough to automatically to perform the join on company codes and then codes for you.array_aggto gel a list of codes for every company and then filter for arrays that contain[2,3,5]