HQL: replace on elements()

2019-09-08 04:33发布

问题:

I have the following HQL-Query:

from Paperboy as paperboy where replace(replace(paperboy._mobile, ' ', ''), '/', '') = :nr or replace(replace(paperboy._phone, ' ', ''), '/', '') = :nr or :nr in elements(paperboy._additionalPhoneNumbers)

Now the problem is, that I also have to replace spaces and slashs in the _additionalPhoneNumbers. Is this possible via HQL?

I tried things like

from Paperboy as paperboy where replace(replace(paperboy._mobile, ' ', ''), '/', '') = :nr or replace(replace(paperboy._phone, ' ', ''), '/', '') = :nr or :nr in elements(replace(replace(paperboy._additionalPhoneNumbers, ' ', ''), '/', ''))

but this throws an exception. Mapping of Paperboy is like this:

<class name="Paperboy"
     table="tblPaperboy"
     lazy="false">
   <id name="_key"
    column="Id"
    access="field" >
      <generator class="assigned"/>
   </id>
   <property name ="_businessId" column="BusinessId" access="field" />
   <many-to-one name="_agency" class="Agency" access="field" column="AgencyKey" />
   <property name ="_lastname" column="Lastname" access ="field" />
   <property name ="_firstname" column="Firstname" access ="field" />
   <property name ="_phone" column="Phone" access ="field" />
   <property name ="_mobile" column="Mobile" access ="field" />

   <bag name="_additionalPhoneNumbers"
     access="field"
     fetch="subselect"
     lazy="false"
     table="tblPaperboyAdditionalPhoneNumbers">
     <key column="PaperboyId"/>
     <element column="PhoneNumber"
           type="string"/>
   </bag>
</class>

回答1:

this might work

select
  paperboy
from
  Paperboy as paperboy left join paperboy._additionalPhoneNumbers number
where
  replace(replace(paperboy._mobile, ' ', ''), '/', '') = :nr or
  replace(replace(paperboy._phone, ' ', ''), '/', '') = :nr or
  replace(replace(number, ' ', ''), '/', '') = :nr