Caique Monteiro Araujo Caique Monteiro Araujo - 22 days ago 7
MySQL Question

MySQL, UNION ALL with SQL_CALC_FOUND_ROWS not working sometimes

I'm having some problems with UNION ALL using SQL_CALC_FOUND_ROWS in MySQL, InnoDB. First let me show a simple database schema to explain what's going on...

Database Schema

Media Table

media_id title
-------------------------------------
1 Empire of the Sun


Genre Pattern Table

genre_id name
-------------------------------------
1 Action
2 Adventure


Locale Table

locale_id code
-------------------------------------
1 en_US
2 pt_BR
3 fr_FR


Genre Translations Table

genre_id locale translation
-------------------------------------
1 1 Action
1 2 Ação
2 1 Adventure
2 2 Aventura


Media Genres Table

media_id genre_id
-------------------------------------
1 1
1 2


Now it's simple: I need to return a genre name compatible with user locale. Let's suppose that user locale is pt_BR. To I get results I use the follow SQL:

select gt.name
from media_genres mg
inner join genre_translation gt on (mg.genre_id = gt.genre_id)
where (mg.media_id = 1 and gt.locale_id = 2)


It returns:

name
-------------------------------------
Ação
Aventura


But, now, let's suppose that user locale is fr_FR. There's no translation for fr_FR, right? The solution I found was use a UNION ALL with SQL_CALC_FOUND_ROWS, then I use the following SQL:

select SQL_CALC_FOUND_ROWS gt.name
from media_genres mg
inner join genre_translation gt on (mg.genre_id = gt.genre_id)
where (mg.media_id = 1 and gt.locale_id = 3)

UNION ALL

select gt.name
from media_genres mg
inner join genre_translation gt on (mg.genre_id = gt.genre_id)
where FOUND_ROWS() = 0 and (mg.media_id = 1 and gt.locale_id = 1)


If there's no found rows for locale_id = 3 and media_id = 1, then I'll get the results for locale_id = 1 (default) and media_id = 1.

The problem is: the statement sometimes return right data and sometimes return no data.

And my main question: why is this happening?

Answer

The way to accomplish this is by using LEFT JOIN to join with the translation table, and then use IFNULL to supply a default when there's no match.

SELECT mg.genre_id, IFNULL(gt2.name, gt1.name) AS name
FROM media_genres AS mg
JOIN genre_translation AS gt1 ON mg.genre_id = gt.genre_id AND gt.locale_id = 1
LEFT JOIN genre_translation AS gt2 ON mg.genre_id = gt.genre_id AND gt.locale_id = 3
WHERE mg.media_id = 1
Comments