I’m working on a system where I need to implement search and sort functionality on data that is displayed in a grid. The data is stored in a primary table, but certain fields, such as names and descriptions, are stored in multiple languages in a separate translation table.

Example Scenario:

1. Primary Table: tbl_product

   • product_id (Primary Key)
   • product_code
   • is_active
   • created_on

2. Translation Table: tbl_product_label

   • label_id (Primary Key)
   • ref_product_id (Foreign Key to tbl_product.product_id)
   • language_code
   • product_name
   • product_description

The tbl_product_label table stores translations for product names and descriptions across different languages.

Requirements:

1. Search: I need to search products by their name or description based on the user’s language preference.
2. Sort: Sorting should be possible based on the translated product name.
3. Criteria Query: I am using JPA’s CriteriaQuery API for constructing the queries, as this allows dynamic query building which is required for the grid’s filtering, searching, and sorting functionalities.

Current Approach:

I’ve considered using a subquery within my CriteriaQuery to filter the products based on the translated name or description, but I’m unsure if this is the best approach, especially considering performance and maintainability.

Example of the SQL Query I Am Trying to Implement:

SELECT p.*
FROM tbl_product p
WHERE p.product_id IN (
    SELECT pl.ref_product_id
    FROM tbl_product_label pl
    WHERE pl.language_code = 'en'
    AND pl.product_name LIKE '%Sample%'
)
AND p.is_active = true
ORDER BY pl.product_name ASC;

When I write criteria Query for this it is really complex in search and sorting. There are n number of tables which uses translation – so what would be the generic and clean solution. I am thiking of creating view table for this case but that will not be the generic one. what should be the best and clean solution ?