Jerodev Jerodev - 1 year ago 79
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
table where
. However, not every product has a translation in every available

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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download