Jerodev Jerodev - 6 months ago 20
SQL Question

Order query by special relation and paginate

I have a website that is listing different products on a page using Laravel pagination. The products on this page should be ordered by their name, however, here lies the problem.

The structure of my database looks like this:

+----------+ +--------------+
| Products | | Translations |
+----------+ +--------------+
| Id | | product_id |
| ... | | slug |
+----------+ | locale |
| value |
+--------------+


The name of every product is available in the
translations
table where
slug='name'
. However, not every product has a translation in every available
locale
.

The name of the product should be the translation in the given locale. If this is not available, the English name is used. If this is not available either, the first available translation is used.

Is it somehow possible to do this in eloquent or MySQL? I have been looking for solutions but couldn't find one that did exactly this.

Answer

You should try to use the COALESCE function, which returns the first non-null value such as:

SELECT COALESCE(<sub-query by locale>, 
    <sub-query to bring english name>, 
    <sub-query to bring any other value you want> FROM product;