0

Hi stackoverflow community,

I want to know if its possible to eval a value as a field in a MySQL request.

Here is my problem:
I have a post table (id, lg, text) and a lang table (id, lg1, lg2, lg3). My post translations in lg1 / lg2 / lg3 are linked following the table lang.

Example:
in lang:
id: 1 - lg1: 1 - lg2: 2 - lg3: 7

in post:
id: 1 - lg: lg1 - text: my text in lg1 language
id: 2 - lg: lg2 - text: my text in lg2 language
id: 7 - lg: lg3 - text: my text in lg3 language

Now I want to get all the translations from a post id (ex: 2) and a language (ex: lg2), here is my request:

select p.* from `post` p left join `lang` l on
(
        (l.lg1 = p.id) or
        (l.lg2 = p.id) or
        (l.lg3 = p.id)
) where l.lg2 = 2;


But if I want to add a language, i'll have to change all my requests... If I could do something like that:

select p.* from `post` p left join `lang` l on
(l.eval(p.lg) = p.id)
where l.lg2 = 2;

it would be great.

Any idea?

S.Daineko
  • 1,790
  • 1
  • 20
  • 29
iow
  • 29
  • 4
  • 5
    Any time you find yourself with enumerated column names (above, say, 2), alarm bells should start ringing. Consider whether your schema design is optimal – Strawberry May 24 '20 at 08:13
  • That's a simplified version of my code, in fact the language names arent "lg1" but the locale name (e.g. en_US or de_DE). But you're right, I should have an id_common field in post table, and a lang table who looks like that: id_common, lg, id_post. I'll try to see if I can easily use this solution. – iow May 24 '20 at 09:00
  • What is the purpose of the `lang` table? What does one row with the several foreign key means? Why would you run a query like selecting one row from the `lang` table? Why does the `post` table has a reference back to the `lang` table? You are building some kind of circle, with what purpose? You might want to look at https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization – Progman May 24 '20 at 09:37
  • 1
    The purpose of this `lang` table was to easily link `post` translation. But thank to Strawberry comment, I changed my mind into a far more simple way: no `lang` table and "id_common" in the `post` table. This way, my `post` are linked and I can get all translations in a simple request regardless of the number of languages. Is there any way to do what I wanted first tho? – iow May 24 '20 at 09:47
  • 1
    That sounds like the right way to go – Strawberry May 24 '20 at 09:59

0 Answers0