1

I'm using version 2.2.15 at the moment and I'm following the relation subqueries recipe. I've outlined my models and their relationships towards the bottom.

Currently, I'm fetching an event as well as it's post count using this query:

const events = await Event.query().select([
  'events.*',
  Event.relatedQuery('posts')
    .count()
    .as('numberOfPosts'),
  ]);

Which works fine - but how can I include the count of each of the event's post's users, that is, the total amount of user's going to an event. I've tried using a refs so far without luck.

Models and their relationships:

Event:

posts: {
  relation: Model.HasManyRelation,
  modelClass: Post,
  join: {
    from: 'events.id',
    to: 'posts.eventId',
  },
},

Post:

event: {
  relation: Model.BelongsToOneRelation,
  modelClass: Event,
  join: {
    from: 'posts.eventId',
    to: 'events.id',
  },
},
users: {
  relation: Model.ManyToManyRelation,
  modelClass: User,
  join: {
    from: 'posts.id',
    through: {
      // users_posts is the join table.
      from: 'users_posts.postId',
      to: 'users_posts.userId',
    },
    to: 'users.id',
  },
},

User:

posts: {
  relation: Model.ManyToManyRelation,
  modelClass: Post,
  join: {
    from: 'users.id',
    through: {
      // users_posts is the join table.
      from: 'users_posts.userId',
      to: 'users_posts.postId',
    },
    to: 'posts.id',
  },
},
mycellius
  • 568
  • 1
  • 5
  • 28

1 Answers1

1

You should be able to do it like so, i haven't tried it in code so you might need to tweek a few things, what i'd do usually if i have a count that needs to be tracked is keep a field on the posts that increments users in the application logic and value is retrieved much faster, in your case you can just count the users_posts where the postId match and get your users count and save it in that field

const events = await Event.query().select([
   'events.*',
   Event.relatedQuery('posts')
   .count()
   .as('numberOfPosts'),

   Event.relatedQuery('posts')
   .join('users_posts', 'posts.id', '=', 'users_posts.postId')
   .join('users', 'users.id', '=', 'users_posts.userId')
   .countDistinct('users.id')
   .as('numberOfPostUsers'),
]);
M-Raw
  • 779
  • 4
  • 10
  • Thank you - I'll be marking this as the accepted answer. With this query, if a user belongs to multiple posts it increases the number of users, how can I get the unique count? e.g Post 1 has users A + B. Post 2 has users A + C. The total count is 4 instead of whereas I'd like to to be 3 (A + B + C). Cheers – mycellius Jan 31 '22 at 07:57
  • try using `.countDistinct('users.id')` instead of `count()` if it works for you i'll update the answer – M-Raw Jan 31 '22 at 08:43