CODEWITHSUNDEEP
ruby-on-railsactiverecord
Richie Thomas
Richie Thomas

Reputation: 3265

How to use ActiveRecord "where" query to check overlap between two arrays

I have a Meetings class which contains a 'days' attribute, which I've serialized into an Array. The method I used to store an array is described here:

Link- Array Attribute for Ruby Model

So for example, running:

Meeting.first.days

might return: 

["Monday", "Tuesday", "Wednesday"]

Here's an example of an entire Meeting object:

#<Meeting id: 7, address: "10 Canal Street", neighborhood: "Tribeca", building_name: "Yale Club", name: "Alumni Luncheon", start_time: "00:00", end_time: "00:35", notes: "", days: ["Tuesday", "Wednesday", "Thursday"], zip_code: 10055, special_interest: nil, meeting_type: nil, area: "Manhattan", latitude: 40.8104529, longitude: -73.9922805, created_at: "2013-09-29 22:02:29", updated_at: "2013-09-29 22:02:29">

I have a search form with checkboxes corresponding to the days of the week, so users can check which days they want to search for a meeting. For instance, a user might want to see any meetings whose days include Monday.

My expectation is for the search to return any Meeting object with "Monday" included in its 'days' array. But I'm having trouble using ActiveRecord to filter these meetings. So far I have:

meetings = Meeting.order(:start_time)
meetings = meetings.where("days in (?)", days_params)

But this keeps filtering the 'meetings' variable down to 0 results. I could be wrong but I think the problem is that this only works if 'days' is not an array, i.e. if I compare a string with an array then it might work. Since I need to compare the union of two arrays, anyone have an idea?

Upvotes: 1

Views: 1705

Answers (2)

AshwinKumarS
AshwinKumarS

Reputation: 1313

Well since meetings has an array of object you can use select.

meetings.select{|i|*compare the days array/check for whatever you want to*}

That should do. If the days are stored as a string you can directly parse in a single request.

meetings = Meeting.where("days like '%Monday%'",days_selected).order(:field_name)

Upvotes: 4

Baldrick
Baldrick

Reputation: 24340

If I understood correctly, the column day in the meetings table is a String, so you can use % (match any string) in your SQL request :

meetings.where("days LIKE '%?%'", days_params)

Assuming days_param is a string like 'Monday', it will find if Monday is in the serialized string of the array.

If you're concerned by performance, it's not a good design: this request does a full scan of the meetings table to find all rows with 'Monday'. A better solution would be to create a Day model, and relation has_and_belongs_to_many, this way the request would use the meeting_days table and would be much faster (do not forget to create the indexes in the migration).

Upvotes: 3

Related Questions