buildSpecification returns duplicate rows when combined with ManyToMany In Criteria

[yelhouti]

Overview of the issue

When we have a ManyToMany relationship, a request to the server with groupId.in=1,2,3 returns the rows with groupIds as many time as present in the filter.
ex:
JDL

entity Task {
    name String required
}

entity Group {
    name String required
}

relationship ManyToMany {
    Task{group(name)} to Group{task(name)}
}

If Task 1 has groups: 1,2,3,4
and a request is sent with groupId.in=1,2,3 the line is shown 3 times as there is no distinct in the code that build the specification: io.github.jhipster.service.QueryService<>.buildSpecification.

Motivation for or Use Case

Be able to list all entities linked to other entities, in my example, all tasks in some specific groups.

Reproduce the error

Related issues

Suggest a Fix

buildSpecification ahould have "an optional parameters" and when set to true calls, query.distinct(true)
A more genreic solution would be to add a parameter queryFunction that is applied to query, which would allow to set multiple orders...

JHipster Version(s)

6.1.2, not a regression

JHipster configuration

Entity configuration(s) entityName.json files generated in the .jhipster directory

Browsers and Operating System

• Checking this box is mandatory (this is just to show you read everything)

[DanielFran]

Ping @gzsombor

[pascalgrimaud]

@yelhouti : I find the time to test and confirm this. Sorry for the big delay. Do you know how to improve this ? In this case, do you want to contribute for that ?

[github-actions]

This issue is stale because it has been open 30 days with no activity.
Our core developers tend to be more verbose on denying. If there is no negative comment, possibly this feature will be accepted.
We are accepting PRs 😃.
Comment or this will be closed in 7 days

[ClaudineiOlSantos]

i have same issue. it is resolved?

[standors]

This problem persists in ver 7.0.0.beta-1
has somebody solution for it?

[Delirante]

Quick solution in generated code from jhipster is possible. For generation tool don't know.
In method ObjectQueryService.createSpecification (which is generated by jhipster) replace this part of code:
Specification<Object> specification = Specification.where(null);
with this:
Specification<Object> specification = (root, query, cb) -> { query.distinct(true); return null; };
Thanks to this will method createSpecification return Specification object with support for distinct values by default.

But it would by nice to have this implemented by default in generated code. Of course with option to turn it on / off.

[ThienNTN1]

Nice solution! Thanks a lot.

Quick solution in generated code from jhipster is possible. For generation tool don't know.
In method ObjectQueryService.createSpecification (which is generated by jhipster) replace this part of code:
Specification<Object> specification = Specification.where(null);
with this:
Specification<Object> specification = (root, query, cb) -> { query.distinct(true); return null; };
Thanks to this will method createSpecification return Specification object with support for distinct values by default.

But it would by nice to have this implemented by default in generated code. Of course with option to turn it on / off.

[iconben]

@pascalgrimaud Hi Pascal, I ran into the same problem and found there was solution for this closed issue, should it be reopened and fixed? Thanks.

[pascalgrimaud]

@iconben : sure :-)

[github-actions]

This issue is stale because it has been open 30 days with no activity.
Our core developers tend to be more verbose on denying. If there is no negative comment, possibly this feature will be accepted.
We are accepting PRs 😃.
Comment or this will be closed in 7 days

[standors]

is here stil some problem? I found out that the current generator generates code with distint enabled by filter parameter with name 'distinct'.

            if (criteria.getDistinct() != null) {
                specification = specification.and(distinct(criteria.getDistinct()));
            }

[DanielFran]

@pascalgrimaud Hi Pascal, I ran into the same problem and found there was solution for this closed issue, should it be reopened and fixed? Thanks.

@iconben Are you available to do a PR?

[DanielFran]

@mraible @mshima @gzsombor What do you think about this 2 solutions? Should we proceed with a fix?

Quick solution in generated code from jhipster is possible. For generation tool don't know. In method ObjectQueryService.createSpecification (which is generated by jhipster) replace this part of code: Specification<Object> specification = Specification.where(null); with this: Specification<Object> specification = (root, query, cb) -> { query.distinct(true); return null; }; Thanks to this will method createSpecification return Specification object with support for distinct values by default.

But it would by nice to have this implemented by default in generated code. Of course with option to turn it on / off.

is here stil some problem? I found out that the current generator generates code with distint enabled by filter parameter with name 'distinct'.

            if (criteria.getDistinct() != null) {
                specification = specification.and(distinct(criteria.getDistinct()));
            }

[DanielFran]

Quick solution in generated code from jhipster is possible. For generation tool don't know. In method ObjectQueryService.createSpecification (which is generated by jhipster) replace this part of code: Specification<Object> specification = Specification.where(null); with this: Specification<Object> specification = (root, query, cb) -> { query.distinct(true); return null; }; Thanks to this will method createSpecification return Specification object with support for distinct values by default.

But it would by nice to have this implemented by default in generated code. Of course with option to turn it on / off.

@gzsombor What do you think about this solution?

[iconben]

@DanielFran Hi Daniel, I suggest the 'distinct' filter already do the job. What do you think?

[mshima]

There is a distinct filter in criteria.
Closing.