Filtering out all users but last 7 days

[monroemann]

Hi @tute. Alas, we have another issue and I'm hoping you can help us solve it. This is our code:

<div>
  <h4 style="color: red">Last 7 Days New Members(Top 25)</h4>
  <!-- A -->
  <% Merit::Score.top_scored(since_date: 7.days.ago, limit: 25).map do |top_score_hash| %>
    <div class="col-md-6">
      <!-- B -->
      <% if User.where('created_at >= ?', 7.days.ago) %>
        <strong>
          <%= User.where('created_at >= ?', 7.days.ago).find(top_score_hash["user_id"]).full_name %>
        </strong>
      <% end %> 
      <div class="col-md-6 leaderboard-score">
        <%# top_score_hash["sum_points"].to_s %>
      </div>
      <br>
    <% end.to_sentence %>
  </div> # EDIT: Tute added a closing div tag
</div>

Line A creates the regular Merit top_score_hash with all the points earned by all members in the last 7 days. That works fine. But Line B is not working. We want to only show the 7 day score totals of members who joined in the last 7 days.

We know something is wrong because the line A loop is going through EVERY user who has earned points in the last 7 days, so when it runs line B, we get an error, because some of those users are not 'new', and so it can't find that user.

Bottom line: we are trying to show the 7 day scores of only those users who joined in the last 7 days. Do you perhaps see our error? Thank you!

[tute]

Hi! Can you paste the extension to merit you have in your application to have the top_scored method available? Thanks!

[monroemann]

Hi @tute Here is merit_scores_overide.rb:

module Merit
  class Score < ActiveRecord::Base
    # Meant to display a leaderboard. Accepts options :table_name (users by
    # default), :since_date (1.month.ago by default) and :limit (10 by
    # default).
    #
    # It lists top 10 scored objects in the last month by default.
    #
    # This is a postgresql snippet, you may need to tweak it for other ORMs.
    def self.top_scored(options = {})
      options[:table_name] ||= :users
      options[:since_date] ||= 1.month.ago
      options[:limit]      ||= 10

      alias_id_column = "#{options[:table_name].to_s.singularize}_id"
      if options[:table_name] == :sashes
        sash_id_column = "#{options[:table_name]}.id"
      else
        sash_id_column = "#{options[:table_name]}.sash_id"
      end

      # MeritableModel - Sash -< Scores -< ScorePoints
      sql_query = <<SQL
SELECT
  #{options[:table_name]}.id AS #{alias_id_column},
  SUM(num_points) as sum_points
FROM #{options[:table_name]}
  LEFT JOIN merit_scores ON merit_scores.sash_id = #{sash_id_column}
  LEFT JOIN merit_score_points ON merit_score_points.score_id = merit_scores.id
WHERE merit_score_points.created_at > '#{options[:since_date]}'
GROUP BY #{options[:table_name]}.id, merit_scores.sash_id
ORDER BY sum_points DESC
LIMIT #{options[:limit]}
SQL
      results = ActiveRecord::Base.connection.execute(sql_query)
      results.map do |h|
        h.keep_if { |k, v| (k == alias_id_column) || (k == 'sum_points') }
      end
      results
    end
  end
end

[monroemann]

And on a related issue, and while I have your attention, I'm wondering if you might know how we could create a leaderboard that shows all members who have earned 50 points in the last 30 days (and similarly, those who have not).

In other words:

MEMBERS WHO HAVE MET MINIMUM MONTHLY ACTIVITY REQUIREMENTS Monroe: 56 Mary: 87 Taz: 50 Maro: 54

MEMBERS WHO RISK BEING PUT ON PROBATION Fred: 4 Tim: 49 Zara: 23

Thank you @tute We (the volunteer staff at Break Diving) really appreciate your donations of time and expertise to our cause!

[monroemann]

In looking at the above code, is there an option we could add like this:

options[:min_score]      ||= 50
options[:max_score]     ||= 50

I'm sure it's not that easy, but maybe it is 😃

[tute]

Thinking of the original issue for now: the problem seems to be on the line WHERE merit_score_points.created_at > '#{options[:since_date]}'. I recommend you start debugging that code! Check the input and output after tweaking it (you may need to restart Rails after each change). Related links:

