Support projections on query methods that take a dynamic query type (Specification or Querydsl Predicate) [DATAJPA-1033]

[spring-projects-issues]

Sebastian Staudt opened DATAJPA-1033 and commented

Currently there seems no (at least no obvious) way to mix projections and specifications.

class ExampleRepository extends JpaRepository<Example, Integer>, JpaSpecificationExecutor<Example> {

    // Overriden from JpaRepository, just to show the whole picture
    // Works without problems
    @Override
    List<Example> findAll();

    // A bit odd, due to the "By", but works - see DATAJPA-680
    List<ExampleProjection> findAllProjectedBy();

    // Overriden from JpaRepository, just to show the whole picture
    // Works without problems
    @Override
    List<Example> findAll(Specification<Example> spec);

    // This would be great, but doesn't work.
    List<ExampleProjection> findAllProjectedBy(Specification<Example> spec);

}

A query method like the last above causes a java.util.NoSuchElementException inside org.springframework.data.jpa.repository.query.CriteriaQueryParameterBinder#bind during query. (Which may be another problem on its own.)

---

Affects: 2.0 M1 (Kay), 1.11 RC1 (Ingalls), 1.10.6 (Hopper SR6)

Issue Links:

• DATAJPA-393 Add support for QueryDSL projections in JPA repositories

Referenced from: pull request #430

107 votes, 96 watchers

[spring-projects-issues]

Oliver Drotbohm commented

There's currently a clean separation between query methods and dynamic queries using either Specification or Querydsl. The latter two only work to the extend of what APIs JpaSpecificationExecutor and QuerydslPredicateExecutor expose respectively. Projections on the other hand only work with query methods.

Your findAllProjectedBy() is — despite being an unusual one as it doesn't declare any criteria — a standard query method. I can see how this looks compelling if reduced to a the most simple — and by that at the same time most unrealistic — way of using that. What is supposed to happen, if the query methods contain criteria expressions themselves? What if a manually declared query is used with the query method?

I guess we could check the query method for that special case (like "It takes a well known dynamic query type like Specification and Querydsl's Predicate") and outright reject any criteria elements declared in the method or a manually declared query

[spring-projects-issues]

Sebastian Staudt commented

I think the most valuable benefit would be to reuse more complicated specifications (e.g. with multiple joins) and be able have a projected view at the same time.

As an example I switched various parts of an application to specification to restrict the view on objects based on user permissions or filters set in the application. This works great. The queries are efficient and the developer experience is fantastic. The code is much more readable and dynamic. Instead of defining dozens of query methods you can mix and match specifications on demand.

Currently, it feels like choosing between optimized results (projections) and powerful queries (specifications)

[spring-projects-issues]

Sebastian Staudt commented

Recently, I found a workaround for implementing this feature in a custom repository implementation (see Gist).

The main problem here is that QueryUtils.toExpressionRecursively is not package-private. This seems appropriate as it is an internal helper method.
But as long as this issue is not resolved I think it should be made public.

If a future step might incorporate code similar to mine into SimpleJpaRepository it would have to be public, too.

[spring-projects-issues]

daniel r. commented

hi,

any updates on this?

Best Regards,
DR

[spring-projects-issues]

Marc Collin commented

any update?

 

any complex dynamic search will pass by an custom repository implementation.... and no projection is possible

[spring-projects-issues]

sfwhite commented

I'm also in support of this.

We have a use case where we have complex search criteria built out in specifications, but we need a lightweight result object (projection). We currently have to map to the lightweight by hand, as there isn't a way to combine the two easily.

I also found a similar github project referenced from a stackoverflow question.

There's definitely demand for this enhancement

[spring-projects-issues]

Sergio Clares Martínez commented

??We have a use case where we have complex search criteria built out in specifications, but we need a lightweight result object (projection).??

Same here.

Regards

[spring-projects-issues]

Morjoh commented

Any news about this?

