Is there a way to get aggregated information via an existing endpoint?

[idle dew]

Is there any endpoint already created to query for aggregated data?

For example:

• Getting total amount made for orders between a period?
• Getting the count of registered customers between X dates?
• etc

[viral parcel]

There is not, but you can easily create a new api endpoint to serve up all the data you need

[idle dew]

I am currently surfing online to find a resource to understand what the best approach to implement this. Do you have any reference? Basically, how should i implement the logic to expose a /aggregate endpoint, where the consumer can request the base resource (from), any groupings (group by), filters (where) and aggregations (select sum, count, min, etc). It kind of acts as a DB proxy

[viral parcel]

https://docs.medusajs.com/v2/first-customizations

[viral parcel]

it's a lot to unpack, you'd need to call each of the corresponding medusa services to pull the related info, or just implement direct db calls. your choice how you choose to handle

personally, i'd avoid any direct db calls open to the public

[idle dew]

I'm using v1, but my question is not targeted as Medusa per se, but the general concept of how to implement an API endpoint, to allow a consumer to specify things like the aggregator function, the filters, grouping and how to parse this dynamically on the handler, to then call the DB

[viral parcel]

get the info from each of the modules you need, ie order module and customer modules

[idle dew]

i don't know if i explained myself correctly. But basically, i want to know how would i implement an endpoint to answer questions like: 'how many orders were sold in the last month', 'how many newcustomers do we have this week', 'what is the average product value for each category', etc

[viral parcel]

create an api end point, specify what to do for each type of Option - ie get/post/etc. handle the logic corresponding to respond json with the data

[idle dew]

not directly linked to Medusa, as the concept is more general than that. Like, should this /aggregation endpoint should be a POST or a GET. And, what is the approach to dynamically parse the body / queryParams into things the DB will understand to issue the query

[viral parcel]

depends on your db

there's packages for all dbs to make it easy

[idle dew]

I know, that is the basics of creating endpoints with Medusa, i already know how to do this

[viral parcel]

that's the basics of creating any api endpoint, not medusa specific

[idle dew]

that's correct, but my question is more specific than how to create any endpoint. My question is how to implement an endpoint to basically make dynamic aggregation queries based on the consumer request

[viral parcel]

its one and the same dude. you write the code for each logic piece

ie if params.seach_id or whatever you have them pass in the body you then map the data to corresponding fields to generate a sql query

all depends on what infra you're using, what db you're using, do you want authentication logic on the api endpoint, etc etc

[idle dew]

you are not understanding me, maybe i didn't make myself clear. I appreciate your time though

[viral parcel]

you can create an edge function directly in the db with posgres and have it handle all the logic on that side

[idle dew]

but that is not what i am asking

[viral parcel]

it is what you're asking. i dont think you're understanding how api's work

[idle dew]

maybe an example can help me explain myself better:

On an express server i used for a business, i hade a /query/product endpoint, where the consumer would send a POST request (i used class-validator to enforce the shape of the payload), then i had a mapperFunction, to map that payload to the input TypeORM (the ORM i used to interact with the MySQL db used) needs and then calling the productService.query method with that input, which called the repository

well, this wasn't scalable and i never was sure if it was the approach for these kind of API requests, to get not entities, but rather aggregated dynamic information

my question is if anyone can point me to a resource that explains what the best architecture pattern and implementation is, to handle the dynamism of the problem

[viral parcel]

you want an edge function in db if you're doing aggregate data. You then make a simple api that just passes info to the edge functions and returns it's response.

api will map out request to how you structured in the edge function in db

still the same exact logic i've been describing this entire time.

idk if postgres is your db, but here's supabse info on edge functions (which is a postgres based db)

https://supabase.com/docs/guides/functions

[idle dew]

yeah i have postgres

i was just reading a doc of that based on your comment

[viral parcel]

you could achieve it locally in code, but id recommend as an edge function for performance in production. off loads the collecting and merging all data outside of your app

[astral basalt]

Hi, if I understood correctly you were asking about the accepted, convention to achieve flexibility and ease to maintain. I would suggest going as REST-full as possible, even though medusa chooses some dynamic quiring approach. As long as you are not planning to create a framework by yourself, you probably should go with most general approaches Rest, graphql, maybe postgREST

[idle dew]

i'm asking specifically about how to implement an endpoint like /stats/ to perform dynamic aggregated queries. So, apart from endpoints to interact with entitites, like /admin/orders, which gets a list of orders, i would like to be able to hit this endpoint and get info like: 'how many orders where created between period', 'how many new customers', 'avg order value' and so on

[astral basalt]

you can do it by getting the code from core and overrding the behavior but you shouldn't. I think what you need is to add endpoint like /admin/orders/count?startDate=x&endDate=y and to add this repo:

async countOrdersBetween(x: any, y: any): Promise<number> {
    const postRepo = this.activeManager_.getRepository(Order);
    
    const count = await orderRepo.count({
        where: {
            // Assuming x and y are used to filter based on some column, e.g., `createdAt`
            createdAt: Between(x, y)
        }
    });

    return count;
}

[idle dew]

i get it, but then if i want to answer the question: "Money made from orders between periods" and "Avg items by orders for X country", "Avg orders per customer" and so on, i would have to keep making endpoints. That's why i want to understand what is the best solution to have a single /aggregations or /stats endpoint, as i see many APIs have. But want to understand how i would need to implement it to parse the request params / body, translate that to what TypeORM understands, execute the query and return the results, for potentially many differen questions (requests from the consumer)

[astral basalt]

I think this kind of question is not related to medusa more to which approach you prefer, from my experience endpoints should be simple as possible and do 1 thing at a time, because I saw how much mess and complexity the dynamic approach can do, but other then that its up to you, you maybe can laverage graph ql for this but Im not sure about details

[astral basalt]

I would do endpoints per widget or graph instead of 1 endpoint for entire analytics

[idle dew]

totally, its more of a broader decision on how to implement such an endpoint in an API

and how would you go about the implementation, to parse the request params or body, to transalte them to what typeORM understands? that for me is the greater doubt, rather than the endpoint/s itself