TC
Twill CMS•13mo ago
Mikołaj Różański

"astrotomic/laravel-translatable" n+1 queries

I've got a package issue which is composer required by twill. 800+ queries on a model only getting one locale at a time based on the /en/ suffix. I have checked and this is the only item showing up in debug tab generating this. When i switch models to call directly from database without twill the amount of queries returned is 32 - and this is getting 388 record items on a foreach loop. Culprit line: /vendor/astrotomic/laravel-translatable/src/Translatable/Translatable.php:161 Has anyone experienced this when calling any Twill models and managed to solve the amount of queries in this package?
6 Replies
ifox
ifox•13mo ago
hi @M can you clarify what you mean by calling directly without Twill? What query exactly? And what method do you use in Twill to retrieve the models?
Mikołaj Różański
Mikołaj RóżańskiOP•13mo ago
With Twill models the model for lessons looks like this
 <?php

namespace App\Models;

use A17\Twill\Models\Behaviors\HasTranslation;
use A17\Twill\Models\Behaviors\HasPosition;
use A17\Twill\Models\Behaviors\Sortable;
use A17\Twill\Models\Behaviors\HasMedias;
use A17\Twill\Models\Behaviors\HasFiles;
use A17\Twill\Models\Behaviors\HasRelated;
use A17\Twill\Models\Model;

class Lesson extends Model implements Sortable
{
    use HasTranslation, HasPosition, HasMedias, HasFiles, HasRelated;

    protected $fillable = [
        'published',
        'position',
        'title',
        'description',
        'position',
        'url',
    ];

    public $translatedAttributes = [
        'active',
        'title',
        'description',
    ];

    public $mediasParams = [
        'lesson_image' => [
            'default' => [
                [
                    'name' => 'free',
                    'ratio' => 0
                ]
            ]
        ]
    ]; 
 <?php

namespace App\Models;

use A17\Twill\Models\Behaviors\HasTranslation;
use A17\Twill\Models\Behaviors\HasPosition;
use A17\Twill\Models\Behaviors\Sortable;
use A17\Twill\Models\Behaviors\HasMedias;
use A17\Twill\Models\Behaviors\HasFiles;
use A17\Twill\Models\Behaviors\HasRelated;
use A17\Twill\Models\Model;

class Lesson extends Model implements Sortable
{
    use HasTranslation, HasPosition, HasMedias, HasFiles, HasRelated;

    protected $fillable = [
        'published',
        'position',
        'title',
        'description',
        'position',
        'url',
    ];

    public $translatedAttributes = [
        'active',
        'title',
        'description',
    ];

    public $mediasParams = [
        'lesson_image' => [
            'default' => [
                [
                    'name' => 'free',
                    'ratio' => 0
                ]
            ]
        ]
    ];
And this is my test calling directly without twill dependencies:
 /*
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Lesson extends Model
{
    use HasFactory;

    protected $table = 'lessons';
    protected $primaryKey = 'id';
}
*/ 
 /*
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Lesson extends Model
{
    use HasFactory;

    protected $table = 'lessons';
    protected $primaryKey = 'id';
}
*/
this page same page also calls years, courses, lessons and twill related but dont think this matters towards the problem
ifox
ifox•13mo ago
yeah I definitely got that part, but I want to know how you are calling these 2 models, not the models themselves which code is triggering the queries you are observing
Mikołaj Różański
Mikołaj RóżańskiOP•13mo ago
In the case of the lessons it is the following: 
 $lesson_list = Lesson::join('lesson_translations', function($join) {
          $join->on('lessons.id', '=', 'lesson_translations.lesson_id')
                ->where('lesson_translations.locale', app()->getLocale())
                ->where('lesson_translations.title', '!=', "")
                ->whereNotNull('lesson_translations.title');
          })
          ->join('twill_related', 'lessons.id', '=', 'twill_related.related_id')
          ->where('related_type', 'App\Models\Lesson')
          ->where('subject_type', 'App\Models\Course')
          ->where('lessons.published', 1)
          ->orderBy('twill_related.position', 'ASC')
          ->select([
              'lessons.*',
              'lesson_translations.title AS title',
              'lessons.url AS url',
              'lesson_translations.lesson_id AS lesson_id',
              'twill_related.subject_id AS subject_id',
              'twill_related.position AS position'
          ])
          ->get();

        foreach ($temp_courses as $temp_course) {
            $lessons[] = $lesson_list->where('subject_id', $temp_course->course_id);
        }
 
 $lesson_list = Lesson::join('lesson_translations', function($join) {
          $join->on('lessons.id', '=', 'lesson_translations.lesson_id')
                ->where('lesson_translations.locale', app()->getLocale())
                ->where('lesson_translations.title', '!=', "")
                ->whereNotNull('lesson_translations.title');
          })
          ->join('twill_related', 'lessons.id', '=', 'twill_related.related_id')
          ->where('related_type', 'App\Models\Lesson')
          ->where('subject_type', 'App\Models\Course')
          ->where('lessons.published', 1)
          ->orderBy('twill_related.position', 'ASC')
          ->select([
              'lessons.*',
              'lesson_translations.title AS title',
              'lessons.url AS url',
              'lesson_translations.lesson_id AS lesson_id',
              'twill_related.subject_id AS subject_id',
              'twill_related.position AS position'
          ])
          ->get();

        foreach ($temp_courses as $temp_course) {
            $lessons[] = $lesson_list->where('subject_id', $temp_course->course_id);
        }
ifox
ifox•13mo ago
I mean, I'm not sure what you're identifying here, your non twill model doesn't have any of that complexity. And the problem is probably not this query, but rather that you are probably triggering some lazy loading on the retrived lessons when rendering the query you put together seems well optimized, so it's not that one directly triggering 800+ queries right?
Mikołaj Różański
Mikołaj RóżańskiOP•13mo ago
I've tested it one by one on all of the years, courses and lessons they all produce the same problem but because theres only 6 years theres less of the same query showing up, hence ive provided the lessons which there is 388 records returned which go into the $lessons[] and they look like this on the debugger - it might be a me issue, but as you say it is fine then must be something else:
No description

Did you find this page helpful?