Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

martinszeltins's avatar

How to to use join and keyBy in relationships?

Hi, I am making a translation feature for my app. I have 3 tables - languages, language_translations and language_strings and I would like to return all the languages and their translations json as a result like this but I can't get the translations array to have the key from my language_strings table and the value from language_translations.

The desired result:

languages: [
    en: {
        lang_long: 'english',

        translations: {
            'good morning': 'good morning',
            'welcome': 'welcome',
            'bye': 'bye',
        }
    },

    es: {
        lang_long: 'español',
        
        translations: {
            'good morning': 'buenos días',
            'welcome': 'bienvenidas',
            'bye': 'adiós',
        }
    },

    ru: {
        lang_long: 'español',
                
        translations: {
            'good morning': 'доброе утро',
            'welcome': 'добро пожаловать',
            'bye': 'до свидания',
        }
    },
]

Here are my 3 tables

languages

+----+------------+-----------+
| id | lang_short | lang_long |
+----+------------+-----------+
| 1  | en         | english   |
| 2  | es         | español   |
| 3  | ru         | русский   |
+----+------------+-----------+

language_strings

+----+--------------+
| id | lang_string  |
+----+--------------+
| 1  | good morning |
| 2  | welcome      |
| 3  | bye          |
+----+--------------+

language_translations

+----+----------------+---------+------------------+
| id | lang_string_id | lang_id | lang_translation |
+----+----------------+---------+------------------+
| 1  | 1              | 1       | good morning     |
| 2  | 1              | 2       | buenos días      |
| 3  | 3              | 3       | до свидания      |
+----+----------------+---------+------------------+

So far I have been able to get the results like this but not 100% what I wanted.

return Language::with('translations')->get()->keyBy('lang_short');

// Result
Array
(
    [en] => Array
        (
            [id] => 1
            [lang_short] => en
            [lang_long] => english
            [created_at] => 2020-12-27T15:53:42.000000Z
            [updated_at] => 2020-12-27T15:53:42.000000Z
            [translations] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [lang_string_id] => 1
                            [lang_id] => 1
                            [lang_translation] => Login
                            [created_at] => 2020-12-27T15:53:42.000000Z
                            [updated_at] => 2020-12-27T15:53:42.000000Z
                        )

                    [1] => Array
                        (
                            [id] => 2
                            [lang_string_id] => 2
                            [lang_id] => 1
                            [lang_translation] => Welcome Back
                            [created_at] => 2020-12-27T15:53:42.000000Z
                            [updated_at] => 2020-12-27T15:53:42.000000Z
                        )

I wanted the translations array to be like this (I don't need id, lang_string_id etc...)

translations: {
    'good morning': 'доброе утро',
    'welcome': 'добро пожаловать',
    'bye': 'до свидания',
}
0 likes
0 replies

Please or to participate in this conversation.