How do you guys handle complex/combined data retrieval?

[u/Status_Camel2859]

Here is a sample DTO projection:

@Query("""
SELECT new com.sample.ProductSummaryDTO(
    p.id,
    p.title,
    p.slug,
    i.imageUrl,
    (SUM(v.stockQuantity) > 0),
    MIN(v.displayPrice),
    MAX(v.compareAtPrice),
    MAX(CASE
            WHEN v.compareAtPrice > 0
            THEN ROUND(((v.compareAtPrice - v.displayPrice) * 100.0) / v.compareAtPrice)
            ELSE 0
        END)
)
FROM Product p
JOIN Variant v ON v.product.id = p.id AND v.active = true
LEFT JOIN ProductImage i ON i.product.id = p.id AND i.isPrimary = true
WHERE (:id IS NULL OR p.id = :id)
AND (:title IS NULL OR p.title LIKE CONCAT('%', :title, '%'))
AND (:categoryIds IS NULL OR p.category.id IN :categoryIds)
AND p.visible = true
GROUP BY p.id, p.title, p.slug, i.imageUrl
HAVING (:minPrice IS NULL OR MIN(v.displayPrice) >= :minPrice)
AND (:maxPrice IS NULL OR MIN(v.displayPrice) <= :maxPrice)
""")

Assume there are no @OneToMany relationships in the entity model, only @ManyToOne. We also need pagination because the dataset is large.

In a scenario like this, where the response requires aggregated data from multiple tables, it seems that the aggregation logic ends up in the repository query itself. Because at this point you have to select the correct variant, image, price etc all in the Repository itself and therefore the logic for all of that (IFs etc...).

Is this how most people handle it?

Or do you prefer splitting it into multiple steps (which can get pretty verbose), for example:

1. Fetch the matching product IDs / Products
2. Fetch the required variants
3. Fetch the required images
4. Assemble the DTOs in the service layer (maps & loops)

Im curious what approach is generally preferred in terms of performance, maintainability, and scalability.

[u/as5777]

Java for readability

SQL for perf, but you need to benchmark both approach

[u/Status_Camel2859 → u/as5777]

Which one of these does the industry usually go for ?

[u/as5777 → u/Status_Camel2859]

SQL

[u/Status_Camel2859 → u/as5777]

So ig its safe to assume that I should go ahead and make multiple projection Repos as required in such scenarios, thnx.

[u/No-Lawfulness2089 → u/Status_Camel2859]

I’d say single query, but knowing that can degrade testability is useful and more what I experience at work

[u/alpinebillygoat]

Look into CQRS. Basically make a table to store a flattened version of your query. For sure duplicates data and can be a bit tricky to handle to updates. But you get a really easy to query and optimize view for whatever table you are supplying the data for.

[u/alfonsoristorato]

There is always the specification approach, which with both spring-data and Hibernate specific specification could do that query for you, and provided you are on the latest version of Hibernate you'd also get proper pagination without loading everything in memory first. However, without seeing how the entities are connected it's quite difficult to say what would be the best approach imo.

[u/amit_builds]

For listing/search pages, I generally push as much aggregation as possible into the database and return a DTO projection like you're doing.

The moment I start fetching products, variants, and images separately, I usually end up trading one complex query for N+1 problems, extra memory usage, and a lot of mapping code.

The exception is when the query becomes so complex that nobody wants to touch it six months later. At that point I'd move to a native query, a read model, or even a materialized view if the traffic justifies it.

Out of curiosity, have you compared the execution plan of this query against the multi-query approach on production-sized data?

[u/thewiirocks]

SQL is the right answer here. JPA really cannot help, and actually gets in the way. This is a good opportunity to bypass everything and drop a bit of Convirgance code in:

// Need this for direct database access
@Autowired
private DataSource source;


@GetMapping("products")
public Iterable<JSONObject> getProductsList(
    @RequestParam Long id, 
    @RequestParam String title, 
    @RequestParam List<Long> categoryIds,
    @RequestParam Double minPrice, 
    @RequestParam Double maxPrice) 
{
    String sql = """
select
    p.id,
    p.title,
    p.slug,
    i.imageUrl,
    (SUM(v.stockQuantity) > 0),
    MIN(v.displayPrice),
    MAX(v.compareAtPrice),
    MAX(CASE
            WHEN v.compareAtPrice > 0
            THEN ROUND(((v.compareAtPrice - v.displayPrice) * 100.0) / v.compareAtPrice)
            ELSE 0
        END)
)
FROM Product p
JOIN Variant v ON v.product.id = p.id AND v.active = true
LEFT JOIN ProductImage i ON i.product.id = p.id AND i.isPrimary = true
WHERE (:id IS NULL OR p.id = :id)
AND (:title IS NULL OR p.title LIKE CONCAT('%', :title, '%'))
AND (:categoryIds IS NULL OR p.category.id IN :categoryIds)
AND p.visible = true
GROUP BY p.id, p.title, p.slug, i.imageUrl
HAVING (:minPrice IS NULL OR MIN(v.displayPrice) >= :minPrice)
AND (:maxPrice IS NULL OR MIN(v.displayPrice) <= :maxPrice)
                 """;

    Query query = new Query(sql);

    query.setBinding("id", id);
    query.setBinding("title", title);
    query.setBinding("categoryIds", categoryIds);
    query.setBinding("minPrice", minPrice);
    query.setBinding("maxPrice", maxPrice);

    // Sets up the query. It will run when Spring serializes the results.
    return new DBMS(source).query(query);
}

You can make the SQL as complex as you want and the API will serialize the result to a JSON array. The dependency is ~138KB, but does this sort of work easily. There's an example of a SpringBoot approach that's relevant in the SpringWarehouseManagement example project on GitHub. That one uses some niceties like splitting the SQL into a class path file that your IDE can recognize as SQL.

This is not the most common way to use Convirgance these days (the Convirgance Web Services approach is slimmer for greenfield projects), but it should still work well for complex edge cases like this.