CODEWITHSUNDEEP
sqlpostgresql
ChrisInCambo
ChrisInCambo

Reputation: 8795

Getting a count of a child relation and a count of a child of the child

Sorry for the naff title, but I'm not really sure how to explain this, I'm one of the new generation whose SQL skills have degraded thanks to the active record patterns!

Basically I have three tables in PostgreSQL

Client (One Client has many maps) - id

Maps (Map has one client and many layers) - id - client_id

Layer (Layer has one map) - id - map_id

I would like to write an SQL query that returns Cliend.id along with a count of how many maps that client has and the total number of layers the client has across all maps.

Is this possible with a single query? Speed isn't of concern as this is just for analytical purposes so will be run infrequently.

Upvotes: 1

Views: 132

Answers (2)

Dario Barrionuevo
Dario Barrionuevo

Reputation: 3277

I'd do it like this, a single SQL Query inside a method in the Client model:

def self.statistics
    Client.select("
            clients.id AS client_id, 
            COUNT(DISTINCT(maps.id)) AS total_maps, 
            COUNT(layers.id) AS total_layers")
    .joins(maps: :layers)
    .group("clients.id")
end

In order for this to work, you need the associations declared between your models (Client has_many :maps, Map has_many :layers)

You can go depper in the ActiveRecord's query interface here

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324851

I'd use a pair of subqueries for this. Something like:

SELECT
    id,
    (
         SELECT count(map.id)
         FROM map 
         WHERE map.client_id = client.id
    ) AS n_maps,
    (
         SELECT count(layer.id)
         FROM map INNER JOIN layer ON (layer.map_id = map.id)
         WHERE map.client_id = client.id
    ) AS n_layers
FROM client;

Upvotes: 2

Related Questions