I'm using Laravel for my website and it is very slow. I have a lot of translations on my website and I discovered through Laravel's debugbar that my site runs a lot of duplicate queries to get its translations which, after some testing, is the biggest speed issue.
To get the correct translation, I run this line in my blade:
{!! Translation::getString('translation_slug') !!}
that calls this function:
//TranslationHelper.php
public static function getString(string $string, array $variables = null) : string
{
if ($variables) {
$variables = array_map('strval', $variables);
$variables = array_flip($variables);
$variables = preg_filter('/^/', ':', $variables);
$variables = array_flip($variables);
}
$translatable = Translatable::where('slug', $string)->first();
if ($translatable) {
$language = Session::get("language");
if ($language) {
if ($variables) {
return strtr($translatable->getTranslation($language), $variables);
} else {
return $translatable->getTranslation($language);
}
} else {
if ($variables) {
return strtr($translatable->getTranslation('en'), $variables);
} else {
return $translatable->getTranslation('en');
}
}
} else {
return $string; // not found
}
}
As you can see it runs the getTranslation function from the Translatable model:
//Translatable.php
public function getTranslation(string $language) {
$lang = Language::where('shortcode', $language)->first();
if ($lang) $translation = Translation::where('language_id', $lang->id)->where('translatable_id', $this->id)->first();
else $translation = Translation::where('language_id', 2)->where('translatable_id', $this->id)->first();
if ($translation == null || !$translation || !$lang) {
$translation = Translation::where('language_id', 2)->where('translatable_id', $this->id)->first();
if ($translation) return strip_tags($translation->value, '<br><a><b><i><p><ul><li><strong><del><em><u>');
else return $this->slug;
}
else return strip_tags(Translation::where('language_id', $lang->id)->where('translatable_id', $this->id)->first()->value, '<br><a><b><i><p><ul><li><strong><del><em><u>');
}
I've looked into Laravel Cache and managed to cache the Language models like this:
$languages = Cache::rememberForever('languages', function() {
return Language::all();
});
$lang = $languages->where('shortcode', $language)->first();
This significantly lowered the duplicate queries it made but when I tried the same for the translation models like this:
$translations = Cache::rememberForever('translations', function() {
return Translation::all();
});
if ($lang) $translation = $translations->where('language_id', $lang->id)->where('translatable_id', $this->id)->first();
Laravel gives a fatal error:
Maximum execution time of 30 seconds exceeded
The lines where I run Translation::where gives hundreds of duplicate queries. I have a total of 6246 different translation entries so i'm guessing it has something to do with that. Did i make a mistake or is there maybe a better way to refactor this code and to get rid of the duplicate queries and improve my website's speed?