CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3.2
webster
webster

Reputation: 4012

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

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?

Upvotes: 0

Views: 137

Answers (2)

BinaryMee
BinaryMee

Reputation: 2142

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.

Upvotes: 1

Simone Carletti
Simone Carletti

Reputation: 176352

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.

Upvotes: 2

Related Questions