Lucid load relationship with count


#1

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

const result = await Challenge
      .query()
      .isActive()
      .withCount('entries')
      .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


#2

This should produce what you’re after…

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

await data.rows.map(async 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 = {
  total,
  perPage: 10,
  page: page,
  lastPage: Math.ceil(total / perPage),
  data
}

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.


#3

Thanks!
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
      .table('challenges')
      .where('is_active', true)
      .orderBy('id', 'desc')
      .offset((page - 1) * 10)
      .limit(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 = rawChallenges.map(c => c.id)
    
    // 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
          WHERE
          subq.rank <= 2;
    `)).rows
    const entryProfileIds = _.uniq(rawEntries.map(e => 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 = rawChallenges.map(challenge => {
      let popularEntries = _.filter(rawEntries, { challenge_id: challenge.id })
      popularEntries = popularEntries.map(entry => {
        const entryProfile = _.find(rawProfiles, { id: entry.profile_id })
        const isVoted = votedEntryIds.some(entryId => entryId === entry.id)
        return {
          ...entry,
          is_voted: isVoted,
          profile: entryProfile
        }
      })
      return {
        ...challenge,
        popular_entries: popularEntries
      }
    })

    return { challenges: populatedResult, totalCount: challengeCount }
  }

#4

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…


#5

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.


#6

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:


#7

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


#8

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.


#9

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