Finding records which includes an id in an array attribute of the records

Question

I have an Exam model with lang_array field

Exam.last.lang_array #=> "1,2,5"

I want to find all Exams that have a particular id. I was able to do it for a single Exam like this:

Exam.last.lang_array.split(',').map(&:to_i).include? 1

How is it possible to do this for all the Exams and find the records efficiently?

Answer 1

You can't perform a search efficiently when you serialize a list of IDs in a single database column. In order to perform an efficient lookup databases use index, but you can't index aggregate data in that way.

The way you should properly construct your database is with a relationship. If the Exam model can have many "lang", then add a new table that properly represent such relationship. If the ID represented by lang is another database model, then that will become a many to many relationship.

You can add for instance a model ExamLang where you have two fields: the ID of the exam and the ID of the lang. Each record represents a single relationship.

When you want to fetch all the Exam that include a specific lang id, that will as easy as a simple SQL SELECT statement.

There is no efficient way to lookup the Exam by lang ID otherwise. You will have to perform a full scan (both a full DB table scan AND an entire loop on the Ruby side) which is inefficient.

Answer 2

Yes, I go with Simone's answer. You have to change your schema in order to accommodate many to many relationships.

But anyway if you want to find a record based on an entry in an array stored as a string in an attribute, you could do something like

Exam.where(["REPLACE(lang_array, ' ', '') REGEXP '/*,1,/*|/*1,/*|/*,1'"])

If Rails < 2.3

Exam.find(:all,:conditions =>["REPLACE(lang_array, ' ', '') REGEXP '/*,1,/*|/*1,/*|/*,1'"])

where 1 is the id.
PS: Please note that it is MySQL specific.