Inner join with more than one OR conditions in spring boot

Question

I am using spring boot specification and trying to execute a query that looks like this -

SELECT DISTINCT
    p.name
FROM
    partner p
        INNER JOIN
    detail d ON p.detail_id = d.id
        INNER JOIN
    account a ON d.account_id = a.id
        OR d.crm_id = a.top_parent
        OR d.crm_id = a.global_partner

I have used the code

  Join<Partner, Detail> details = root.join("detail");
  Join<Detail, Account> account = details.join("account");
  Predicate global = cb.equal(details.get("crm_id "), account.get("top_parent"));
  Predicate top = cb.equal(details.get("crm_id "), account.get("global_partner"));
  account.on(cb.or(global, top));

However, it creates the query

SELECT DISTINCT
    p.name
FROM
    partner p
        INNER JOIN
    detail d ON p.detail_id = d.id
        INNER JOIN
    account a ON d.account_id = a.id
        AND (d.crm_id = a.top_parent
        OR d.crm_id = a.global_partner)

Notice the AND operator in the query...I need to replace it OR operator

Another use case I am struggling with

@Entity
public class Detail {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private long id;
  
  @OneToMany(mappedBy = "detail", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
  private Set<Skill> skills;
}

I am trying to generate the query below using specifications

order by (select count(s.detail_id) from skill s where detail.id = s.detail_id AND s.category is not null) desc

I have used the code below

cq.orderBy(cb.desc(cb.size(details.get("skills"))));

But the query it generates is

order by (select count(s.detail_id) from skill s where detail.id = s.detail_id) desc

Notice that I am unable to add an extra AND to the order by clause

Answer 1

I believe you can not change that AND. could you change the query in the following way

SELECT DISTINCT p.name
FROM partner p INNER JOIN detail d ON p.detail_id = d.id, account a
where d.account_id = a.id
OR d.crm_id = a.top_parent
OR d.crm_id = a.global_partner

and the jpa criteria similar to

    Join<Partner, Detail> details = root.join("detail");
    Root<Account> account = criteria.from(Account.class);
    Predicate global = cb.equal(details.get("crm_id"), account.get("top_parent"));
    Predicate top = cb.equal(details.get("crm_id"), account.get("global_partner"));
    Predicate byId = cb.equal(details.get("account").get("id"), account.get("id"));
    Predicate or = cb.or(global, top, byId);
    criteria.where(or);