CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Diego Castillo
Diego Castillo

Reputation: 355

Active Record: Remove element from PostgreSQL array

Assume there's an active record model called Job which has an array column follower_ids. I have already created a scope which allows to fetch all jobs followed by a user:

# Returns all jobs followed by the specified user id
def self.followed_by(user_id)
  where(
    Arel::Nodes::InfixOperation.new(
      '@>',
      Job.arel_table[:follower_ids],
      Arel.sql("ARRAY[#{user_id}]::bigint[]")
    )
  )
end

# Retrieve all jobs followed by user with id=1
Job.followed_by(1)

Is there a way to remove specific elements from the follower_ids column using the database (i.e., not looping through the active record objects and manually calling delete/save for each of them)?

For instance, it'd be nice to do something like Job.followed_by(1).remove_follower(1) to remove user with id=1 from all those jobs' follower_ids with just one query.

Upvotes: 5

Views: 1540

Answers (2)

max
max

Reputation: 102433

I think this is really a XY problem caused by the fact that you are using an array column where you should be using a join table.

The main reasons you don't want to use an array are:

  • If user is deleted you would have to update every row in the jobs table instead of just removing rows in the join table with a cascade or the delete callback.
  • No referential integrity.
  • Horrible unreadable queries. Its really just a marginal step up from a comma separated string.
  • Joins are not really that expensive. "Premature optimzation is the root of all evil".
  • You can't use ActiveRecord associations with array columns.

Create the join model with rails g model following user:references job:references. And then setup the assocations:

class Job < ApplicationRecord
  has_many :followings
  has_many :followers,
    through: :followings,
    source: :user
end

class User < ApplicationRecord
  has_many :followings
  has_many :followed_jobs,
    source: :job,
    through: :followings,
    class_name: 'Job'
end

To select jobs followed by a user you just do a inner join:

user.followed_jobs

To get jobs that are not followed you do an outer join on followings where the user id is nil or not equal to user_id.

fui = Following.arel_table[:user_id]
Job.left_joins(:followings)
   .where(fui.eq(nil).or(fui.not_eq(1)))

If you want to unfollow a job you just remove the row from followings:

Following.find_by(job: job, user: user).destroy
# or 
job.followings.find_by(user: user).destroy
# or
user.followings.find_by(job: job).destroy

You can automatically do this with the when the job or user is destroyed with the dependent: option.

Upvotes: 2

Diego Castillo
Diego Castillo

Reputation: 355

I ended using the PostgreSQL array_remove function, which allows to remove a value from an array as follows:

user_id = 1
update_query = <<~SQL
  follower_ids = ARRAY_REMOVE(follower_ids, :user_id::bigint),
SQL
sql = ActiveRecord::Base.sanitize_sql([update_query, { user_id: user_id }])
Job.followed_by(user_id).update_all(sql)

Upvotes: 4

Related Questions