[SOLVED] How to do counts in batch for graphql data loader?

How to do counts in batch for graphql data loader?

I'm implementing a Graphql resolver for complex datatype. I try to use a data loader to avoid N+1 problem. I have a datatype with a counter. Therefore, in one graphql query I need to perform user counting multiple times for different types. Currently, I'm trying to combine multiple COUNT(*) queries within one SQL query.

See the pseudo code:

# My type
type MyType {
  users_count: number
}

# The query returns an array of "MyType" 
getMyType(id: ID!): [MyType!]!

# Graphql query
getMyType(id: ID!) {
    id
    users_count
    created_at
}

# This is how I'm trying to do multiple counts
# 
SELECT 
    (SELECT COUNT(*) FROM table_with_users WHERE my_type_id = 1),
    (SELECT COUNT(*) FROM table_with_users WHERE my_type_id = 2)

The problem is I may have dozens of counts in one query. Is this going to be efficient for DB? What is the most efficient way of counting in this case?

Solution

A far more efficient way to do those multiple counts is to do it using a single group by query.

SELECT
  my_type_id
, COUNT(*) as count_of
FROM table_with_users 
WHERE my_type_id IN(1,2,3) -- adjust this detail as needed
GROUP BY
  my_type_id

This will produce 1 row per my_type_id.

If you absolutely must have just one row with many columns, then you can still achieve that with a single query:

SELECT
       COUNT(CASE WHEN my_type_id = 1 THEN 1 END) AS type_1
     , COUNT(CASE WHEN my_type_id = 2 THEN 1 END) AS type_2
     , COUNT(CASE WHEN my_type_id = 3 THEN 1 END) AS type_3
FROM table_with_users
WHERE my_type_id IN (1,2,3)