@Query(value = "",@Query(value = "", countQuery = "", nativeQuery = true)
Page<ProfileProjection> findByXXX(Specification<ProfileProjection> spec, Pageable pageable);

No exceptions, Specification is just being ignored without creating the "where" statement

[spring-projects-issues]

Christophe Maillard commented

Currently, it feels like choosing between optimized results (projections) and powerful queries (specifications).

Exactly. As I had to chose either/or, I've chosen specifications, but being able to project the results would be ()()(*)

Any news about this?

[spring-projects-issues]

Mahesh commented

Need above feature "Specifications with projections and pagination" very badly

[spring-projects-issues]

Hurelhuyag commented

Any news? Any workaround?

[spring-projects-issues]

Warsic Dia commented

I stumbled to this problem today.

And I think it's a "must have" to be able to mix projection and specifications.

in the meantime I found this github repository which could be a workaround.

But I did not give it a try yet

[spring-projects-issues]

Matija Folnović commented

Hello!

This issue, together with other related issues ( DATAJPA-393, DATAJPA-51, DATAJPA-1189 ) have a total of ~109 votes. Just thisissue alone is 3rd in total votes.

I'm aware you have limited resources and you need to somehow prioritize issues, but is there a way to prioritize this as it's cleary highly requested issue? :)

Another clear indicator this is highly requested is workaround Github repository above, which at the moment has 69 stars.

Thank you very much for all the hard work you do here! :)

[spring-projects-issues]

ziomill commented

This improvement would be great!

[spring-projects-issues]

Bernhard Kern commented

Yes, this improvedment would help us alot, is there any update in 2020. Thx!

[spring-projects-issues]

atg200 commented

I would really love this feature as well.  I have this use case all the time

[spring-projects-issues]

wirk commented

We would appreciate if this feature could be considered as we have several use cases. Cheers

[spring-projects-issues]

Leonardo Terrão commented

This improvement would be great for me too! I've many cases that need this feature. Thanks!

[spring-projects-issues]

liviu-c commented

well, it's a basic feature for any listing with dynamic filter. of course, there is criteria api, but this feature would be more apropiate ... 

[spring-projects-issues]

mhmdsalem1993 commented

any updates on this feature ?

[spring-projects-issues]

this9is3me commented

would really love to have this feature too! is there any update?

[spring-projects-issues]

lorenzodee commented

Anyone have some time to review?

#430

Thanks in advance

[spring-projects-issues]

Jens Schauder commented

lorenzodee I just had a quick look at your PR. Not in detail, just superficial the approach you have choosen.

Your approach is to extend the JpaSpecificationExecutor interface with copies of existing methods that additionally to existing parameters have an additional Specification argument.

But I find the original approach rather compelling even though Oliver Drotbohm argued against it some extend in the first comment: Start from query derivation and add Specification as an additional special argument.

I envision the following rules:

• Specification still can't be combined with @Query
• Predicates defined in the method name are applied first and are then combined with AND specification. This means it's the task of the Specification to handle any potential conflicts with existing predicates, e.g. handling the decision if a certain join is already present and should be reused by the Specification is up to the Specification to decide.
• Predicates defined in the method name need effectively get wrapped in parens so findByLastnameOrFirstname("Smith", "Peter", addressInIreland()) would translate to something like WHERE (lastname = :param1 OR firstname = :param2) AND address.country = "Ireland"

What does everybody else think?
Are there any problems with that?

Apart for Specification already being used for all kinds of stuff, like GROUP BY and controlling the select list which was never intended and isn't supported anyway.

This might even make JpaSpecifiactionExecutor superfluous. Although I'd keep that to a different change.

[spring-projects-issues]

Jens Schauder commented

We had a discussion in the team about this and there are some concerns, that with special handling of Specification as part of query derivation, this might get too complex over time.

In general my concern was shared about the current proposal that duplicating the methods in JpaSpecificationExecutor is not desirable and does not scale with the next combination of features that people might want.

Therefore an alternative was proposed to have one additional method in the JpaSpecificationExecutor it would probably look somewhat like this:

T findBy(Specification s, Collector c)

