Objection.js: How to count related data without return the rows(eager loading)?

[davidchalifoux]

A generic social media use case. Let's say that you have Tweets with Likers(Users). When you're scrolling through a feed, you don't need to load the information of every User who has liked the tweet, only the number of Likers it has.

This may be optimizing a bit early, however I'm still curious how this can be done.

Thanks!

[elhigu]

I don't think thats possible with eager.

Probably it will be fast enough to just do related query tweet.$relatedQuery('likers').count() or something like that for each tweet in screen.

Or if you want to do it at the same time when querying tweets, by joining likersCount to every tweet or with subquery in Tweet.query().select('*', subQuery.as('likersCount'))...

[koskimas]

You can leverage SQL and get the counts in one single query, but it's quite tedious. Something like this:

      return Tweet.query()
        .innerJoinRelation('likers')
        .select('Tweet.id', 'Tweet.text')
        .count('Tweet.id as likerCount')
        .groupBy('Tweet.id', 'Tweet.text')
        .then(tweets => {
          console.log(tweets);
        });

There is no helper for getting the count for an eager query, because its performance would be horrible in a generic case (one query for each parent model) and creating a generic version of the example above isn't feasible.

[koskimas]

I just added a feature that allows you to do this starting from the next version:

const tweets = await Tweet.query()
  .select([
    'Tweet.*',
    Tweet.relatedQuery('likers').count().as('likeCount')
    Tweet.relatedQuery('retweets').count().as('retweetCount')
  ])
  .whereIn('id', tweetIds);

[davidchalifoux]

Thanks so much @koskimas !

[davidchalifoux]

@koskimas One more curveball. On top of returning the count of likers, it's pretty important to be able to check if a currently auth'ed user has already liked a post.

My assumption is that this should be possible through a relatedQuery(), am I right?

[elhigu]

@koskimas Thats nice! I was thinking that exact same syntax, when I wrote my comment :) does that Tweet.relatedQuery() work only in subqueries? I don't understand how that would make sense being ran as a standalone query.

[koskimas]

@elhigu Yep, only works as a subquery.

@davidchalifoux This should get you youLiked: 1 or youLiked: 0.

const tweets = await Tweet.query()
  .select([
    'Tweet.*',
    Tweet.relatedQuery('likers')
      .where('id', loggedInUserId)
      .count()
      .as('youLiked')
  ])
  .whereIn('id', tweetIds);

I don't know if there is an easy way to convert that into boolean in SQL, but you can do it easily in code. If you use that query in multiple places, you could do something like this:

class Tweet extends Model {
  $parseDatabaseJson(json) {
    json = super.$parseDatabaseJson(json);
    if ('youLiked' in json) {
      json.youLiked = !!json.youLiked;
    }
    return json;
  }
}

[koskimas]

Haven't tested, but this could work directly:

const tweets = await Tweet.query()
  .select([
    'Tweet.*',
    Tweet.relatedQuery('likers')
      .select(true)
      .where('id', loggedInUserId)
      .as('youLiked')
  ])
  .whereIn('id', tweetIds);

or at least this:

const { raw } = require('objection');

const tweets = await Tweet.query()
  .select([
    'Tweet.*',
    Tweet.relatedQuery('likers')
      .select(raw('true'))
      .where('id', loggedInUserId)
      .as('youLiked')
  ])
  .whereIn('id', tweetIds);

[davidchalifoux]

I've come to a conclusion that the most efficient way to count likers is to simply denormalize it and have a likeCount field on each tweet, with a database trigger to automatically handle increments/decrements. This way you don't have to count it each time through SQL.

I'm looking into your suggestion about how to check if a currently auth'ed user has already liked a tweet now.

Thanks for all the help @koskimas !

[davidchalifoux]

Here's my current method for checking if the current user liked a tweet or not:

Tweet
     .query()
     .leftJoinRelation('likers', currentUserId, '=', 'likers.id')
     .select('tweets.*', knex.raw(`IF('${currentUserId}'=likers.id,'True','False') AS isLikedByYou`))

[gu-fan]

great work!
further question. what if each tweets have comment, and the comment have it's likers.
can I eager the tweet's comment's liker's count?
( Of course, I will have a 'liked_count' column in production, but still have to handle the 'liked_by_me')