• https://jvns.ca/blog/2019/06/23/a-few-debugging-resources/
• https://tenderlovemaking.com/2016/02/05/i-am-a-puts-debuggerer.html

I don't foresee where the issue might be, but seems to be bad syntax somewhere.

[tute]

Regarding the queries by min and max, you'll want to use the sum_points name (for SUM(num_points) after the SQL GROUP BY). Same thing: read some https://www.w3schools.com/sql/sql_groupby.asp, and then use those debugging resources! :-)

I'm glad this helps your project.

[monroemann]

1. So we need to adjust the SQL code in merit_acores_override? Is that correct?
2. Won’t that negatively affect the rest of the code that works correctly?
3. For the min/max, you also suggest we modify the SQL in merit_scores_override?

Is this something relative beginners like us can figure out on our own? Or is this very high level coding?

[monroemann]

Also, it’s quite possible that OUR code is incorrect, yes? Or does what we wrote look correct?

[tute]

1. So we need to adjust the SQL code in merit_acores_override? Is that correct?
2. Won’t that negatively affect the rest of the code that works correctly?

Right. You can do it in your local computers I development, such that if anything breaks in unexpected ways (and it will 😄) it doesn't affect real world users.

3. For the min/max, you also suggest we modify the SQL in merit_scores_override? Is this something relative beginners like us can figure out on our own? Or is this very high level coding?

Right, it would be adding a line, with some help from a friendly nearby programmer, or a group, if you'd like to learn.

If you want to learn to code I can point you in some directions and paths you can go down to find the answers to your questions.

Best!

[monroemann]

Hi @tute haha, I guess I should feel pretty embarrassed that I gave off the impression that I don't know how to code. I'm no expert, but I've been a ruby on rails developer for now about 2 1/2 years. 😄 But I thank you for your kind assistance, and insodoing, Bhavya and I were able to solve both above problems by not touching the gem code at all, as follows:

CUSTOM LEADERBOARD DEFINITIONS:

  def leaderboard
    @new_users_7_days = User.select("id, first_name, last_name, sash_id")
                            .where("created_at::date >= ?", 7.days.ago)
    @new_users_30_days = User.select("id, first_name, last_name, sash_id")
                            .where("created_at::date >= ?", 30.days.ago)
    @users = User.all
  end

NEW MEMBER LEADERBOARD:

  <h3 class="text-center">NEW MEMBER LEADERBOARDS</h3>
  <br>

  <div class="row">
    <div class="col-md-6">
    <h4>New Members (Joined In Last 7 Days)</h4>

      <% @new_users_7_days.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% total_score = score_points.sum(:num_points) %>
        <div class="col-md-6">
          <strong><%= link_to user.full_name, profile_path(user.profile) %></strong> in <%= link_to user.profile.current_country.name, country_path(user.profile.current_country) %>
        </div>   
        <div class="col-md-6 leaderboard-score">
          <%= total_score %>
        </div>
        <br>
      <% end %>
      <br>
    </div> <!-- close col div -->
    <div class="col-md-6">
    <h4>New Members (Joined In Last 30 Days)</h4>
      <% @new_users_30_days.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% total_score = score_points.sum(:num_points) %>
        <div class="col-md-6">
          <strong><%= link_to user.full_name, profile_path(user.profile) %></strong> in <%= link_to user.profile.current_country.name, country_path(user.profile.current_country) %>
        </div>   
        <div class="col-md-6 leaderboard-score">
          <%= total_score %>
        </div>
        <br>
      <% end %>
      <br>
    </div> <!-- end col div -->
  </div><!-- close row div -->

