Objection.js: Getting total count of page

[AndrejGajdos]

How can I get total count of page method or QueryBuilder object?

const PodcastListener = require('../models/podcastListener');

const podcast = await PodcastListener.query()
    .findOne('name', podcastName)
    .eager('episodesRel')
    .modifyEager('episodesRel', (fstBuilder) => {
      const episodes = fstBuilder
        .page(pageNumber, 3);
      console.log(episodes);
    });

// QueryBuilder {_knex: ƒ, _operations: Array(4), _context: QueryBuilderContext, _modelClass: ƒ, _resultModelClass: null, …}

podcastListener.js

const { Model, snakeCaseMappers } = require('objection');

class Podcast extends Model {
  static get tableName() {
    return 'podcasts_listeners';
  }

  static get columnNameMappers() {
    return snakeCaseMappers();
  }

  static get jsonSchema() {
    return {
      type: 'object',

      properties: {
        id: { type: 'integer' },
        name: { type: 'string', minLength: 1, maxLength: 100 },
      },
    };
  }

  static get relationMappings() {
    return {
      usersRel: {
        relation: Model.BelongsToOneRelation,
        modelClass: `${__dirname}/user`,
        join: {
          from: 'podcasts_listeners.user_id',
          to: 'users.id',
        },
      },

      episodesRel: {
        relation: Model.HasManyRelation,
        modelClass: `${__dirname}/episode`,
        join: {
          from: 'podcasts_listeners.id',
          to: 'episodes.podcasts_listeners_id',
        },
      },
    };
  }
}

module.exports = Podcast;

episode.js

const { Model, snakeCaseMappers } = require('objection');

class Episode extends Model {
  static get tableName() {
    return 'episodes';
  }

  static get columnNameMappers() {
    return snakeCaseMappers();
  }

  static get jsonSchema() {
    return {
      type: 'object',

      properties: {
        podcasts_listeners_id: { type: 'integer' },
        id: { type: 'integer' },
        episode_name: { type: 'string', minLength: 1, maxLength: 100 }, },
        description: { type: 'string' },
      },
    };
  }

  static get relationMappings() {
    return {
      podcastRel: {
        relation: Model.BelongsToOneRelation,
        modelClass: `${__dirname}/podcastListener`,
        join: {
          from: 'episodes.podcasts_listeners_id',
          to: 'podcasts_listeners.id',
        },
      },
    };
  }
}

module.exports = Episode;

[koskimas]

Unfortunately you cannot use page like that in a modifyEager. You want to get the number of episodes for each PodcastListener right? This should work:

const podcast = await PodcastListener.query()
    .findOne('name', podcastName)
    .select([
      'PodcastListener.*', 
      PodcastListener.relatedQuery('episodeRel').count().as('numEpisodes'))
    ])

[AndrejGajdos]

@koskimas Thanks for the response. I want to paginate Episodes for a Podcast.

I need to get a total count of episodes and wonder if I need a separate query after page function to get total count?

If I try:

const podcast = await PodcastListener.query()
    .findOne('name', podcastName)
    .select([
      'PodcastListener.*', 
      PodcastListener.relatedQuery('episodeRel').count().as('numEpisodes'))
    ])

I get Error: PodcastListener.relatedQuery is not a function

You can see PodcastListener model definition in my question.

[elhigu]

@AndrejGajdos In your OP there is no PodcastListener class name seems to be Podcast are you requiring it with different name? Also which objection version are you using?

[AndrejGajdos]

@elhigu I updated my question, PodcastListener is:

const PodcastListener = require('../models/podcastListener');

I use version 0.9.4

If I try $relatedQuery method, I get the same error PodcastListener.$relatedQuery is not a function

[elhigu]

I think that Model.relatedQuery was added in v1.0.0-rc.11

[AndrejGajdos]

@elhigu Right, I checked the changelog. Does it mean I need to upgrade to 1.0.0 if I want to get total count with page function?

[koskimas]

@AndrejGajdos No, you can use normal SQL subqueries, separate queries or any other method. There are multiple ways to get the total number of related rows. You just cannot use modifyEager for multiple reasons (all explained in the docs). You can get total count with page function, just not inside modifyEager. Here's an equivalent of the relatedQuery query:

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

const podcast = await PodcastListener.query()
    .findOne('name', podcastName)
    .select([
      'podcast_listeners.*', 
      Episode.query().where('podcast_listeners.id', ref('episodes.podcasts_listeners_id')).count().as('episodeCount')
    ]);

Here's another way to do it:

const podcast = await PodcastListener.query().findOne('name', podcastName);
podcast.episodeCount = (await pocast.$relatedQuery('episodeRel').count()).count;

[AndrejGajdos]

If I try:

const podcast = await PodcastListener.query()
    .findOne('name', podcastName)
    .select([
      'podcast_listeners.*',
      Episode.query().where('podcast_listeners.id', ref('episodes.podcasts_listeners_id')).count().as('episodeCount'),
    ]).debug();

I get error: error: missing FROM-clause entry for table "podcast_listeners"

Sql from debug, is similar to example. Do you know what can be the issue?

{method: "select", options: {…}, timeout: false, cancelOnTimeout: false, bindings: Array(1), …}
bindings
:
["Pod Save America"]
cancelOnTimeout
:
false
method
:
"select"
options
:
__proto__
:
Object
sql
:
"select "podcast_listeners".*, (select count(*) from "episodes" where "podcast_listeners"."id" = "episodes"."podcasts_listeners_id") as "episodeCount" from "podcasts_listeners" where "name" = $1"
timeout
:
false
__knexQueryUid
:
"22ce99ac-7f80-4196-ae82-525ba637d82b"
toNative
:
ƒ value()
__proto__
:
Object

[koskimas]

You have both podcasts_listeners and podcast_listeners as a table name. Use the correct table name. You can edit my example, I didn't run it 😉

[AndrejGajdos]

Thanks, it works.