puts "Having"

User.joins(:payment_requests).select("DISTINCT ON (users.id) users.*").where('payment_requests.created_at >= ?', '2024-01-01').group('users.id').having("SUM(payment_requests.amount) > ?", 0.3728e2)

puts "Includes (Prevents N+1 query problem)"

# This code block makes a single SQL query that makes a left outer joins and combines both associated records 
    # and doesn't contain all record but only the filtered records based on provided inputs

User.includes(:payment_requests).where("payment_requests.order_id IN (?)",  [253, 254]) # This will throw an error cz it won't accomodate sql based query

User.includes(:payment_requests).where("payment_requests.order_id IN (?)",  [253, 254]).references(:payment_requests) # adding references fixes it

# This triggers two seperate query, first to fetch user records and subquery to fetch all associated records
User.includes(:payment_requests)

# https://[Log in to view URL]

# Line number 12 and these below queries are identical

# This makes a single query that makes a Left joins and filters out  
User.includes(:payment_requests).where(payment_requests: {order_id: [253, 254]})

# This makes a single query that makes a Left joins and filters out  
User.eager_load(:payment_requests).where("payment_requests.order_id IN (?)",  [253, 254])

puts "Joins (Causes N+1 query problem and duplicate records in parent table)"

# Causes Duplicate user records based on payment_requests's records
User.joins(:payment_requests).where("payment_requests.order_id IN (?)",  [253, 254])

# To Rectify duplicate records we use distinct

User.joins(:payment_requests).select("DISTINCT ON (users.id) users.*").where("payment_requests.order_id IN (?)",  [253, 254])

# To Rectify N+1 query issue and preload doesn't make any changes over the conditional statement

User.joins(:payment_requests).select("DISTINCT ON (users.id) users.*").where("payment_requests.order_id IN (?)",  [253, 254]).preload(:payment_requests)


# SQL Queries

ActiveRecord::Base.connection.execute("select users.email, tickets.contact_center, count(tickets.id) as tickets_count from users 
inner join tickets on users.id = tickets.user_id group by users.email, tickets.contact_center").to_a

ActiveRecord::Base.connection.execute("select users.email, count(tickets.id) as tickets_count from users inner join tickets on users.id = tickets.user_id group by users.email having count(tickets.id) >= 1 order by tickets_count asc").to_a

ActiveRecord::Base.connection.execute("select users.email, count(payment_requests.id) from users inner join payment_requests on users.id = payment_requests.user_id group by users.email having count(payment_requests.id) > 0").to_a