SQL JOIN to SELECT - is there an ActiveRecord way to do this?

Question

I have an SQL statement like this. Is this type of statement possible entirely or mostly in ActiveRecord?

UPDATE users
JOIN (
  SELECT user_id, count(*) AS num
  FROM posts
  GROUP BY user_id
) AS counts ON users.id = counts.user_id
SET users.publications = counts.num
WHERE users.publications != counts.num;

1. How can I do the JOIN from a table "users" to the results of a SELECT subquery that's been grouped by one of the selected values?

2. Can I alias the join so i can compare values between the users table and the join?

I'm having a hard time searching for this because I don't know the term to call a JOIN that's not a join between tables, but a join from a table to a specially formatted group of results. I don't think AR's magic can work with that, but i'm not sure.

Answer 1

I'm assuming that your models are related like user has_many posts and post belongs_to user.

In this case, you would be better advised to add a column called posts_count and change the relationship in User to has_many :posts, counter_cache: true.

This will keep a count of a user's posts in the User model, in the posts_count field, automatically updated whenever a user creates a new post. If you really need it to be called publications, add def publications; posts_count; end to the user model.

When adding this counter cache, you will need to initialize it for all the existing users. This is a one-time operation, so you can do it from the Rails console like this: User.all.each{|u| u.reset_counters(u.id, :posts_count)}.

Answer 2

What database is used in your project? In SQL Server the syntax is like this:

UPDATE A
SET users.publications = counts.num
from users as A
JOIN (
    SELECT user_id, count(*) AS num
    FROM posts
    GROUP BY user_id
) AS counts ON users.id = counts.user_id
WHERE users.publications != counts.num

I believe it is ANSI SQL but I'm not sure

Answer 3

Using Arel you can hack together any valid and/or invalid SQL statements; however more complex statements generally require using classes you wouldn't think to use (or make reasonable sense in the code) *See Notes below.

For instance you can construct your desired SQL using:

users = Arel::Table.new('users') # User.arel_table
posts = Arel::Table.new('posts') # Post.arel_table
counts = Arel::Table.new('counts')

subquery = Arel::Nodes::TableAlias.new(
  # This could be converted to 
  # Post.select(:user_id, Arel.star.count.as('num')).group(:user_id).arel
  posts.project(posts[:user_id],Arel.star.count.as('num')).group(posts[:user_id]),
  counts.name)

upd_stmt = Arel::Nodes::UnaryOperation.new(users.name,
  Arel::Nodes::InnerJoin.new(subquery,subquery.create_on(users[:id].eq(counts[:user_id])))
)

updater = Arel::UpdateManager.new
updater.table(upd_stmt)
updater.set({users[:publications] => counts[:num]})
updater.where(users[:publications].not_eq(counts[:num]))

Then updater.to_sql will produce:

UPDATE users 
  INNER JOIN (
    SELECT 
      posts.user_id, 
      COUNT(*) AS num 
    FROM 
      posts 
    GROUP BY 
      posts.user_id) counts ON users.id = counts.user_id 
SET publications = counts.num 
WHERE 
  users.publications != counts.num

Whether or not this is functional I cannot say but it does produce the desired SQL.

Note: As you can see we used a UnaryOperation to create the top half of the UPDATE statement. This is obviously odd because these operations are generally for basics like - 1 or + 1 but understanding that when visited this class will construct SQL as [operator] [operand] we can utilize this understanding (no matter how debasing it might be) to construct other statements.