Search code examples
octobercmsoctobercms-plugins

High query overhead with Rainlab Translate for a dropdown menu

I have a model called Area which contains a list of area names that I need to populate a dropdown. The list is translated using the Rainlab Translate plugin.

If I just do a straightforward Area::lists() then the list is not translated. However, if I do Area::get()->lists() then it is translated but one query is run on the rainlab_translate_attributes table for every single item in the dropdown, causing ~100 queries to run and a 1.5s request duration.

Model

<?php namespace Namespace\PluginName\Models;

use Model;

class Area extends Model
{
    public $implement = ['RainLab.Translate.Behaviors.TranslatableModel'];

    public $translatable = ['name'];

    // .... 
}

View

<div class="form-group {{ errors.first('location_id') ? 'has-error' }}">
    {{ form_label('area_id','Area') }}
    {{ form_select('area_id', {'': 'Select...'} + area, null, {'class': 'form-control', 'placeholder': 'Select...'}) }}
    <small class="text-danger" data-validate-for="area_id"></small>
</div>

Component Option #1 (fast query but items don't get translated)

public function areas() {
    return Area::lists('name','id');
}

Component Option #2 (items translated but ~100 queries and very slow)

public function areas() {
    return Area::get()->lists('name','id');
}

In other similar situations I would add public $with = ['relation'] but the rainlab_translate_attributes table doesn't seem to have a model that I could related the Area model to.

UPDATE

I have created the following functions in my Area.php model:

public static function listAreas()
{
    $areas = Cache::rememberForever("all:" . App::getLocale()  , function() {
        return self::
        whereNotNull('iso3166_2')
        ->get()
        ->toArray();
    });

    return  self::makeCollection( $areas ) ;
}

public static function makeCollection ( array $models = [] )
{
    return self::hydrate( $models );
}

...and then in my component, I have tried:

$areas = Area::listAreas(); <-- this reads cached data immedately

$areas->lists('name','id'); <-- this causes a new query to be generated for every item in the collection, here's an example of one query:

select * fromrainlab_translate_attributeswherelocale= 'th' andmodel_id= '1275' andmodel_type= 'Namespace\PluginName\Models\Area' limit 1

I have verified that App::getLocale() is correctly set as th

Solution

  • I had the same requirements and solved it using caching . If you don't want to cache your queries ignore this answer but I think you should consider it.

    1) Make sure your RainLab Translator is configured, so when using App::getLocale() returns the Translator's active locale not Laravel's.

    2) Create a method in your model for front-end usage. The purpose is to cache the Model / Relations & Translated Attributes.

    E.g AreaModel.php

    public static function listAreas()
{
       $areas = Cache::tags([  'areas' ])
            ->rememberForever(  "all:" . App::getLocale()  , function() {
                return self::
                    with(['relation_model_name']) // Fetch the Relation
                    ->get()
                    ->toArray();
            });

    return  self::makeCollection( $areas ) ;
}

public static function makeCollection ( array $models = [] )
{
    return self::hydrate( $models );
}

    a) Here we are cahcing the query with a key that includes the active locale

    b) We are adding with for related models

    c) We just cache the whole collection ( No pluck / lists ) and converting back to eloquent model instance.

    The advantages is that now in your component Area::listAreas(); will return the cached collections and you can manipulated like any other.

    $areas = Area::listAreas(); // collection ( Area + Relation )

$dropdown = $areas->pluck('name', 'id'); // get Dropdown values for Areas...

    Some Consideration is to clear the cache ( Delete Cache Tag / Key ) every time a record is Updates, Added or Deleted ( Model + Relations ) .

    Below Screenshots of Redis Cache Store for a Store Model and its relation model Business Type;

    E.g of Store Model - EN E.g of Store Model - ES

    Update :

    First I apologize for using Redis in my example assuming everyone does. The Original post should have been more focused on the implementation. Please don't copy / paste code as I did.

    a) In my original answer I posted code using the hydrate() method to create an exisiting model instance from the cached records. It was confusing & not necesary but I doubt it has to do with the related queries to the rainlab translate. ( Needs confirmation )

    b) return self::whereNotNull('iso3166_2')->get()->lists('name','id') is more than enough to cache Areas records.

    c) In my comment I used pluck because lists is deprecated. pluck returns a collection - See here and here

    $areas = self::whereNotNull('iso3166_2')->pluck('name', 'id') ; // collection
$areas->toArray();

    I haven't tried the file-based caching yet with October and not sure about its behavior Vs Redis.

    Again, Some Considerations ;

    a) Please name your cache key to something unique and meaningful, in my post all + locale was related to a cache tag areas . e.g. areas.iso3166_2.locale ( Avoid overrides )

    b) Add Cache::forget('key'); in your models afterSave & afterDelete methods

    c) If you are caching related models also be careful to clear the cache when they change.