Active Record: Remove element from PostgreSQL array

Question

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.

Diego Castillo · Accepted Answer · 2020-01-23 21:35:25Z

4

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)

edited Jan 23, 2020 at 21:35

answered Jan 23, 2020 at 19:46

Diego Castillo

3554 silver badges11 bronze badges

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

1 Comment

Ismael Padilla Over a year ago

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you’ve made.

max · Accepted Answer · 2020-01-23 22:31:22Z

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.

Collectives™ on Stack Overflow

Active Record: Remove element from PostgreSQL array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related