0

I have two tables

product

|----|---------------------|
| id |        code         |
|----|---------------------|
|  1 |          A          |
|  2 |          B          |

and

product_tarnslation
|---|------|---------------------|
|pid| name |        locale       |
|---|------|---------------------|
| 1 |prod A|          en         |
| 2 |prod B|          en         |
| 1 | منتج |          ar         |
| 2 |prod B|          ar         |

I need a query to search in any locale but get result for an only desired locale. eg: when I search by prod and I want Arabic translation

expected result
|---|------|---------------------|
|pid| name |        locale       |
|---|------|---------------------|
| 1 | منتج |          ar         |
| 2 |prod B|          ar         |

UPDATE I solved it by self join, but it resulted in a slow query.

SELECT t1.name as t1name, t2.name as t2name, t1.locale as t1locale, t2.locale as t2locale
FROM product_translation t1
INNER JOIN product_translation t2
on t1.translatable_id = t2.translatable_id
WHERE t1.name LIKE '%prod%'
AND t2.locale = 'ar'
  • Post ddl including indexes, t1. locale = 'en' will probably help. Also, do you need wildcard at the beginning of prod? – Lennart Jul 20 at 2:32
  • What does code have to do with anything? If pid is "product id", why is product 1 linked product 2? – Rick James Jul 20 at 3:01
  • Please post TEXT RESULTS of EXPLAIN SELECT SQL_NO_CACHE t1.name as t1name, t2.name as t2name, t1.locale as t1locale, t2.locale as t2locale FROM product_translation t1 INNER JOIN product_translation t2 on t1.translatable_id = t2.translatable_id WHERE t1.name LIKE '%prod%' AND t2.locale = 'ar'; so we can see what the OPTIMIZER is considering doing with your query. – Wilson Hauck Jul 20 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.