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?

Lucid load relationship with count
#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!


#13

I have a similar problem.
There are two models, Article and ArticleCategories.
Article belongsToMany ArticleCategories and ArticleCategories belongsToMany Article.
The pivot table is article_category.

I’m trying to the following:

const articleData = await ArticleCategories
      .query()
      .select('id', 'parent_id', 'permalink', 'title', 'visible')
      .where('permalink', _.toLower(allParams.category))
      .where('visible', true)
      .with('subcategories', q => {
        q.select('id', 'parent_id', 'permalink', 'title', 'visible')
          .whereNot('parent_id', null)
          .where('visible', true)
          .with('articles', q => {
            q.select('id', 'title', 'intro', 'visible', 'created_at', 'updated_at')
              .where('visible', true)
              .with('media', q => q.select('id', 'client_name', 'field_name', 'path'))
              .orderBy('created_at', 'DESC')
              .limit(allParams.limit)
          })
      })
      .fetch()

How to use eagerLoadQuery helper with many to many?
ArticleSubcategories has self referencing fk on some of its category ids aka subcategories (a function inside the model).
A colleague of mine stated that is not possible, but you say that it’s possible by overriding the adonis eager load query.
What are my options? This problem hunts me for months. Please help.

Here’s the mysql query I get:

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'savjeti', true ],
  __knexQueryUid: '501d590b-1d4e-4dfb-a4b7-14717a6906bf',
  sql:
   'select `id`, `parent_id`, `permalink`, `title`, `visible` from `article_categories` where `permalink` = ? and `visible` = ?' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ true, 3 ],
  __knexQueryUid: '2cf0dbb9-e7a3-402e-980a-a48f37cd6175',
  sql:
   'select `id`, `parent_id`, `permalink`, `title`, `visible` from `article_categories` where `parent_id` is not null and `visible` = ? and `article_categories`.`parent_id` in (?)' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ true, 9, 10, 12, 13, 19, 4 ],
  __knexQueryUid: '334c7d96-95e8-4278-8dac-d24109e21695',
  sql:
   'select `articles`.`id`, `articles`.`title`, `articles`.`intro`, `articles`.`visible`, `articles`.`created_at`, `articles`.`updated_at`, `article_category`.`article_id` as `pivot_article_id`, `article_category`.`article_category_id` as `pivot_article_category_id` from `articles` inner join `article_category` on `articles`.`id` = `article_category`.`article_id` where `visible` = ? and `article_category`.`article_category_id` in (?, ?, ?, ?, ?) order by `created_at` DESC limit ?' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 28, 27, 26, 2 ],
  __knexQueryUid: '8a276597-e728-453b-9ccf-51372bc8da39',
  sql:
   'select `media`.`id`, `media`.`client_name`, `media`.`field_name`, `media`.`path`, `article_media`.`media_id` as `pivot_media_id`, `article_media`.`article_id` as `pivot_article_id` from `media` inner join `article_media` on `media`.`id` = `article_media`.`media_id` where `article_media`.`article_id` in (?, ?, ?, ?)' }

#14

Can you be more clear about what you’re trying to achieve?

Just explain in plain language what your ideal outcome would be.