1

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?

Community
  • 1
  • 1
Richie Thomas
  • 3,073
  • 4
  • 32
  • 55
  • meetings = meetings.where("days in (?)", days_params), The meetings.where, is that meetings Model name ? or just an array of meeting objects ? – AshwinKumarS Sep 29 '13 at 07:50
  • 1
    @0v3rc10ck3d, is there a change in that code? – dax Sep 29 '13 at 07:51
  • Hey, just updated the code. I initialize 'meetings' by setting it equal to Meeting.order(:start_time). – Richie Thomas Sep 29 '13 at 07:52
  • @agentutah do you store `days` in db? Check SQL query: `meetings.where("days in (?)", days_params).to_sql` – NARKOZ Sep 29 '13 at 07:52
  • 1
    `meetings = meetings.where(:days => days_params)` – Zabba Sep 29 '13 at 07:52
  • 1
    @dax no i clicked on edit by mistake. nothing was edited – AshwinKumarS Sep 29 '13 at 07:52
  • @NARKOZ are you asking if days is an attribute in my Meeting model? The answer to that is yes, it's stored as an array of strings. – Richie Thomas Sep 29 '13 at 07:59
  • Zabba- I tried that, same thing: no results. Returns an empty array. – Richie Thomas Sep 29 '13 at 07:59
  • 1
    You've used a serialised array, which means that at DB level it's a VARCHAR. If you want to do Array-like things to it, it has to be deserialised first in Ruby, otherwise treat it as a string. Serialising objects that you want to use in logic like this is bad design. – Mike Campbell Sep 30 '13 at 09:16
  • So would a better practice be to make a Days model and have a many-to-many relationship between Meeting and Day instances? Or are there other best-practice options than that? I avoided creating a Day model until now because it seemed superfluous to create a model without any significant behavior and only one state, i.e. name ("Monday", "Tuesday", etc.). – Richie Thomas Sep 30 '13 at 21:13

2 Answers2

4

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)
AshwinKumarS
  • 1,303
  • 10
  • 13
  • I thought database performance was faster when using an ActiveRecord query than an enumerable method, no? – Richie Thomas Sep 29 '13 at 07:58
  • By the way, just tried this and it does indeed work. Just wondering about the database performance issues. – Richie Thomas Sep 29 '13 at 08:04
  • I didn't, don't know who did. Was thinking of choosing this as the answer, actually, if nothing better comes along. – Richie Thomas Sep 30 '13 at 06:33
  • @agentutah how are the days stored in DB ? Actually this should be done with a single where query itself instead of fetching by active record then again performing some operations on the resultant. – AshwinKumarS Sep 30 '13 at 06:34
  • Please see above. They're stored as an attribute of a Meeting instance, as an array of strings. – Richie Thomas Sep 30 '13 at 06:47
  • @agentutah i am sorry to bug you, but the days in db are stored in one single column as comma separated string ? like "moday,tuesday,wednesday" ?? which is converted on calling days methods ?? Or can you just giv me an example entry of a meeting record from your DB so that i can directly give you the activerecord single query solution, – AshwinKumarS Sep 30 '13 at 07:35
  • I added an example of a Meeting instance, as well as another Stack Overflow article explaining how you can store an array as an attribute by first storing it as a text attribute and then serializing it. – Richie Thomas Sep 30 '13 at 08:59
  • I required a SQL table entry, and not the object itself, i understand that the days contain array. But how is it stored in the back end. – AshwinKumarS Sep 30 '13 at 09:08
  • Not sure how to find that out. What command would I use in the terminal? I'm running Postgresql with Unix, if that helps. – Richie Thomas Sep 30 '13 at 09:48
  • Go into database. Find the table name for meeting and write a select query on the table for any of the meeting record. – AshwinKumarS Sep 30 '13 at 10:53
  • Can you break down how to go into the database into steps? I've never needed to do so this way before, and I don't see a specific database file in the project directory, like I might with SQLite3. – Richie Thomas Sep 30 '13 at 17:50
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38371/discussion-between-agentutah-and-0v3rc10ck3d) – Richie Thomas Sep 30 '13 at 21:02
3

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

Baldrick
  • 23,882
  • 6
  • 74
  • 79
  • the 'day' column is an array of strings, not a string itself. In other words, I'm comparing one array with another, trying to find the intersection of the two. – Richie Thomas Sep 29 '13 at 08:50