High query overhead with Rainlab Translate for a dropdown menu

Question

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

Answer 1

You need to fire JOIN manually I guess, there seems no functionality available for collection.

$locale = 'de';
$query = \HardikSatasiya\DemoTest\Models\Relation::query();

$query->select($query->getModel()->getTable() .'.*');
$query->addSelect('rainlab_translate_attributes.attribute_data');
$query->leftJoin('rainlab_translate_attributes', function($join) use ($locale, $query) {
    $join
        ->on(\Db::raw(\DbDongle::cast($query->getModel()->getQualifiedKeyName(), 'TEXT')), '=', 'rainlab_translate_attributes.model_id')
        ->where('rainlab_translate_attributes.model_type', '=', get_class($query->getModel()))
        ->where('rainlab_translate_attributes.locale', '=', $locale)
    ;
});

$data = $query->get();
$translatedArray = [];
foreach ($data as $value) {
    if(is_null($value->attribute_data)) {
        $translatedArray[$value->id] = $value->name;
    }
    else {
        $translations = json_decode($value->attribute_data);
        $translatedArray[$value->id] = $translations->name;
    }

}
dd($translatedArray);

may be this will help you.

Answer 2

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;

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.

Answer 3

Another idea that could affect speed (if not number of queries) - indexing model's name property:

public $translatable = [
    ['name', 'index' => true]
];

Reference: https://github.com/rainlab/translate-plugin#indexed-attributes