Get count of items between T0 and T1

prisma

#1

I’m working on a small app, Point-of-Sale type, and I need the ability to create monthly sales reports, basically, a list with all items sold during a month and their quantity.

In my datamodel I have a type Order that has an array of type OrderItem.

type OrderItem {
id: ID! @unique
title: String
price: Int!
deletedAt: DateTime
quantity: Int! @default(value: 1)
user: User
}

type Order {
id: ID! @unique
items: [OrderItem!]!
total: Int!
user: User!
createdAt: DateTime!
updatedAt: DateTime!
}

I’m able to get all monthly sales by querying orders:

query {
  orders(
    where: {
      AND: [
        { createdAt_gte: "2019-01-01" }
        { createdAt_lte: "2019-01-31" }
      ]
    }
    orderBy: createdAt_ASC
  ) {
    id
    items {
      id
      title
    }
  }
}

But I’m not sure how to get a list of OrderItems and their total quantities. What would be the right way to do this using prisma/graphql?


#2

Hi,

You can use aggregations for this:

That way you can get a total count for these things


#3

Thanks, I managed to do what I wanted by using aggregations, like you suggested.

I ended up doing two queries: first, I query all items, and return their title and id. Then, in the second query I use as variables the item title, a start and end date which allowed me to return the orderItemsConnection.aggregate.count for each item, exactly as I wanted.


#4

Hi! @georgegrigorita

Could you please share your query?

Thanks
Hemant


#5

Sure, here it is:

<Query query={ITEMS} variables={{ store: this.props.store_id }}>
        {({ data, error, loading }) => {
          if (loading) return <p>Loading...</p>;
          if (error) return <p>Error: {error.message}</p>;
          return (
            <>
              {data.items.map(item => (
                <div key={item.id}>
                      {item.title}
                      <Query
                        query={REPORTS}
                        variables={{
                          store: this.props.store_id,
                          title: item.title,
                          from: this.props.from,
                          to: this.props.to
                        }}
                      >
                        {({ data, error, loading }) => {
                          if (loading) return <p>Loading...</p>;
                          if (error) return <p>Error: {error.message}</p>;
                          return (
                            <Block>
                              Number: {data.orderItemsConnection.aggregate.count}
                            </Block>
                          );
                        }}
                      </Query>
                </div>
              ))}
            </>
          );
        }}
      </Query>

And the graphql looks like this:

const ITEMS = gql`
  query ITEMS($store_id: ID) {
    items(where: { store_some: { id: $store_id } }, orderBy: tag_ASC) {
      id
      title
    }
  }
`;

const REPORTS = gql`
  query REPORTS($store_id: ID, $from: DateTime, $to: DateTime, $title: String) {
    orderItemsConnection(
      where: {
        AND: [
          { store: { id: $store_id } }
          { createdAt_gte: $from }
          { createdAt_lte: $to }
          { title: $title }
        ]
      }
    ) {
      aggregate {
        count
      }
    }
  }
`;

#6

Thanks @georgegrigorita


#7

This topic was automatically closed 45 days after the last reply. New replies are no longer allowed.