Problem with formula definition in property

lewisf · **Joined:** Sun Jan 09, 2005 4:09 pm **Posts:** 1

Hibernate version:
2.1.7c

Mapping documents:
<many-to-one name="companyId" class="net.thebluepages.db.Company" />
<property name="dateClicked" type="date"/>
<property name="billed"
type="boolean"
formula="(select iv.id IS NOT NULL from clix cx, invoice iv where cx.dateclicked >= iv.startdate and cx.dateclicked <= iv.enddate and cx.companyid = iv.companyid
)"
/>

I am trying to created a class witha derived property "billed" that indicates whether a particular object has been invoiced. Since Hibernate does not support bulk updates of tables and as the data is in fact derived from the existance of a row in the invoice table I want to use a formula property to determine if the appropriate invoice exists. However the query in the formula above quite correctly returns more than one row which causes the outer Hibernate query to fail. Is there any way in the formula to explicitly refer to an indiviedual database row, e.g. by reference it to the value of the id? What I have in mind is being able to add something like "and cx.id = %id%" to the query where %id% would be translated to the value of theid field of the current row.

All help would be greatly appreciated.

regards

Lewis

drj · **Posted:** Sun Jan 09, 2005 5:23 pm

Try (I suspect it will work)

Code:

formula="
    (select count(*) from clix cx, invoice iv 
     where cx.dateclicked >= iv.startdate 
     and cx.dateclicked <= iv.enddate 
     and cx.companyid = iv.companyid
     and cx.companyid = COMPANY_ID
    )" 

Note that
- in this case the expression will return a number, and not a boolean.
- I assume that you have a column named COMPANY_ID in the company table.

You can convert this to a boolean in a similar fashion using a WHERE EXISTS clause (if your database supports it). This will certainly be more efficient.