Activerecord "where" condition for Array attribute

Question

I have model course.rb with pre_courses is an array.

class Course < ActiveRecord::Base
  serialize :pre_courses, Array
end

Now I want to check if an exist course is pre_course of any course by Activerecord or raw SQL (I am using MySQL), such as Course.where("pre_courses INCLUDEs self.id").

Is there any way to do this?

Try this Course.includes(:pre_courses).where.not(pre_courses: {id: nil} ") or Course.includes(:pre_courses).where.not(pre_courses: {id: nil} ").find(self.id) — Vishal
– Vishal, Commented Aug 1, 2017 at 4:33
Your first step should really be to replace serialize with a separate table. — mu is too short
– mu is too short, Commented Aug 1, 2017 at 4:56
Queries with LIKE tend to be very slow with many columns and are hard to optimize. As soon as I need to query by a serialized data column with LIKE I would consider another data structure: Perhaps a JSON or a normalized table structure. — spickermann
– spickermann, Commented Aug 1, 2017 at 6:32

Gerry · Accepted Answer · 2017-08-01 05:23:34Z

2

A serialized array is just a string in the db, so try using LIKE, for example:

Course.where("pre_courses LIKE ?", "% #{self.id}\n%")

Notice that a serialzed array adds a space before each item and a new line after, thus the added space before the interpolated string and the \n at the end.

edited Aug 1, 2017 at 5:23

answered Aug 1, 2017 at 4:41

Gerry

10.5k3 gold badges33 silver badges40 bronze badges

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

2 Comments

mu is too short Over a year ago

What happens if 21 is in the YAMLized array but you're searching for 2?

Gerry Over a year ago

@muistooshort That specific case shouldn't be a problem, because of the \n after 2; but it will be for any number that ends with 2. So a space must be added before the number; i'll update the answer. Thank you.

m. simon borg · Accepted Answer · 2017-08-01 05:37:16Z

It sounds like a pre_course is actually a regular course, a course can have many pre_courses and a pre_course can belong to many courses. A self referential has_many through relationship is possible and may give you more flexibility to work with your data than serializing it as an array.

You'll need a join model I'll call CoursePreCourse. It will have the columns course_id and pre_course_id. pre_course_id will be a foreign key for records on the courses table.

class CreateCoursePreCourses < ActiveRecord::Migration[5.1]
  def change
    create_table :course_pre_courses do |t|
      t.references :course, foreign_key: true
      t.references :pre_course, foreign_key: { to_table: :courses }

      t.timestamps
    end
  end
end

class CoursePreCourse < ApplicationRecord
  belongs_to :course
  belongs_to :pre_course, class_name: 'Course'
end

class Course < ApplicationRecord
  # A straight-forward has_many :through association for a course that has_many :pre_courses
  has_many :course_pre_courses
  has_many :pre_courses, through: :course_pre_courses

  # A little coercion is necessary to set up the association as a pre_course that has_many :courses
  has_many :pre_course_courses, class_name: 'CoursePreCourse', foreign_key: :pre_course_id
  has_many :courses, through: :pre_course_courses
end

Now you can retrieve the courses that are pre_courses of any course with course.pre_courses. If you want to see if a course is a pre_course of other courses it's pre_course.courses. Similarly you can add a course to another course's pre_courses with either course.pre_courses << pre_course or pre_course.courses << course.

Do you ever say a word so many times it loses its meaning?

Collectives™ on Stack Overflow

Activerecord "where" condition for Array attribute

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related