Lucid load relationship with count


Hello, I want to load relationship with count. Currently my code block is:

const result = await Challenge
      .with('entries', builder => {
        builder.orderBy('like_count', 'desc').with('owner').limit(3)
      .orderBy('id', 'desc')
      .paginate(page, 10)

It’s loading total 3 entries but I want to load 3 for each Challenge (if there is 10 challenge, it should load 30 most liked entries related with them)

How can I achieve such thing? Such I change my db structure?

@virk any help would be appreciated


This should produce what you’re after…

const data = await Challenge.query()
                            .orderBy('id', 'desc')
                            .forPage(page, 10)

await i => i.load('entries', builder => {
  builder.orderBy('like_count', 'desc').with('owner').limit(3)

const counts = await Challenge.query().isActive().count('* as total')
const total = Array.isArray(counts) ? counts[0].total : 0

const result = {
  perPage: 10,
  page: page,
  lastPage: Math.ceil(total / perPage),

I don’t think you can really do it with fewer queries, but if you keep your pagination size reasonable, it shouldn’t be too big of a problem.


I made something like that (includes extra code). But it produces less query

async indexActive ({ request }) {
    const { userId } = request
    const page = Number(request.input('page') || 1)

    const rawChallenges = await Database
      .where('is_active', true)
      .orderBy('id', 'desc')
      .offset((page - 1) * 10)

    const challengeCount = await Cache.remember('index-active-challenge-count', 1, () => {
      return Challenge.query().where('is_active', true).getCount()

    if (!rawChallenges.length) {
      throw new EmptyPaginationException(`No challenge found on page ${page}`)

    const challengeIds = =>
    // using row_number() and partition I tried to fetch 3 most popular entry for each challenge in one query
    const rawEntries = (await Database
      .raw(`SELECT * FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY challenge_id ORDER BY like_count desc) AS rank, e.* FROM entries e WHERE e.challenge_id IN (${challengeIds.join(',')})) subq
          subq.rank <= 2;
    const entryProfileIds = _.uniq( => e.profile_id))

    const rawProfiles = await Cache.remember(`profiles-${entryProfileIds.join('.')}`, 60, () => {
      return Database.table('profiles').whereIn('id', entryProfileIds)

    let votedEntryIds = []
    if (userId) {
      votedEntryIds = await Cache.remember(`user-${userId}-votes`, 30, () => {
        return Vote.query().where('user_id', userId).pluck('entry_id')

    const populatedResult = => {
      let popularEntries = _.filter(rawEntries, { challenge_id: })
      popularEntries = => {
        const entryProfile = _.find(rawProfiles, { id: entry.profile_id })
        const isVoted = votedEntryIds.some(entryId => entryId ===
        return {
          is_voted: isVoted,
          profile: entryProfile
      return {
        popular_entries: popularEntries

    return { challenges: populatedResult, totalCount: challengeCount }


Interesting… I’ll have to play with that sort of query. I assume it’s generating a 0 indexed array of results, thus the <= 2 to get the top 3…


Yes, I didn’t want to generate n+1 query, but this usage may require additional indexing on table.

I have to use this approach in multiple places, now I’m looking to somehow integrate this on my model.


Actually, just tried this and it’s only producing 2 results per challenge id for me. are you getting three results each with this query?

I would also be curious how well that subquery will perform as the dataset grows. I’ve seen subqueries bring a db to it’s knees enough in the past that I generally avoid them as much as possible :slight_smile:


Yeah I just noticed as well, it should be <=3 or <4.
If table has propery indexes and with help of IN statement I think it should perform better than n+1 query.

I will try


Just occurred to me I have no idea (probably because it was fairly late night) why I opted for forPage() in my example, one could just as easily still use paginate() and iterate through it’s data array.


Some time ago I had the same goal and @virk provided an answer there, you may check it here: What is better for performance?