Drizzle Team•2y ago

How to count joined table?

I'm trying to count rows returned by join for a query, basically the same as this: https://dba.stackexchange.com/questions/110850/count-rows-with-inner-joined-tables How to convert this to Drizzle? Any documentation I've missed regarding this kind of things?

SELECT t1.team_name,
       IFNULL(t2.num_players, 0) AS strength,
       t1.team_timestamp
FROM team t1
LEFT OUTER JOIN 
    (SELECT team_id, COUNT(team_id) AS num_players 
     FROM player 
     GROUP BY team_id
    ) t2
ON t1.team_id = t2.team_id
-- GROUP BY t1.team_id, t1.team_name, t1.season_id  -- **NOTE** - see discussion below
ORDER BY strength DESC, team_name ASC;

SELECT t1.team_name,
       IFNULL(t2.num_players, 0) AS strength,
       t1.team_timestamp
FROM team t1
LEFT OUTER JOIN 
    (SELECT team_id, COUNT(team_id) AS num_players 
     FROM player 
     GROUP BY team_id
    ) t2
ON t1.team_id = t2.team_id
-- GROUP BY t1.team_id, t1.team_name, t1.season_id  -- **NOTE** - see discussion below
ORDER BY strength DESC, team_name ASC;

Database Administrators Stack Exchange

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; +-----------+---------+----------------------+----------------------+-----------------+------------------------------+--------------...

1 Reply

Jeyprox•2y ago

you should be able to do something along the lines of:

await db.select({
  teamName: team.teamname,
  time: team.team_timestamp
  strength: sql<number>`count(${player.team_id})`
}).from(team)
.leftJoin(player, eq(team.team_id, player.team_id))
.groupBy(team.team_id)
.orderBy(desc(sql`count(${player.team_id})`));

await db.select({
  teamName: team.teamname,
  time: team.team_timestamp
  strength: sql<number>`count(${player.team_id})`
}).from(team)
.leftJoin(player, eq(team.team_id, player.team_id))
.groupBy(team.team_id)
.orderBy(desc(sql`count(${player.team_id})`));

this is not exactly what you're trying to do but should give you an idea of how you can achieve something like this. otherwise you can also just leftJoin the other tables and write a function to aggregate the results yourself

Gaming

Programming

How to count joined table?