Graph: Get orders with a transaction of kind SALE and ProcessedAt > DATE

MozzoERP
Shopify Partner
84 4 20

Using GraphQL, I'm loooking to export orders that have a Sale transaction since the last time we polled....something like this in T-SQL:

 

Select o.Id, t.processedAt
FROM Orders o INNER JOIN Transactions t ON t.OrderId = o.Id
WHERE t.Kind = 'SALE' and t.Status = 'SUCCESS' and t.ProcessedAt >= '2019-08-30 12:35:00'

 

I've been googling graphql questions for an hour, not able to find out if this is possible (i.e. querying a top level object by its related node data)...I've very new to GraphQL, as this question probably shows 🙂

Chad Richardson
Mozzo Software - Modular Software that grows with you from solopreneur to a 200 person mega team. Why keep outgrowing your Shopify Apps? Start with us, and just use the modules you need, then add more as you grow. http://MozzoERP.com
Replies 2 (2)

Alex
Shopify Staff
1561 81 341

You can't filter by orders that contain a specific transaction type, you can filter them by processed_at timestamps and view the transactions within each that meet that initial qualifier, paginating through the orders when needed:

 

query {
  orders(first:25, query:"processed_at:>2019-08-10T12:35:00") {
    pageInfo {
      hasNextPage
    }
    edges {
      node {
       id
       processedAt
        transactions(first: 25) {
	  id
          kind
        }
      }
      cursor
    }
  }
}

Cheers.

Alex | Shopify 
 - Was my reply helpful? Click Like to let me know! 
 - Was your question answered? Mark it as an Accepted Solution
 - To learn more visit the Shopify Help Center or the Shopify Blog

MozzoERP
Shopify Partner
84 4 20

Thanks @Alex .

 

I found a better way for me, query:

 

orders (query"fulfillment_status:unfulfilled AND financial_status:paid")

 

 

However, the processedAt at the order level is not always a good additional parameter to retrieve orders because it more or less represents the createdAt date time. And if you have check or bank deposit payment methods, the actual paid date could be days later. The updatedAt could be potentially used, but it gets updated for any order change.

 

Ideally, to periodically get newly paid orders, we'd store the last time we checked and then do something like:

 

orders ("fulfillment_status:unfulfilled AND financial_status:paid AND paidAt:>[datetime last checked]")

 

This would be the most efficient in terms of api calls. In lieu of having the ability to filter by a paid date time, you have to limit your query just to 

orders (query"fulfillment_status:unfulfilled AND financial_status:paid")

which causes you to re-pull back the same orders again and again.

 

This is all said to:

a.) see if you can think of an alternative that I'm not thinking about and

b.) if not, for the vote to have the ability to query orders by their transaction attributes (i.e. kind:SALE AND status:SUCCESS or the like, which would also have the benefit of reducing your back end api load (by eliminating the multiple times of pulling down the same order). 

Chad Richardson
Mozzo Software - Modular Software that grows with you from solopreneur to a 200 person mega team. Why keep outgrowing your Shopify Apps? Start with us, and just use the modules you need, then add more as you grow. http://MozzoERP.com