HQL and checking if object is null or object field have specific value

Question

I have problem with executing following query:

from Customer c where c.connectedUserID = 1 and (c.connectedFinancialAnalyst is null or c.connectedFinancialAnalyst.completed = false)

object c.connectedFinancialAnalyst can be null. What I need is to find Customers that dont have financial analyst or property in financial analyst has specific value.

Edit: completed property is boolean in postgres and in java

Output generated by Hibernate

select
        customer0_.id as id1_0_,
        customer0_.birthDate as birthDat2_0_,
        customer0_.birthPlace as birthPla3_0_,
        customer0_.city as city4_0_,
        customer0_.email as email5_0_,
        customer0_.fatherName as fatherNa6_0_,
        customer0_.motherName as motherNa7_0_,
        customer0_.name as name8_0_,
        customer0_.pesel as pesel9_0_,
        customer0_.phoneNumber as phoneNu10_0_,
        customer0_.postalCode as postalC11_0_,
        customer0_.secondName as secondN12_0_,
        customer0_.sex as sex13_0_,
        customer0_.street as street14_0_,
        customer0_.surname as surname15_0_,
        customer0_.connectedFinancialAnalyst_id as connect17_0_,
        customer0_.connectedUserID as connect16_0_ 
    from
        Customer customer0_ cross 
    join
        FinancialAnalyst financiala1_ 
    where
        customer0_.connectedFinancialAnalyst_id=financiala1_.id 
        and customer0_.connectedUserID=1 
        and (
            customer0_.connectedFinancialAnalyst_id is null 
            or financiala1_.completed='false'
        )

then it should be 'false'. i.e enclosed inside single quotes '. Check out this link here. — M.Ali
– M.Ali, Commented Mar 3, 2014 at 21:02
i have already tried this. I thinh that the problem comes up when I try to check value on unexisting object — s7474
– s7474, Commented Mar 3, 2014 at 21:05

JustinKSU · Accepted Answer · 2014-03-03 21:33:23Z

6

You need to be explicit that you want to do a left join

from Customer c 
left join c.connectedFinancialAnalyst as analyst
where c.connectedUserID = 1 and (analyst.completed is null or analyst.completed = false)

edited Mar 3, 2014 at 21:33

answered Mar 3, 2014 at 21:22

JustinKSU

5,0192 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

JustinKSU Over a year ago

Let me know which one works for you and I'll update the answer accordingly.

s7474 Over a year ago

I've tried this, but it have the same output as ealier. im quite sure that problem is with: 'c.connectedFinancialAnalyst.completed is null or c.connectedFinancialAnalyst.completed = false'

JustinKSU Over a year ago

Try doing a left join.

s7474 Over a year ago

It returned expected number of rows, now i have to deal with returned object's becouse they r objects of other type

JustinKSU Over a year ago

If you have select c from Customer c, then you will get the Customer object. Is that not what you want?

|

s7474 · Accepted Answer · 2014-03-04 21:11:45Z

0

I rewritten my code to Criteria and evrything works great. I suppose tkat the problem was with left join. When I used criteria i had to append Left Outter join like:

Criteria c = session.createCriteria(Customer.class, "c").createAlias("c.connectedFinancialAnalyst", "analyst", JoinType.LEFT_OUTER_JOIN);

Maybe when i would change left join at query to left outter join everything might work great ^^. But now my code seems to be a little bit more user friendly ^^

answered Mar 4, 2014 at 21:11

s7474

582 silver badges8 bronze badges

Collectives™ on Stack Overflow

HQL and checking if object is null or object field have specific value

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related