Wsrtjtyk

In the following (working) query:

SELECT q.id, q.section_id, q.type, q.required, q.condition,

CASE WHEN (t1.text_1 IS NULL) THEN

    CASE WHEN ((SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1) IS NULL) THEN 

        (SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)

    ELSE

        (SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)

    END

ELSE

    t1.text_1

END

AS translation

FROM questions q

LEFT JOIN translations t1 ON t1.item_id = q.id

    AND t1.item_model = 'questions'

    AND t1.language = 'fr'

ORDER BY q.position

You can see that the part (SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1) is repeated twice (the first to check if it's null, and the second to get the value).

Could the repeated same query a performance issue (I guess so)?

Is there a better way to rewrite this query, being DRY?

You can replace the inner CASE statement with coalesce() function:

coalesce(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1,

SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)

From the documenation:

coalesce(X,Y,...)

The coalesce() function returns a copy of its first non-NULL argument,
or NULL if all arguments are NULL. Coalesce() must have at least 2
arguments.

Similar is the ifnull() function.