How to write a spring boot jpa specification joining multiple tables

Question

I want to write below query using spring boot specification.

SELECT o.*
from orders as o
     inner join user as u on o.user_id = u.id
     inner join user_group as ug on u.user_group_id = ug.id
     left join order_product op on o.id = op.order_id
     left join mobile_order_product mop on op.id = mop.order_product_id
     left join mobile_device as md on mop.mobile_device_id = md.id
     left join tablet_order_product top on op.id = top.order_product_id
     left join tablet_device as td on top.tablet_device_id = td.id
where ug.id = 1
     and (md.imei = 123456789 or td.imei = 123456789)

I try to write specification like below but I couldn't find a way to join order_product table.

    public static Specification<Order> filterOrdersByGroupIdAndImei(int userGroupId, int imei) {
        return (root, query, cb) -> {
            Join<Object, User> user = root.join("user");
            Join<Object, UserGroup> userGroup = user.join("userGroup");
//            how to join order_product and other join tables

            Predicate equalPredicate = cb.equal(userGroup.get("id"), userGroupId);
            return cb.and(equalPredicate);
        };
    }

Technique

What about userGroup.join("order_product")? – Duke

it's not working – Technique

What does not work with that? – Duke

Answer 1

I am going to put answer in my own question.

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(referencedColumnName = "id", nullable = false)
    @JsonIgnore
    private User user;

    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderProduct> orderProducts ;
}

@Entity
@Table(name = "order_product")
public class OrderProduct {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(referencedColumnName = "id", nullable = false)
    @JsonIgnore
    private Order order;

    @OneToMany(mappedBy = "orderProduct", fetch = FetchType.LAZY)
    private List<MobileOrderProduct> mobileOrderProducts;


    @OneToMany(mappedBy = "orderProduct", fetch = FetchType.LAZY)
    private List<TabletOrderProduct> tabletOrderProducts;
}

@Entity
@Table(name = "mobile_order_product")
public class MobileOrderProduct {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String mobileCode;
    private String mobileNumber;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(referencedColumnName = "id", nullable = false)
    @JsonIgnore
    private MobileDevice mobileDevice;

    @ManyToOne(fetch = FetchType.LAZY)
    @JsonIgnore
    @JoinColumn(referencedColumnName = "id", nullable = false)
    private OrderProduct orderProduct;
}

@Entity
@Table(name = "mobile_device")
public class MobileDevice {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String serialNumber;
    private String imei;

    @OneToMany(mappedBy = "mobileDevice", fetch = FetchType.LAZY)
    @JsonIgnore
    private List<MobileOrderProduct> mobileOrderProducts;
}

Here I only included couple of my entity class because then you can understand the table structure correctly

    public static Specification<Order> filterOrdersByGroupIdAndImei(int userGroupId, String imei) {
        return (root, query, cb) -> {
            List<Predicate> list = new ArrayList<Predicate>();
            Join<Order, User> user = root.join("user");
            Join<User, UserGroup> userGroup = user.join("userGroup");
            Join<Order, OrderProduct> orderProduct = root.join("orderProducts", JoinType.INNER);

            Join<OrderProduct, MobileDevice> mobileDevice = orderProduct
                    .join("mobileOrderProducts", JoinType.LEFT)
                    .join("mobileDevice", JoinType.LEFT);

            Join<OrderProduct, TabletDevice> tabletDevice = orderProduct
                    .join("tabletOrderProducts", JoinType.LEFT)
                    .join("tabletDevice", JoinType.LEFT);

            list.add(cb.equal(userGroup.get("id"), userGroupId));
            list.add(cb.or(cb.equal(mobileDevice.get("imei"), imei), cb.equal(tabletDevice.get("imei"), imei)));
            Predicate[] p = new Predicate[list.size()];
            return cb.and(list.toArray(p));

}

Technique