Filter on JoinFetch'ed table

[andreyBelov]

Hi, thanks for useful project! I have a problem very similar to this closed issue https://github.com/tkaczmarzyk/specification-arg-resolver/issues/14

Spec:

@JoinFetch(paths = {"divisions"})
@And({
        @Spec(path = "podrId", spec = Null.class, constVal = "true"),
        @Spec(path = "divisions.name", params = "searchStr", spec = LikeIgnoreCase.class)
})
public interface DepartmentWithDivisionsSpec extends Specification<FsPodrEntity> {}

Entity:

@Entity
@Table(name = "FS_PODR", schema = "GKUOP")
@Getter
@Setter
@ToString
public class FsPodrEntity {

    @Id
    private Long id;

    @Column(name = "NAME", nullable = false)
    private String name;

    @Column(name = "PODR_ID")
    private String podrId;

    @OneToMany(cascade = {CascadeType.ALL})
    @JoinColumn(name = "PODR_ID")
    @ToString.Exclude
    private List<FsPodrEntity> divisions;

}

I get this error:

java.lang.IllegalStateException: Illegal attempt to dereference path source [null.divisions] of basic type
    at org.hibernate.query.criteria.internal.path.AbstractPathImpl.illegalDereference(AbstractPathImpl.java:82) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at org.hibernate.query.criteria.internal.path.AbstractPathImpl.get(AbstractPathImpl.java:174) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    at net.kaczmarzyk.spring.data.jpa.domain.PathSpecification.path(PathSpecification.java:53) ~[specification-arg-resolver-2.6.1.jar:na]

Could the problem be that my entity is hierarchical (i.e. children and parents in the same table) ? I use last version:

        <dependency>
            <groupId>net.kaczmarzyk</groupId>
            <artifactId>specification-arg-resolver</artifactId>
            <version>2.6.1</version>
        </dependency>

If I don't have request parameter "searchStr" all it's ok and sql query looks like this:

select distinct fspodrenti0_.id      as id1_2_0_,
                divisions1_.id       as id1_2_1_,
                fspodrenti0_.name    as name2_2_0_,
                fspodrenti0_.podr_id as podr_id3_2_0_,
                divisions1_.name     as name2_2_1_,
                divisions1_.podr_id  as podr_id3_2_1_,
                divisions1_.podr_id  as podr_id3_2_0__,
                divisions1_.id       as id1_2_0__
from gkuop.fs_podr fspodrenti0_
         left outer join gkuop.fs_podr divisions1_ on fspodrenti0_.id = divisions1_.podr_id
where fspodrenti0_.podr_id is null

[jradlica]

Hi @andreyBelov , you should use @Join with an alias, for example:

@Join(path = "divisions", alias = "d")
@And({
        @Spec(path = "podrId", spec = Null.class, constVal = "true"),
        @Spec(path = "d.name", params = "searchStr", spec = LikeIgnoreCase.class)
})
public interface DepartmentWithDivisionsSpec extends Specification<FsPodrEntity> {}

or (version with fetching lazy associations):

@Join(path = "divisions", alias = "d")
@JoinFetch(paths = {"divisions"})
@And({
        @Spec(path = "podrId", spec = Null.class, constVal = "true"),
        @Spec(path = "d.name", params = "searchStr", spec = LikeIgnoreCase.class)
})
public interface DepartmentWithDivisionsSpec extends Specification<FsPodrEntity> {}

[andreyBelov]

@jradlica thanks for fast reply Second version looks good, but it create another problem. Using it I receive query like this with extra inner join:

select distinct fspodrenti0_.id      as id1_2_0_,
                divisions2_.id       as id1_2_1_,
                fspodrenti0_.name    as name2_2_0_,
                fspodrenti0_.podr_id as podr_id3_2_0_,
                divisions2_.name     as name2_2_1_,
                divisions2_.podr_id  as podr_id3_2_1_,
                divisions2_.podr_id  as podr_id3_2_0__,
                divisions2_.id       as id1_2_0__
from gkuop.fs_podr fspodrenti0_
         inner join gkuop.fs_podr divisions1_ on fspodrenti0_.id = divisions1_.podr_id
         left outer join gkuop.fs_podr divisions2_ on fspodrenti0_.id = divisions2_.podr_id
where (fspodrenti0_.podr_id is null)
  and (upper(divisions1_.name) like ?)

This query filters records by the extra inner join table's field "divisions1_.name", and it does't actually limit the output.

I need resulting query looked like this then all will works as expected:

select distinct fspodrenti0_.id      as id1_2_0_,
                divisions2_.id       as id1_2_1_,
                fspodrenti0_.name    as name2_2_0_,
                fspodrenti0_.podr_id as podr_id3_2_0_,
                divisions2_.name     as name2_2_1_,
                divisions2_.podr_id  as podr_id3_2_1_,
                divisions2_.podr_id  as podr_id3_2_0__,
                divisions2_.id       as id1_2_0__
from gkuop.fs_podr fspodrenti0_
         left outer join gkuop.fs_podr divisions2_ on fspodrenti0_.id = divisions2_.podr_id
where (fspodrenti0_.podr_id is null)
  and (upper(divisions2_.name) like ?)

--- update I can achieve this myself by implementing Specification like this:

        Specification<FsPodrEntity> s = new Specification<FsPodrEntity>() {
            @Override
            public Predicate toPredicate(Root<FsPodrEntity> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
                Predicate isNull = cb.isNull(r.get(FsPodrEntity_.podrId));
                Join<FsPodrEntity, FsPodrEntity> fetch = (Join<FsPodrEntity, FsPodrEntity>) r.fetch(FsPodrEntity_.divisions, JoinType.LEFT);
                Predicate like = cb.like(fetch.get(FsPodrEntity_.name), "1 отдел");
                return cb.and( isNull , like );
            }
        };

But I would like this cool library to do it for me

[jradlica]

@andreyBelov The need to use two joins in this case (@Join & @JoinFetch) is due to the current library limitations. This should be improved in the next release (in September).

[tkaczmarzyk]

Hi @andreyBelov . Sorry for the huge delay. For whatever it's worth: v2.8.0 was just released and thanks to @jradlica great contribution, the issue should be resolved now.