LOW ACTIVITY LEADERBOARD

  <!-- LOW ACTIVITY MEMBER LEADERBOARDS -->

  <h3 class="text-center">LOW ACTIVITY MEMBERS</h3>
  <br>

  <div class="row">
    <div class="col-md-3">
    <h4>Last 7 Days</h4>
    <p style="color: red">Less than 25 Points in Last 7 Days</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_7_days = score_points.where("created_at > '#{1.week.ago}'").sum(:num_points) %>
        <% if last_7_days < 25 and last_7_days > 0 %>   
            <strong><%= user.full_name %></strong>: <%= last_7_days %>
        <% end %>
      <% end %>
      <br><br>
    </div> <!-- close col div -->
    <div class="col-md-3">
    <h4>Last 30 Days</h4>
    <p style="color: red">Less than 100 Points in Last 30 Days</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_30_days = score_points.where("created_at > '#{1.month.ago}'").sum(:num_points) %>
        <% if last_30_days < 100 and last_30_days > 0 %>
            <strong><%= user.full_name %></strong>: <%= last_30_days %>
        <% end %>
      <% end %>
      <br><br>
    </div><!-- close col div -->
    <div class="col-md-3">
    <h4>Last 3 Months</h4>
    <p style="color: red">Less than 300 Points in Last 90 Days</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_90_days = score_points.where("created_at > '#{3.months.ago}'").sum(:num_points) %>
        <% if last_90_days < 300 and last_90_days > 0 %>
            <strong><%= user.full_name %></strong>: <%= last_90_days %>
        <% end %>
      <% end %>
      <br><br>
    </div><!-- close col div -->
     <div class="col-md-3">
    <h4>Last 6 Months</h4>
    <p style="color: red">Less than 600 Points in Last 180 Days</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_180_days = score_points.where("created_at > '#{6.months.ago}'").sum(:num_points) %>
        <% if last_180_days < 600 and last_180_days > 0 %>
            <strong><%= user.full_name %></strong>: <%= last_180_days %>
        <% end %>
      <% end %>
      <br><br>
    </div><!-- close col div -->
  </div><!-- close row div -->

NO ACTIVITY LEADERBOARD:

<h3 class="text-center">NO ACTIVITY MEMBERS</h3>
  <br>

  <div class="row">
    <div class="col-md-3">
    <h4>Zero Last 7 Days</h4>
    <p style="color: red">Become More Active!</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_7_days = score_points.where("created_at > '#{1.week.ago}'").sum(:num_points) %>
        <% if last_7_days == 0 %>   
            <strong><%= user.full_name %></strong>: <%= last_7_days %>
        <% end %>
      <% end %>
      <br><br>
    </div> <!-- close col div -->
    <div class="col-md-3">
    <h4>Zero Last 30 Days</h4>
    <p style="color: red">Truly, Become More Active!</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_30_days = score_points.where("created_at > '#{1.month.ago}'").sum(:num_points) %>
        <% if last_30_days == 0 %>
            <strong><%= user.full_name %></strong>: <%= last_30_days %>
        <% end %>
      <% end %>
      <br><br>
    </div><!-- close col div -->
    <div class="col-md-3">
    <h4>Zero Last 60 Days</h4>
    <p style="color: red">WARNING: Account In Jeopardy of Deletion!</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_60_days = score_points.where("created_at > '#{2.months.ago}'").sum(:num_points) %>
        <% if last_60_days == 0 %>
            <strong><%= user.full_name %></strong>: <%= last_60_days %>
        <% end %>
      <% end %>
      <br><br>
    </div><!-- close col div -->
     <div class="col-md-3">
    <h4>Zero For Last 90 Days</h4>
    <p style="color: red">DANGER ZONE: Your Account Will Soon be Deleted.</p>
      <% @users.each do |user| %>
        <% score_points = user.score_points(category: 'Total') %>
        <% last_90_days = score_points.where("created_at > '#{3.months.ago}'").sum(:num_points) %>
        <% if last_90_days == 0 %>
            <strong><%= user.full_name %></strong>: <%= last_90_days %>
        <% end %>
      <% end %>
      <br><br>
    </div><!-- close col div -->
  </div><!-- close row div -->

Perhaps this will help some other merit users in the future, who are trying to do something similar. Or, if you like, I could write this up as a cleaner solution to add to the wiki or instructions?

[tute]

Thanks for sharing! I meant to change the override, which is not code in the gem itself, so it's application code as much as everything else you are sharing. Closing this issue for now as it's not a bug. For documentation I think a blog post would be best, as it seems specific to your application. Enjoy.