I have a data model which has three tables: STORE, REGISTER, and PURCHASE. A store has many registers and a register has many purchases. In my object model I need to create a collection of the 100 most recent purchases on the Store class. I figured using a set and the loader/sql-query elements would do the trick, but there's not a whole lot of documentation on how to use these elements. Here's what I've got so far:
Code:
<class name="Store" table="STORE">
<id name="id" column="STORE_ID" type="integer"/>
<set name="recentPurchases">
<key column="STORE_ID"/>
<one-to-many class="Purchase"/>
<loader query-ref="recentPurchasesQuery"/>
</set>
</class>
<sql-query name="recentPurchasesQuery">
<load-collection alias="p" role="Store.recentPurchases"/>
select {p.*}
from register r, purchase p
where r.store_id = :id
and r.register_id = p.register_id
order by p.purchase_datetime desc
limit 100
</sql-query>
This results in the following generated SQL:
Code:
select p.STORE_ID as STORE6___,
p.PURCHASE_ID as PURCHASE1___,
p.PURCHASE_ID as PURCHASE1_0_,
p.REGISTER_ID as REGISTER2_7_0_,
p.EMPLOYEE_ID as EMPLOYEE3_7_0_,
p.CUSTOMER_ID as CUSTOMER4_7_0_,
p.PURCHASE_DATETIME as PURCHASE5_7_0_,
('Purchase ' || p.PURCHASE_ID) as formula3_0_
from register r,
purchase p
where r.store_id = ? and
r.register_id = p.register_id
order by p.purchase_datetime desc
limit 100
The first column in the select is problematic because it doesn't exist on the PURCHASE table (but it is on the REGISTER table). Also, I don't understand the purpose/mechanics of the {p.*} construct. Furthermore, am I supposed to retrieve all the columns that are needed to construct the instance, or can I simply retrieve the primary keys and use that to drive the instantiation of the purchase objects?
Any help, pointers to documentation, etc, is greatly appreciated!
Thanks,
-Kaare
Login
FAQ
Search
