Spring Data Join with Specifications

Question

I'm trying to convert this raw sql query:

select product.* from following_relationship
join product on following_relationship.following=product.owner_id
where following_relationship.owner=input

Into Spring Data specifications, i think that my issue so far is on joining those tables.

Here is my current conversion in Specification:

protected Specification<Product> test(final User user){
   return new Specification<Product>() {
       @Override
       public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
           Join<FollowingRelationship,Product> pfJoin = query.from(FollowingRelationship.class).join("following");
           pfJoin.on(cb.equal(pfJoin.get("following"),"owner"));
           return  query.where(cb.equal(pfJoin.get("following"),user)).getGroupRestriction();

       }
   };
}

And I'm getting this exception :

Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessA
piUsageException: org.hibernate.hql.internal.ast.InvalidWithClauseException: with clause can only reference columns in the driving table

I will like to add that I'm new at Spring framework for instance this is my first application on spring, so my apologies for the newbie question ;)

Edit: added entities Product, FollowingRelationShip

Entity
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, property = "json_id_prop")
public class FollowingRelationship extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "OWNER", referencedColumnName = "uuid")
    private User owner;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOLLOWING", referencedColumnName = "uuid")
    private User following;

    public User getOwner() {
        return owner;
    }

    public void setOwner(User owner) {
        this.owner = owner;
    }

    public User getFollowing() {
        return following;
    }

    public void setFollowing(User following) {
        this.following = following;
    }

}

@Entity
@Table(name = "product")
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, property = "json_id_prop")
public class Product extends BaseEntity {

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "OWNER_ID", referencedColumnName = "uuid")
    private User owner;
    @NotNull
    private String name;
    @NotNull
    private String description;
    @NotNull
    private String price;
    @NotNull
    private String brand;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public User getOwner() {
        return owner;
    }

    public void setOwner(User owner) {
        this.owner = owner;
    }


}

Product and FollowingRelationShip entities do no have any explicit relationship, hence the join on my implementation about.What i want to achieve is to get all products from all users which another user follow in Spring data Specifications.

should `pfJoin.on(cb.equal(pfJoin.get("following"),"owner"));` be `pfJoin.on(cb.equal(pfJoin.get("following"),"owner_id"));` ? — Galz, Jul 05 '16 at 21:17
@Galz yes , owner_id is the name of the joined column which has reference to the owner uuid field (which is also the ID of the entity). Btw i tried already with both owner_id and owner but none work — murielK, Jul 06 '16 at 06:08

score 49 · Accepted Answer · edited Nov 21 '20 at 04:31

EDIT: Ok, I did quite a mess here, but I hope this time I'm closer to the right answer.

Consider (id's are auto-generated like 1 for John etc.):

INSERT INTO some_user (name) VALUES ('John');
INSERT INTO some_user (name) VALUES ('Ariel');
INSERT INTO some_user (name) VALUES ('Brian');
INSERT INTO some_user (name) VALUES ('Kelly');
INSERT INTO some_user (name) VALUES ('Tom');
INSERT INTO some_user (name) VALUES ('Sonya');

INSERT INTO product (owner_id,name) VALUES (1,'Nokia 3310');
INSERT INTO product (owner_id,name) VALUES (2,'Sony Xperia Aqua');
INSERT INTO product (owner_id,name) VALUES (3,'IPhone 4S');
INSERT INTO product (owner_id,name) VALUES (1,'Xiaomi MI5');
INSERT INTO product (owner_id,name) VALUES (3,'Samsung Galaxy S7');
INSERT INTO product (owner_id,name) VALUES (3,'Sony Xperia Z3');

INSERT INTO following_relationship (follower_id, owner_id) VALUES (4,1);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (5,1);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (4,2);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (6,2);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (6,3);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (1,3);

Based on simplified version of entities that You provided, and SomeUser Entity like:

@Entity
public class FollowingRelationship {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;

@ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name = "owner_id")
SomeUser owner;
    
@ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name = "follower_id")
SomeUser follower;

...

@Entity
public class Product {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
    
@ManyToOne()
@JoinColumn(name = "owner_id")
private SomeUser owner;
    
@Column
private String name;

...

@Entity
public class SomeUser {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
    
@Column
private String name;

@OneToMany(mappedBy = "owner")
private Set<Product> products = new HashSet<Product>();

@OneToMany(mappedBy = "owner")
private Set<FollowingRelationship> ownedRelationships = new HashSet<FollowingRelationship>();

@OneToMany(mappedBy = "follower")
private Set<FollowingRelationship> followedRelationships = new HashSet<FollowingRelationship>();

I have created Specification like:

public static Specification<Product> joinTest(SomeUser input) {
    return new Specification<Product>() {
        public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Join<Product,SomeUser> userProd = root.join("owner");
            Join<FollowingRelationship,Product> prodRelation = userProd.join("ownedRelationships");
            return cb.equal(prodRelation.get("follower"), input);
        }
    };
}

And now, we can execute the query with:

SomeUser someUser = someUserRepository.findOne(Specification.where(ProductSpecifications.userHasName("Kelly")));
List<Product> thatProducts = productRepository.findAll(Specification.where(ProductSpecifications.joinTest(someUser)));
System.out.println(thatProducts.toString());

We get:

[Product [id=1, name=Nokia 3310], Product [id=4, name=Xiaomi MI5], Product [id=2, name=Sony Xperia Aqua]]

And this in My opinion is equivalent of: "get all products from all users which another user follow" - get products of all users that Kelly is following.

Product and FollowingRelationship do not have any relationship the only thing they have in common are user where so each product has it's owner (user) where FollowingRelationship have the following field and follower field (which are all User type or entity). So im trying to get all the products that of all users that some other specific user is following. — murielK, Jul 07 '16 at 12:33
That why i need to join the Product entity on FollowingRelationship Entity where FollowingRelationship.following = product.owner — murielK, Jul 07 '16 at 12:53
Ok, I will rewrite the answer, but please show me Entites when You get home. I get the basic idea when You explained it now, but it will be a lot easier when I will see it. — patrykos91, Jul 07 '16 at 13:11
Helle @patrykos91 have you got the chance to check out my updated post? — murielK, Jul 08 '16 at 08:45
Yes, I'm working on it right now. Tell me if SQL Query like: SELECT p.* FROM product.p INNER JOIN user u ON p.owner_id = u.id INNER JOIN following_relationship fr ON u.id = fr.following WHERE fr.owner = input, would be satisfying? Would it give what You wanted to achieve? In my opinion it can be translated to : Give me FOLLOWED PRODUCTS, of all USERS, that are OWNED, by specified USER (input). If that's it , than I'm almost done with code, and I will post it soon with some explaination. — patrykos91, Jul 08 '16 at 09:11
I really appreciate your effort seriously. I will try that as soon as i get home. AND thank you very much and yes that is what im trying to achieve: "SELECT p.* FROM product.p INNER JOIN user u ON p.owner_id = u.id INNER JOIN following_relationship fr ON u.id = fr.following WHERE fr.owner = input" — murielK, Jul 08 '16 at 12:16
OMG it works. Im not sure if it is efficient but it works. Thanks a lot for your help. — murielK, Jul 08 '16 at 16:44
It was my first time with that Specification API, and I learned a lot, so I thank You too, and I'm glad I could help. — patrykos91, Jul 08 '16 at 21:13
@patrykos91 - Could you please share code for : `ProductSpecifications.userHasName("Kelly")` — PAA, Jul 24 '19 at 12:18

Spring Data Join with Specifications

1 Answers1

Linked