How to use the distinct method in Rails with Arel Table?-CodePudding

I am looking to run the following query in Rails (I have used the scuttle.io site to convert my SQL to rails-friendly syntax):

Here is the original query:

SELECT pools.name AS "Pool Name", COUNT(DISTINCT stakings.user_id) AS "Total Number of Users Per Pool" from stakings
INNER JOIN pools ON stakings.pool_id = pools.id
INNER JOIN users ON users.id = stakings.user_id
INNER JOIN countries ON countries.code = users.country
WHERE countries.kyc_flow = 1
GROUP BY (pools.name);

And here is the scuttle.io query:

    <%Staking.select(
    [
      Pool.arel_table[:name].as('Pool_Name'), Staking.arel_table[:user_id].count.as('Total_Number_of_Users_Per_Pool')
    ]
    ).where(Country.arel_table[:kyc_flow].eq(1)).joins(
      Staking.arel_table.join(Pool.arel_table).on(
        Staking.arel_table[:pool_id].eq(Pool.arel_table[:id])
      ).join_sources
    ).joins(
      Staking.arel_table.join(User.arel_table).on(
        User.arel_table[:id].eq(Staking.arel_table[:user_id])
      ).join_sources
    ).joins(
      Staking.arel_table.join(Country.arel_table).on(
        Country.arel_table[:code].eq(User.arel_table[:country])
      ).join_sources
    ).group(Pool.arel_table[:name]).each do |x|%>
<p><%=x.Pool_Name%><p>
<p><%=x.Total_Number_of_Users_Per_Pool%>
<%end%>

Now, as you may notice, sctuttle.io does not include the distinct parameter which I need. How in the world can I use distinct here without getting errors such as "method distinct does not exist for Arel Node?" or just syntax errors?

Is there any way to write the above query using rails ActiveRecord? I am sure there is, but I am really not sure how.

CodePudding user response：

Answer The Arel::Nodes::Count class (an Arel::Nodes::Function) accepts a boolean value for distinctness.

def initialize expr, distinct = false, aliaz = nil
  super(expr, aliaz)
  @distinct = distinct
end

The #count expression is a shortcut for the same and also accepts a single argument

def count distinct = false
  Nodes::Count.new [self], distinct
end

So in your case you could use either of the below options

Arel::Nodes::Count.new([Staking.arel_table[:user_id]],true,'Total_Number_of_Users_Per_Pool')
# OR
Staking.arel_table[:user_id].count(true).as('Total_Number_of_Users_Per_Pool')

Suggestion 1: The Arel you have seems a bit overkill. Given the natural relationships you should be able to simplify this a bit e.g.

country_table = Country.arel_table
Staking
  .joins(:pools,:users)
  .joins( Arel::Nodes::InnerJoin(
            country_table, 
            country_table.create_on(country_table[:code].eq(User.arel_table[:country])))
  .select( 
     Pool.arel_table[:name],
     Staking.arel_table[:user_id].count(true).as('Total_Number_of_Users_Per_Pool')
   )
  .where(countries: {kyc_flow: 1})
  .group(Pool.arel_table[:name])

Suggestion 2: Move this query to your controller. The view has no business making database calls.