Laravel initial page load is very slow because of duplicate queries it runs in order to get the correct translations

Viewed 235

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?

0 Answers
Related