How to check a property in any of multiple column when column value not null

Question

Below are some of the tables in my product app where I need to execute a case statement as per the data values.

class Payment implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;
                
    @JoinColumn(name = "payee_id", referencedColumnName = "id", nullable = true)
    @OneToOne(optional = true)
    private ProductPayee payeeId;
                
    @JoinColumn(name = "allocation_id", referencedColumnName = "id", nullable = true)
    @OneToOne(optional = true)
    private ProductAllocation allocationId;     //  can have value only if no payeeId(ie, when payeeId = null) else null
                
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = true)
    @OneToOne(optional = true)
    private Recipient userId;     //  if no payeeId
                
    @Column(name = "amount")
    private Double amount;
}

class ProductPayee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;
                
    @JoinColumn(name = "allocation_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = false)
    private ProductAllocation allocationId;
                
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = false)
    private Recipient userId;
                
}                        

class ProductAllocation implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;
                
    @JoinColumn(name = "product_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = true)
    private Product productId;
                
    @Column(name = "amount")
    private Double amount;
}

class GroupLink implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;

    @JoinColumn(name = "group_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = true)
    private Group groupId;

    @JoinColumn(name = "product_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = false)
    private Product productId;
}

class GroupStaff implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;

    @JoinColumn(name = "staff_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = false)
    private Staff staffId;

    @JoinColumn(name = "group_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = true)
    private Group groupId;
}     

class ProductStaff implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;

    @JoinColumn(name = "staff_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = false)
    private Staff staffId;

    @JoinColumn(name = "allocation_id", referencedColumnName = "id", nullable = false)
    @OneToOne(optional = false)
    private ProductAllocation allocationId;

    @Column(name="type")
    private int type; 
}

Recipient,Staff and Product are like normal Person & Item tables.

Below is the query that I execute to get the data where the Payment table has either payeeId with value or allocationId with value.

So (1) if payeeId is there, then allocationId will be null and check value in ProductPayee or (2) else allocationId is there and then payeeId will be null and check in ProductAllocation table.

Query query = session.createQuery("FROM Payment where " +
        "(case when payeeId != null then payeeId.allocationId.productId.id else allocationId.productId.id end in (SELECT productId.id from GroupLink where groupId.id in (SELECT groupId.id from GroupStaff where staffId.id = :staffId)) " +
        "OR case when payeeId != null then payeeId.allocationId.id else allocationId.id end in (SELECT allocationId.id FROM ProductStaff WHERE staffId.id = :staffId and type = :resType))) " +
        "order by date desc");

I need to avoid the null value column in where clause from two of the columns and look for the same value in both the columns which ever is not null, either one of the two column is always have a null value.

Above HQL query does not provide the expected result when executed. How do I write it correctly by avoiding null case in HQL?

UPDATE

Tried with COALESCE(payeeId.allocationId.productId.id, allocationId.productId.id) in (Select ...) , but still most of the records are missing.

UPDATE 2 :

What I want is COALESCE(payeeId.allocationId.productId.id, allocationId.productId.id) but it will work only if it is just COALESCE(payeeId, allocationId).So the problem is once the allocationId is NULL then allocationId.productId.id will return error COALESCE statement will exit, similarly if payeeId NULL then payeeId.allocationId.productId.id results error.How can I overcome this situation?

Answer 1

if you are using Hibernate 6.0+, may I suggest to use the UNION clause in the hql query, instead of the case, to help separate the different restrictions that has to be used, if payeeId or allocationId fields are null or not? I tried to rewrite your query using this approach, obtainig this:

"select p from Payment p where p.payeeId is not null and p.allocationId is null and "
          + "( "
          + "p.payeeId.allocationId.productId.id in (SELECT productId.id from GroupLink where groupId.id in (SELECT groupId.id from GroupStaff where staffId.id = :staffId)) "
          + "or "
          + "p.payeeId.allocationId.id in (SELECT allocationId.id FROM ProductStaff WHERE staffId.id = :staffId and type = :resType) "
          + ")"
          + "union "
          + "select p from Payment p where p.payeeId is null and p.allocationId is not null and "
          + "( "
          + "p.allocationId.productId.id in (SELECT productId.id from GroupLink where groupId.id in (SELECT groupId.id from GroupStaff where staffId.id = :staffId)) "
          + "or "
          + "p.allocationId.id in (SELECT allocationId.id FROM ProductStaff WHERE staffId.id = :staffId and type = :resType)"
          + ")"

As you can see, there is the union of two subqueries, the first occurrs when payeeId is not null, the other when allocationId is not null. I just enforced the condition of being not null exclusively.

You say in the question that "query not providing appropriate result when execute": which are the records that are missing, or are returned when they shouldn't?

Update, after the "coalesce" comment.

You can try to write the joins in the query, and apply the coalesce clause on the attributes you want, like this:

  "select p from Payment p "
      + "left join p.payeeId pp left join pp.allocationId ppa left join ppa.productId ppap "
      + "left join p.allocationId pa left join pa.productId pap "
      + "where coalesce(ppap.id, pap.id) in "
      + "    (SELECT productId.id from GroupLink where groupId.id in (SELECT groupId.id from GroupStaff where staffId.id = :staffId)) "
      + "  or coalesce(ppa.id, pa.id) in "
      + "    (SELECT allocationId.id FROM ProductStaff WHERE staffId.id = :staffId and type = :resType)"

Let us know if this still produces wrong records.