Rails/Arel: select case sum along with model-CodePudding

I have a model called Post (table posts), which has a relationship with a model called User (table users), which is referenced as "uploader" in the Post model (the posts table has a column called uploader_id which is the id of the table users). An User can have many Posts, some of which can be deleted (marked through the is_deleted boolean column in posts).

How do I get a list of users with the number of total and deleted posts?

::Post
  .select(
    Arel.star.count.as("total"),
    Arel::Nodes::Case.new.when(Post.arel_table[:is_deleted]).then(1).sum.as("deleted_count"),
    :uploader
   )
  .joins(:uploader)
  .group(:uploader)
  .order(:total, :deleted_count)
  .limit(20)

This does not work. I need the user to be returned as model, the uploader_id is not sufficient.

I've tried many attempts at this but no matter what I do I can't figure it out. Either it tries to insert "#<Arel::Nodes::Count:0x0000565..." as a literal in the SQL string, or it returns arrays of nils.

What's extremely frustrating to me is that this works instead, if I only want the total posts and the user:

::Post
  .joins(:uploader)
  .group(:uploader)
  .order(Arel.sql("count(*) desc"))
  .limit(20)
  .count

I just can't figure out how to add the conditional case sum, I'm a novice at rails and the documentation does not help.

CodePudding user response：

"How do I get a list of users with the number of total and deleted posts?" seems like you are going about this in the wrong direction. Better to start with User since you want User objects to be returned.

Also Post.arel_table[:is_deleted] is not a condition this should be Post.arel_table[:is_deleted].eq(1) or whatever is "true" in your database.

Try the following instead:

User.left_joins(:posts)
  .select(
     User.arel_table[Arel.star],
     Post.arel_table[Arel.star].count.as('total_posts'),
     Arel::Nodes::Case.new 
       .when(Post.arel_table[:is_deleted].eq(1)).then(1)
       .else(0).sum.as('deleted_count'))
  .group(:id)
  .order(
    Post.arel_table[Arel.star].count, 
    Arel::Nodes::Case.new 
      .when(Post.arel_table[:is_deleted].eq(1)).then(1)
      .else(0).sum)
  .limit(20)