How to get the correct count for a Lucid Model's Paginate when joining with additional tables

Question

I have 2 Lucid models: Ad and Campaign, which are associated using a Many:Many relationship. They have a pivot table which manages the relationship which has additional information, so my table structure is as follows:

• ads
  • id
  • ...
• campaign_ads
  • campaign_id
  • ad_id
  • spend
  • sent
  • clicks
  • leads
  • ftds
• campaigns
  • id
  • ...

I am trying to fetch the results of a paginate query using the Ad models' query function, but in addition to the Ad models' fields, I would also like to fetch the sum of spend, sent, clicks, leads and ftds from the related Campaign models' pivots.

I have come up with the following code, which returns the correct information in the collection, but returns an incorrect value for the count

const Ad = use('App/Models/Ad');
const query = Ad.query()
                .leftJoin('campaign_ads', 'ads.id', 'campaign_ads.ad_id')
                .select('ads.*')
                .sum('campaign_ads.spend as spend')
                .sum('campaign_ads.sent as sent')
                .sum('campaign_ads.clicks as clicks')
                .sum('campaign_ads.leads as leads')
                .sum('campaign_ads.ftds as ftds')
                .groupBy('ads.id')
                .paginate()

I assume that this is related to how the paginate function rewrites or performs the query, but I have no idea how to fix it.

Answer 1

Here is some example usage based on the answer:

const Ad = use('App/Models/Ad');
const query = Ad.query()
                .leftJoin('campaign_ads', 'ads.id', 'campaign_ads.ad_id')
                .select('ads.*')
                .sum('campaign_ads.spend as spend')
                .sum('campaign_ads.sent as sent')
                .sum('campaign_ads.clicks as clicks')
                .sum('campaign_ads.leads as leads')
                .sum('campaign_ads.ftds as ftds')
                .groupBy('ads.id')
const paginate = async (query, page = 1, perPage = 20) {
  // Types of statements which are going to filter from the count query
  const excludeAttrFromCount = ['order', 'columns', 'limit', 'offset', 'group']
  // Clone the original query which we are paginating
  const countByQuery = query.clone();
  // Case Page and Per Page as Numbers
  page = Number(page)
  perPage = Number(perPage)
  // Filter the statments from the array above so we have a query which can run cleanly for counting
  countByQuery.query._statements = _.filter(countByQuery.query._statements, (statement) => {
    return excludeAttrFromCount.indexOf(statement.grouping) < 0
  })
  // Since in my case, i'm working with a left join, i'm going to ensure that i'm only counting the unique models
  countByQuery.countDistinct([Ad.table, 'id'].join('.'));
  const counts = await countByQuery.first()
  const total = parseInt(counts.count);
  let data;
  // If we get a count of 0, there's no point in delaying processing for an additional DB query
  if (0 === total) {
    data = [];
  }
  // Use the query's native `fetch` method, which already creates instances of the models and eager loads any relevant data
  else {
    const {rows} = await query.forPage(page, perPage).fetch();
    data = rows;
  }
  // Create the results object that you would normally get
  const result = {
    total: total,
    perPage: perPage,
    page: page,
    lastPage: Math.ceil(total / perPage),
    data: data
  }
  // Create the meta data which we will pass to the pagination hook + serializer
  const pages = _.omit(result, ['data'])
  
  if (Ad.$hooks) {
    await Ad.$hooks.after.exec('paginate', data, pages)
  }
  // Create and return the serialized versions
  const Serializer = Ad.resolveSerializer()
  return new Serializer(data, pages);
}
paginate(query, 1, 20)
    .then(results => {
        // do whatever you want to do with the results here
    })
    .catch(error => {
        // do something with the error here
    })

Answer 2

So, as I noted before in my notes, the problem that I was have was caused by how Lucid's query builder handles the paginate function, so I was forced to "roll my own". Here's what I came up with:

paginate (query, page = 1, perPage = 20) {
  // Types of statements which are going to filter from the count query
  const excludeAttrFromCount = ['order', 'columns', 'limit', 'offset', 'group']
  // Clone the original query which we are paginating
  const countByQuery = query.clone();
  // Case Page and Per Page as Numbers
  page = Number(page)
  perPage = Number(perPage)
  // Filter the statments from the array above so we have a query which can run cleanly for counting
  countByQuery.query._statements = _.filter(countByQuery.query._statements, (statement) => {
    return excludeAttrFromCount.indexOf(statement.grouping) < 0
  })
  // Since in my case, i'm working with a left join, i'm going to ensure that i'm only counting the unique models
  countByQuery.countDistinct([this.#model.table, 'id'].join('.'));
  const counts = await countByQuery.first()
  const total = parseInt(counts.count);
  let data;
  // If we get a count of 0, there's no point in delaying processing for an additional DB query
  if (0 === total) {
    data = [];
  }
  // Use the query's native `fetch` method, which already creates instances of the models and eager loads any relevant data
  else {
    const {rows} = await query.forPage(page, perPage).fetch();
    data = rows;
  }
  // Create the results object that you would normally get
  const result = {
    total: total,
    perPage: perPage,
    page: page,
    lastPage: Math.ceil(total / perPage),
    data: data
  }
  // Create the meta data which we will pass to the pagination hook + serializer
  const pages = _.omit(result, ['data'])

  // this.#model references the Model (not the instance). I reference it like this because this function is part of a larger class
  if (this.#model.$hooks) {
    await this.#model.$hooks.after.exec('paginate', data, pages)
  }
  // Create and return the serialized versions
  const Serializer = this.#model.resolveSerializer()
  return new Serializer(data, pages);
}

I only use this version of pagination when I detect group by in my query, and it follow's Lucid's own paginate function pretty closely, and returns identical feedback. While it's not a 100% drop-in solution, it's good enough for my needs