hi Marty, Your query is not in the right context and is doing what you ask. You are doing a join and as you have to records in the sub table that meet the requirments of your join ie WHERE A.ALERT_ID=AC.ALERT_ID then two rows are returned. This is only good for N:1 situations. You have a N:M situation. Easiest solution do a follow up query see page 26 0f 53 in ibatis-sqlmap.pdf file. ie.
<result property="criteriaList" select="getMyAlertCriteia"/> This will envolve a second hit on your database. other option use a lazy loading technique to only get the data when you need it. Good luck steve.. -----Original Message----- From: Marty Tomasi [mailto:[EMAIL PROTECTED] Sent: Thu 3/10/2005 1:20 AM To: [email protected] Subject: Questions on N+1 and ParameterMap Hi, First off, excellent work to date on iBATIS. It's been a real treat to work with so far. I could go on, but.... I have some questions. Note that I am using the latest 2.0.9B code when running this code. Given two tables with the following: Table: ALERTS +----------+----------------+------------+--------------+ | alert_id | event_name | subscriber | active_state | +----------+----------------+------------+--------------+ | 1001 | ContentAdded | demouser | 1 | | 1002 | ContentUpdated | demouser | 1 | +----------+----------------+------------+--------------+ Table: ALERT_CRITERIA +----------+-----------+----------+---------------+------+ | alert_id | attribute | operator | value | type | +----------+-----------+----------+---------------+------+ | 1001 | Document | 4 | D003EFF4C0099 | 12 | | 1002 | Document | 4 | C900EF808099C | 12 | | 1002 | Status | 4 | 200 | 8 | +----------+-----------+----------+---------------+------+ There are two Java beans, Alert and AlertCriteria with the appropriate member fields. Pretty standard stuff, other than the fact that Alert also has a List of AlertCriteria objects; hence the 1:N relationship. Here is the SQL Mapping: <sqlMap namespace="Alerts"> <typeAlias alias="alert" type="com.myorg.alerts.Alert"/> <typeAlias alias="alertCondition" type="com.myorg.alerts.AlertCriteria"/> <typeAlias alias="alertParam" type="com.myorg.alerts.AlertParameterMap"/> <resultMap id="criteriaResult" class="alertCriteria" groupBy="alertId"> <result property="alertId" column="ALERT_ID"/> <result property="attribute" column="ATTRIBUTE"/> <result property="operator" column="OPERATOR"/> <result property="value" column="VALUE"/> <result property="type" column="TYPE"/> </resultMap> <resultMap id="findAlertsResult" class="alert"> <result property="id" column="ALERT_ID"/> <result property="eventName" column="EVENT_NAME"/> <result property="subscriber" column="SUBSCRIBER"/> <result property="active" column="ACTIVE_STATE"/> <result property="criteriaList" resultMap="Alerts.criteriaResult"/> </resultMap> <parameterMap id="alertParamMap" class="alertParam"> <parameter property="eventName"/> <parameter property="attributes"/> </parameterMap> <select id="findActiveAlerts" parameterMap="alertParamMap" resultMap="Alerts.findAlertsResult"> SELECT A.*, AC.* FROM ALERTS A, ALERT_CRITERIA AC WHERE A.ALERT_ID=AC.ALERT_ID AND A.EVENT_NAME=#eventName# AND A.ACTIVE_STATE=1 <iterate prepend="AND" property="attributes" open="(" close=")" conjunction="OR"> AC.ATTRIBUTE=#attributes[]# </iterate> </select> . . . </sqlMap> The AlertParameterMap is a parameter mapping used so that 1 to n "attributes" can be passed in to the "findActiveAlerts" query. Its attributes property is a List, which is then used in the <iterate> element of the query. (Nice feature, btw.) Here's what happens. Consider a query where the AlertParameterMap contains a single attribute (attributes.size() = 1) and the eventName="ContentAdded". When I execute the queryForList() with that parameter map, I get back a single Alert. Makes sense because the SQL returns: +----------+--------------+------------+--------------+----------+-----------+----------+---------------+------+ | ALERT_ID | EVENT_NAME | SUBSCRIBER | ACTIVE_STATE | ALERT_ID | ATTRIBUTE | OPERATOR | VALUE | TYPE | +----------+--------------+------------+--------------+----------+-----------+----------+---------------+------+ | 1001 | ContentAdded | demouser | 1 | 1001 | Document | 4 | D003EFF4C0099 | 12 | +----------+--------------+------------+--------------+----------+-----------+----------+---------------+------+ The List returned by queryForList() contains a single Alert object, as expected. The List of AlertCriteria on the Alert object has a single object as well. However, when I try a query where the AlertParameterMap contains multiple attributes (attributes.size() > 1) using, for example, eventName="ContentUpdated", the SQL returns: +----------+----------------+------------+--------------+----------+-----------+----------+---------------+------+ | ALERT_ID | EVENT_NAME | SUBSCRIBER | ACTIVE_STATE | ALERT_ID | ATTRIBUTE | OPERATOR | VALUE | TYPE | +----------+----------------+------------+--------------+----------+-----------+----------+---------------+------+ | 1002 | ContentUpdated | demouser | 1 | 1002 | Document | 4 | C900EF808099C | 12 | | 1002 | ContentUpdated | demouser | 1 | 1002 | Status | 4 | 200 | 8 | +----------+----------------+------------+--------------+----------+-----------+----------+---------------+------+ The List returned by queryForList() contains two (2) Alert objects instead of the expected one (based on the id 1002 in this example). Further, the first Alert object has a List of AlertCriteria with a single AlertCriteria (where attribute="Document", etc.). The second Alert object has a null value for the AlertCriteria. The expected result is a single Alert object whose criteriaList is size()=2 with both criteria (attribute="Document" and attribute="Status") present. Any thoughts or suggestions as to what is incorrect? Is the parameter map not working as I expect? Are my expectations way off base? Any insight would be greatly appreciated. Thanks, Marty ------------------- Marty Tomasi [EMAIL PROTECTED] This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.
<<winmail.dat>>
