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 defferent types. Currently, I’m trying to combine multiple COUNT(*) queries within one SQL query. See the pseudocode below.
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?
1
1 Answer
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)
The number of counts is not a problem. The size of table might be a problem. Or rather size of table per my_type_id (if indexed, it should be).
Sep 19 at 7:45