I have 3 models, Postgresql db -
##user.rb - :full_name
has_many :enquiries
##enquiry.rb - :actual_target_count, :actual_visit_count
##there are different types of enquiry too - business, formal, phone, email etc
belongs_to :user
##sales_projection.rb - :target_month, desired_target_count, desired_visit_count
belongs_to :user
belongs_to :enquiry
Problem Statement - If I have 500+ users who have provided a target for current month (july 2023) with different types of enquiries (60k entries), how can I get a projection of a particular month to show all the users desired vs actual targets.
Let me elaborate this in more detail below -
records in users table (500+ entries)
{id: 1, full_name: "mike"}
{id: 2, full_name: "jason"}
{id: 3, full_name: "remi"}
{id: 4, full_name: "sam"}
records in enquiries table (500+ entries), every enquiry can have multiple entries of users
mike has worked on 5 enquiries, mike has worked on 20 enquiries and so on....
{id: 1, user_id: 1, actual_target_coun:30, actual_visit_count: 40}
{id: 2, user_id: 1, actual_target_coun:10, actual_visit_count: 05}
{id: 3, user_id: 1, actual_target_coun:20, actual_visit_count: 10}
records in sales_projection table (50k+ entries), every sales_projection record every users projections (actual vs desired targets count) for selected month(here its july 2023).
{id: 1, user_id: 1, desired_target_coun:30, desired_visit_count: 40, target_month: "July 2023"}
{id: 2, user_id: 2, desired_target_coun:10, desired_visit_count: 05, target_month: "June 2023"}
{id: 3, user_id: 3, desired_target_coun:20, desired_visit_count: 10, target_month: "July 2023"}
{id: 4, user_id: 4, desired_target_coun:50, desired_visit_count: 15, target_month: "March 2023"}
Based on the above set of records, how can I show a list of all the users and their desired/actual count for a selected month?
this is what i want to be shown:
user | desired target | actual target | desired visit | actual visit | target_month
This is not possible with ActiveRecord (which i have currently) as this will be very time consuming and increase the page load time.
Currently this is what I have:
##pseudo code :)
SalesProjection.where(target_month: "July 2023").order(target_month: :asc).group_by { |m| m.user.id }.map do |key, value|
{
get user full name,
get user desired count using joins
get user actual count using joins
}
end
The above logic takes 1+ mins to load the execute.
What I have tried but need more help:
Your query strategy is slow b/c you instantiate many ActiveRecord objects. The key is to instantiate only the objects and fields of interest, and only do it once. So here is how you might structure your query
class User < ApplicationRecord
has_many :enquiries
has_many :sales_projections # note: missing from OP
def self.report
select =<<-SEL
users.full_name,
sales_projections.target_month,
sales_projections.desired_target_count,
enquiries.actual_target_count,
sales_projections.desired_visit_count,
enquiries.actual_visit_count
SEL
User.
select(select).
joins(:enquiries, :sales_projections).
where("sales_projections.target_month = ?", "July 2023").
where("extract(month from enquiries.created_at)::integer = 7").
where("extract(year from enquiries.created_at)::integer = 2023").
where("enquiries.is_visited = ? and enquiries.is_qualified = ?", true, true).
map do |u|
[u.full_name,
u.target_month,
u.desired_target_count,
u.actual_target_count,
u.desired_visit_count,
u.actual_visit_count]
end
end
end
User.report # => [["Olene", "July 2023", 82, 48, 80, 0],
# ["Kalyn", "July 2023", 10, 2, 26, 0],
# ["Drew", "July 2023", 7, 36, 53, 26],
# ["Terry", "July 2023", 99, 26, 44, 37],
# ["Olene", "July 2023", 82, 36, 80, 2],
# ["Sherlene", "July 2023", 71, 19, 79, 43],
# ["Delmer", "July 2023", 5, 48, 43, 20],
# ["Connie", "July 2023", 86, 1, 42, 34],
# ["Candis", "July 2023", 86, 17, 13, 9],
# ["Candis", "July 2023", 86, 11, 13, 21], ...etc.
This should be much much faster than your query, I used 50 users, 5000 sales_projections, and 1000 enquiries and the query took 50mS. I didn't use any indexes... you should do that to get even better performance.
Rather than querying the enquiries.created_at as between two dates, I suggest performance will be improved by querying the month and year values directly.
I'll be curious to know how fast this performs with your data set.