Where like query with array

Question

I have a Track model with 4 columns of comma separated string values that need to be searchable by any number of terms.

e.g.

Track.first.genre => "Alternative, Lite Rock, Indie Rock, Psychedelic"
Track.first.mood => "Aggressive, Angry, Dark, Driving, Energetic, Heavy"
Track.first.tempo => "Fast, Medium"
Track.first.artist => "something"

A user then can use any number of terms to narrow down the list of displayed Tracks. I am collecting the terms into arrays matching each column. e.g.

genres = params[:genre].split(",")
moods = params[:mood].split(",")

and so on. So if genres => ["Rock", "Alternative"], that would match all Tracks which contain either of those terms in the genre column, and all additional terms would select from that returned array, and so on for each of the 4 columns.

What is the best way to do this, and how do I write a where query using like with an array as the condition?

I think you might need to re-evaluate your entities and relationships... It seems that genres/moods/tempi have a many-to-many relationship with the tracks. — Platinum Azure
– Platinum Azure, Commented Jun 12, 2012 at 19:58
@PlatinumAzure - Those are all attributes of the Track model. Originally, each was going to be a single string, but the client has since added multiple strings into each column rendering my ("genre in (?)", genres) query unusable. — aperture
– aperture, Commented Jun 12, 2012 at 20:01
I am saying your design (having those be attributes of the track model) probably needs to be reworked. They should be their own models. You'll thank me later. — Platinum Azure
– Platinum Azure, Commented Jun 12, 2012 at 20:55
Agree with PA. Once the client added the requirement of multiple genres, the right solution would be to break out that relationship into a many-to-many with genres in their own table. — Emily
– Emily, Commented Jun 12, 2012 at 21:17

Mori · Accepted Answer · 2012-06-13 03:41:43Z

1

^ Consider using models for genre, mood & tempo. ActiveRecord will make queries that involve those a lot easier.

^ The serialize declaration could save you some translating.

^ You can just search for the array elements you want with your where call, e.g.:

class Track < ActiveRecord::Base
  def self.for_genre(name)
    where "genre like '%#{name}%'"
  end
end

This means choosing names that aren't substrings of other names.

edited Jun 13, 2012 at 3:41

answered Jun 12, 2012 at 20:06

Mori

27.8k10 gold badges71 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Where like query with array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related