Collector (preliminary name) would be constructed by a fluent interface and would specify the return type and what gets actually selected.
Collectors.count could result in an Integer and a Select count.
Collectors.listOf(SomeProjection.class).distinct() would do a {{Select distinct of the columns in SomeProjection and would result in a List<SomeProjection>

Let us know what you think

[spring-projects-issues]

Jens Schauder commented

I'm personally not convinced that this reduces complexity compared to an extension of the query derivation approach. After all, it would create another way to specify projections, conversions and options. And it would be another (partial) DSL to configure queries.

The one thing I really like about the proposal is that it creates a dedicated type for projections. That would help with issues where users want to use an actual class as argument to their queries

[spring-projects-issues]

Oliver Drotbohm commented

The prototype I had locally a few years ago (which unfortunately got lost at some point) was basically just introducing overrides that took a Class<?> to basically construct a SELECT clause and apply the Specification. Effectively a single projection step put on top of the already existing specification execution model.

A very compelling argument with that is that it would still contain the added complexity in the specification related APIs, which are – important reminder – a Spring Data JPA specific thing. Anything that suggests a model that overlaps with existing general query capabilities unfortunately has to justify how that affects the programming model for other stores and probably has a hard time doing that if it starts with a very store specific abstraction in the first place

[spring-projects-issues]

Jens Schauder commented

After some more internal discussion we came up with the following plan:

We'll demo a custom implementation of this and then take a look at the code to see if we can extract some kind of infrastructure that makes it easier to implement this kind of method.

If this works out it would allow easy implementation of such methods, without the potential burden of supporting an ever grower complexity of interacting features

[474846718]

i need this feature too. i search google to flow the problem it lead me here.
if the project maintainer could implement Spec with Projections that would be great thing for me.
thank you!

[olantobi]

Do we have any update on this. Is it a WIP yet ? Thank you.

[bilalahmed54]

Any update on this please?

[Mr-DeWitt]

I would happy to see this solution implemented in Spring too. :)

[besafx]

Guys, since 3 years until now, i looking for this feature!

[masteryyh]

almost 6 years passed since this issue opened guys, any update now?

[ZaheerUdDeen]

We desperately need this.

[kth13]

This would be a really great feature. Would appreciate if Spring implements this

[puce77]

Also make sure expressions like the following work:
select(cb.construct(MyValueClass.class,...

[raghera]

Another thumbs up from me for implementing this. We may consider stop using Specifications due to this. I have tried the workaround on Github: https://github.com/pramoth/specification-with-projection
But it's not ideal, is not particularly active and seems incompatible with the latest versions of Springboot.

[ZaheerUdDeen]

@mp911de can you update us on this please ?

[mp911de]

Projection support for Querydsl was added with Spring Data JPA 2.6 via #2294. Projection support for the specification executor will be available in version 3.0, see #2274.

[gustavodecarli]

this case does not respect the UserDTO interface and returns the full User objects.is there any solution to this?

Page<UserDTO> findAll(Specification<User> specification, Pageable pageable)

[Muhammad-Sherif]

there is a solution for this Just replace Order model with ur model

 public List<Integer> findIdsBySpecification(Specification<Order> specification) {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Integer> query = builder.createQuery(Integer.class);
        Root<Order> root = query.from(Order.class);
        query.select(root.get("id")).where(specification.toPredicate(root, query, builder));
        return entityManager.createQuery(query).getResultList();
    }

[nguyenbn7]

Hi Spring team,

I have worked around with projection and specification issue and thought the same as topic owner but no hope and I have come to another solution like this

var productsPage = productRepository.findBy( hasCriteriasAndSort(params), q -> q.as(ProductProjection.class) .page(PageRequest.of(params.getPageIndex() - 1, params.getPageSize())));

which I think I accept it but there is an issue with this, when I called my api, Application log looks like this
Hibernate: select p1_0.id,p1_0.dtype,p1_0.description,p1_0.name,p1_0.picture_url,p1_0.price,p1_0.product_brand_id,p1_0.product_type_id from product p1_0 where 1=1 order by p1_0.name offset ? rows fetch first ? rows only
Hibernate: select p1_0.id,p1_0.name from product_brand p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name from product_type p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name from product_type p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name from product_brand p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name from product_type p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name from product_brand p1_0 where p1_0.id=?
Hibernate: select p1_0.id,p1_0.name from product_type p1_0 where p1_0.id=?
Hibernate: select count(p1_0.id) from product p1_0 where 1=1

which turns out to be N + 1 problem rather than fetch join. I have tested the projection without spec and it created the join statement but findBy can not.