Prisma•7d ago

Help Needed with sorting/filtering data over Timeframe Based on Aggregated results

I'm working on a feature that involves querying daily event data and aggregating metrics over a specified timeframe (e.g., last 30 days or 90 days) for each event. Based on these aggregates, I need to filter and sort the events according to certain thresholds, such as total revenue or average price within the selected timeframe. Here’s a more detailed breakdown of my issue: Data Structure: Events table: Stores information about each event, like its name and category. EventData table: Contains daily metrics for each event, including: totalRevenue: Total revenue generated by the event on a given day. totalTicketsSold: Total tickets sold on that day. avgTicketPrice: Average ticket price for that day. eventDate: The date of the event metrics. The Events table has a relation to EventData, where EventData holds the daily details. Query for all events within a given timeframe (e.g., last 30 or 90 days). For each event, I need to aggregate the totalRevenue, totalTicketsSold, and avgTicketPriceover the timeframe. Apply filters on these aggregated values. For example: Only retrieve events where the sum of totalRevenue in the timeframe is between a specified minimum and maximum. Similarly, apply filters for totalTicketsSold and avgTicketPrice (where avgticketprice is average price over that timeframe) based on specified thresholds. Finally, sort the results based on one of these aggregate fields (e.g., sort by totalRevenuein descending order). Challanges im facing Filtering on Aggregated Data: I need to filter events based on the sum or average of fields in EventData over the specified timeframe. For example, filtering events with a total revenue between 1000 and 5000 within the last 30 days. Sorting: I want to sort the filtered results based on these aggregated values (e.g., total revenue or average ticket price).

3 Replies

SvenOP•7d ago

@ me please ❤️

RaphaelEtim•6d ago

Hi @Sven The best approach would be to use TypedSQL as Prisma can't handle all the requirements you've listed. You can create a new SQL file in the prisma/sql directory, for example, getEventMetrics.sql:

SELECT 
  e.id, 
  e.name, 
  e.category,
  SUM(ed.totalRevenue) as totalRevenue,
  SUM(ed.totalTicketsSold) as totalTicketsSold,
  AVG(ed.avgTicketPrice) as avgTicketPrice
FROM 
  "Events" e
JOIN 
  "EventData" ed ON e.id = ed."eventId"
WHERE 
  ed.eventDate >= CURRENT_DATE - INTERVAL :daysAgo DAY
GROUP BY 
  e.id, e.name, e.category
HAVING 
  SUM(ed.totalRevenue) BETWEEN :minRevenue AND :maxRevenue
ORDER BY 
  SUM(ed.totalRevenue) DESC

SELECT 
  e.id, 
  e.name, 
  e.category,
  SUM(ed.totalRevenue) as totalRevenue,
  SUM(ed.totalTicketsSold) as totalTicketsSold,
  AVG(ed.avgTicketPrice) as avgTicketPrice
FROM 
  "Events" e
JOIN 
  "EventData" ed ON e.id = ed."eventId"
WHERE 
  ed.eventDate >= CURRENT_DATE - INTERVAL :daysAgo DAY
GROUP BY 
  e.id, e.name, e.category
HAVING 
  SUM(ed.totalRevenue) BETWEEN :minRevenue AND :maxRevenue
ORDER BY 
  SUM(ed.totalRevenue) DESC

Generate Prisma Client with the SQL flag:

prisma generate --sql

prisma generate --sql

you can use the generated TypedSQL function in your TypeScript code:

import { PrismaClient } from '@prisma/client'
import { getEventMetrics } from '@prisma/client/sql'

const prisma = new PrismaClient()

const result = await prisma.$queryRawTyped(getEventMetrics({
  daysAgo: 30,
  minRevenue: 1000,
  maxRevenue: 5000
}))

console.log(result)

import { PrismaClient } from '@prisma/client'
import { getEventMetrics } from '@prisma/client/sql'

const prisma = new PrismaClient()

const result = await prisma.$queryRawTyped(getEventMetrics({
  daysAgo: 30,
  minRevenue: 1000,
  maxRevenue: 5000
}))

console.log(result)

SvenOP•6d ago

Thanks alot for your answer

Gaming

Programming

Help Needed with sorting/filtering data over Timeframe Based on Aggregated results