CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5
Jerodev
Jerodev

Reputation: 33196

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.

Upvotes: 1

Views: 39

Answers (1)

Jaumzera
Jaumzera

Reputation: 2369

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;

Upvotes: 1

Related Questions