CODEWITHSUNDEEP
ruby-on-railsrubydatabasepostgresqlruby-on-rails-4
Shefalee Chaudhary
Shefalee Chaudhary

Reputation: 602

Postgres database query to filter active record result

I have three tables

  1. Company
  2. Code
  3. CompanyCode [join table] - to store company codes

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.

Upvotes: 2

Views: 906

Answers (3)

pumbo
pumbo

Reputation: 3826

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).

Upvotes: 3

Hieu Pham
Hieu Pham

Reputation: 6707

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:

  • The sub_query just gets the company_id which has all required_codes and the distinct codes after grouping must equal the required_codes
  • After having company_id, we can easily query Company

Upvotes: 0

IngoAlbers
IngoAlbers

Reputation: 5802

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:

  • Get all companies that have codes
  • which contain one of the required codes
  • Group them, so that we have for example a group of 3 when the article has the codes 2, 3, 5, or just a group of 2 when the article has the tags 2, 3 and 6.
  • Then only take those groups with as many members as specified in our required codes array.

Upvotes: 1

Related Questions