CODEWITHSUNDEEP
mysqlnode.jsknex.js
Muljayan
Muljayan

Reputation: 3886

Counting inside select knex js

I have two sets of tables which look as follows

User table

|  id| name |
|----|------|
|  1 |  x   |
|  2 |  y   |
|  3 |  z   |

Visits table

| event_id | user_id |
|----------|---------|
|     1    |  1      |
|     2    |  2      |
|     3    |  3      |
|     4    |  1      |
|     5    |  1      |
|     1    |  2      |

How do i get the number of visits by each user along with the name of the user. I know raw SQL can be used with knex and I can easily do this with a SQL query. I curious to know if this is possible using only knex js.

await knex('visits').count().groupBy('user_id');

The above query would give me the count of visits by each user. How do i get the name of the user in the same query.

I am looking for an output which looks like below

| user_id  | name | count |
|----------|------|-------|
|     1    |  x   |   3   |
|     2    |  y   |   2   |
|     3    |  z   |   1   |

Upvotes: 4

Views: 5939

Answers (1)

Ihor Sakailiuk
Ihor Sakailiuk

Reputation: 6068

Try:

knex('visits')
  .select(['visits.user_id AS user_id', 'user.name'])
  .count('visits.user_id AS count')
  .leftJoin('user', 'user.id', 'visits.user_id')
  .groupBy(['visits.user_id', 'user.name']);

Upvotes: 6

Related Questions