Hi there,

I am having trouble getting a HQL query to render properly to SQL.

The query is as follows:

from InternalMessage i where i.receiver = :user and hasBeenRead = false ORDER BY i.sender DESC , i.senderGroup DESC

This is because in my system, a message can come from a group of users, or from a single user.

The following SQL gets generated:

select internalme0_.id as id, internalme0_.senderuserid as senderus2_, internalme0_.sendergroupid as senderba3_, internalme0_.receiveruserid as receiver4_, internalme0_.receivergroupid as receiver5_, internalme0_.subject as subject, internalme0_.message as message, internalme0_.hasbeenread as hasbeenr8_, internalme0_.timesent as timesent, internalme0_.messagetype as message10_ from pixel_internalmessage internalme0_, pixel_user user1_, pixel_group group2_ where internalme0_.senderuserid=user1_.id AND internalme0_.sendergroupid=group2_.id and ((internalme0_.receiveruserid=1 )) order by user1_.username DESC , group2_.name DESC

Which returns no rows because of this SQL:

internalme0_.senderuserid=user1_.id AND internalme0_.sendergroupid=group2_.id

changing the query so that the above reads:

(internalme0_.senderuserid=user1_.id OR internalme0_.sendergroupid=group2_.id )

functions exactly like I want it too! (I tried it in the database. returns all the rows I need!)

full:

select internalme0_.id as id, internalme0_.senderuserid as senderus2_, internalme0_.sendergroupid as senderba3_, internalme0_.receiveruserid as receiver4_,
internalme0_.receivergroupid as receiver5_, internalme0_.subject as subject, internalme0_.message as message, internalme0_.hasbeenread as hasbeenr8_, internalme0_.timesent as timesent,
internalme0_.messagetype as message10_ from pixel_internalmessage internalme0_, pixel_user user1_, pixel_group group2_ where (internalme0_.senderuserid=user1_.id or internalme0_.sendergroupid=group2_.id) and ((internalme0_.receiveruserid=1 )) order by user1_.username DESC , group2_.name DESC


So, the big question, the one thats kept me pacing back and forth from guitar to computer all day, is how do i get the HQL to produce the above SQL?

Any suggestions? Any help would be greatly appretiated.

Jay @ Pixelknowledge.com :: Web solutions.

Could this possibly have something to do with me needing to perform a join? (left, right, outer) ?

Impossible to tell without a mapping document (sigh).

sorry, didn't think it was that relevant for the query.