What is better for performance?


#1

Let’s say, we have 6 post categories, that have more than 100 posts each, in order to get these 6 categories with 20 random posts from each there are two ways to do this:

  1. Through loop, with limit(20) this will create 7 queries (n+1), but will delegate random() to DB layer, as a result we get all the data already prepared.
  2. Through eager loading, that will create 2 queries, but will request all the posts that are connected to categories, then we just have to get 20 random from array and limit all that. It can be done in multiple ways.
    So what is better?

#2

Maybe this

Category
	.query()
	.with('posts', (postsQuery) => {
		postsQuery.limit(20)
	})
	.fetch()

#3

Tried it, in that case we get these 20 posts for all the categories, not for each of them, but in total.


#4

Ohhh yeah, there is a helper method, that let you override the eager loading query.

On mobile right now, lemme share a working example tomorrow ( if possible with that helper method )


#5

Also do you have a belongsToMany relationship or hasMany


#6

It is a hasMany relation :slight_smile:


#7

So that means, a post always belongs to a single category?


#8

Yes, Post - belongsTo a Category, and Category - hasMany Posts.


#9

So this works to fetch 5 latest posts for each category.

  return Category
    .query()
    .with('posts', (postsBuilder) => {
      postsBuilder.eagerLoadQuery((relationQuery, foriegnKey, categories) => {
        relationQuery
          .from('posts as p')
          .whereRaw('(select count(*) from `posts` as p1 where p.category_id = p1.category_id AND p.id < p1.id) < 5')
          .whereIn(`p.${foriegnKey}`, categories)
          .orderBy('p.id', 'desc')
      })
    })
    .fetch()

So eagerLoadQuery method simply overrides the eager load query Adonis makes and you are free to create any query and the output result will be used.


#10

That is awesome, great! Thank you!