DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile


  • submit to reddit
        a string/text column with comma-separated values

class SQLListColumn < String
  def contains(value)
    "#{self} like '%#{SEPARATOR}#{value}#{SEPARATOR}%' or #{self} like '#{value}#{SEPARATOR}%' or #{self} like '%#{SEPARATOR}#{value}' or #{self} = '#{value}'"

Usage:'admins').contains('tmorgan') you a string you can use in a SQL query:

"admins like '%,tmorgan,%' or admins like 'tmorgan,%' or admins like '%,tmorgan' or admins = 'tmorgan'"

Rails usage might look like this:

  :conditions => \'admins').contains(username) \
    + ' or ' + \'viewers').contains(username),
  :order => 'name'


Snippets Manager replied on Mon, 2012/05/07 - 1:36pm

I'm of the opinion (and I realize I'm probably the only one with this opinion) that one can over-normalize. I used to go to the trouble of pulling every list-type structure into a separate table, but at some point realized it was overkill in some cases. Now, I just do what feels right... sometimes I use another table, sometimes a YAML-serialized text column, and sometimes just a comma-separated list in a column. I agree, this shouldn't be used instead of relational tables in most cases, but where it makes sense, it's a handy bit of code. That's all.

Snippets Manager replied on Fri, 2006/06/09 - 9:24am

If you are forced to use comma-separated values in a column, then this would be useful. However it would be better for design and performance if the database structure was normalized by moving the values into a related table and doing some joins. Rails makes this quite easy - it might be something like 'Program has_many :admins'.