TC
Twill CMSkingrozanski

"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?
ifox
ifox•101d 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?
kingrozanski
kingrozanski•100d 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•100d 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
kingrozanski
kingrozanski•100d 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•100d 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?
kingrozanski
kingrozanski•100d 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
Want results from more Discord servers?
Add your server
More Posts
Repeaters and Blockeditor won't work with media library.I saw the pst regarding JSON repeaters. So I switched to the block editor for the content that NEEDSTag Field on Block not Displaying Saved TagsHello. I am using the Tag Field in a block, the tags are being saved correctly, but the field is cFile link in view issueHi everyone i'm trying to render a file in blade view bug seem difficult to me how can i render linIssue using repository methods??? Twill 3Can someone please provide any support for the following error: Declaration of App\Repositories\PostHow can I make sure my assets are loaded correctly on a subdomain?I have a site for example website.nl and a twill installation on website.nl/subsite Now when I go tI can't see any images in twill media library.Hello, everyone! I have installed twill and tried to upload a image to media library. However, I amDatePicker ExceptionI'm getting an unexpected error while using a pretty simple DatePicker in my sidebar form. `UndefinSome fresh vibes would be nice :))Would love to hear some global announcement about twill cms this year and where it's heading @ifox .Edit defaultOrders in controllerWhat would be the most correct way to edit? $this->defaultOrders of ModuleController in my controlleVue2 EOL Plans?We're upgrading our application and generally upgrading our setup in preparation for the Vue2 EOL atRoute [admin.password.reset.link] not defined on upgrade from 2.x to 3Hey folks, I followed the upgrade guide step by step successfully, but when I try to visit the adminTwicpics & fit=cropApparently fit=crop is automatically added to the url, also when using Twicpics as image service. HoCan anyone help me get a single image/media field working within a json repeater ?I've detailed the issue here: https://github.com/area17/twill/discussions/2358DatePicker problemHi! sorry, what s wrong with my setup .... Just installed a fresh laravel app, with newest twill 3Admin api requests blocked for mixed-contentThis is an odd one and happening in a single environment (which unfortunately is production) . I knoI'm lookig for the DNS Settings of my website. Can anybody help me please?The person who made my website is not available and i need to access my dns settings. Thanks!How to load images in smaller size while keeping aspect ratio and not cutting parts of it off?Hello, I am trying display images with a smaller width that I uploaded in my CMS but without any cr419 login superadminHi all, I'm deploying a project with docker containers, I created a superadmin, I try to log in and Call to a member function permissions() on stringCan you give me a hint, please? In config/twill.php I specified return [ 'enabled' => [ 'perpermalinkGood morning, everyone. I wanted to ask a question. I would like to have the possibility to add a bu