Count relations

Sometimes you may want to return a count of relations (for example, a user’s post count). To accomplish this, you can do as the example below.

Having the query below in the file select-users-with-post-count.sql:

SELECT
  u.name,
  count(p.id) as postCount
FROM users u
LEFT JOIN posts p on p.fk_user = u.id
GROUP BY u.id
ORDER BY count(p.id) desc

TypeSQL will gererate the function selectUsersWithPostCount:

const usersWithPostCount = await selectUsersWithPostCount(conn);
console.table(usersWithPostCount);

Result: