CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqlactiverecord
Myxtic
Myxtic

Reputation: 5699

Matching Array with Column of a Table

I have an array of certain dates called array_of_dates.

And I have a table called my_table with columns 'date' and 'count'.

Is there a way I can write an active records query to match the 'date' column in my_table with array_of_dates and return hashes ? All keys being the dates from array_of_dates, and all the values being the 'count' from my_table; returning zero as the value if a date present in array_of_dates doesn't exist in column 'date' of my_table.

Thanks in advance.

Upvotes: 0

Views: 255

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

The SQL could look like this:

SELECT date_col AS key, count(t.date_col) AS value
FROM  (
   SELECT unnest('{2012-07-08, 2012-07-09, 2012-07-10}'::date[]) AS date_col
   ) x
LEFT   JOIN tbl t USING (date_col)
GROUP  BY date_col;

Key elements are the subquery with unnest() and the LEFT JOIN instead of a plain join.

values will be 0 if no match is found for a date because count(t.date_col) does not count NULL values.

Upvotes: 1

Harish Shetty
Harish Shetty

Reputation: 64363

Try this:

Model.select("date, count").
  where(:date => array_of_dates).
    reduce(Hash.new(0)){ |h, r| h[r.date] = r.count;h}

Upvotes: 1

Related Questions