SQLListColumn

• Tweet
• 

        a string/text column with comma-separated values

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

Usage:

SQLListColumn.new('admins').contains('tmorgan')

...gives 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:

Program.find(
  :all,
  :conditions => \
    SQLListColumn.new('admins').contains(username) \
    + ' or ' + \
    SQLListColumn.new('viewers').contains(username),
  :order => 'name'
)

Tags:
• Ruby
• SQL

Comments

Snippets Manager replied on Mon, 2012/05/07 - 2: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.

• Login or register to post comments

Snippets Manager replied on Fri, 2006/06/09 - 10: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'.

• Login or register to post comments