Return-Path: Delivered-To: apmail-incubator-ibatis-user-java-archive@www.apache.org Received: (qmail 88291 invoked from network); 10 Mar 2005 15:10:55 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur-2.apache.org with SMTP; 10 Mar 2005 15:10:55 -0000 Received: (qmail 54795 invoked by uid 500); 10 Mar 2005 15:10:53 -0000 Delivered-To: apmail-incubator-ibatis-user-java-archive@incubator.apache.org Received: (qmail 54775 invoked by uid 500); 10 Mar 2005 15:10:53 -0000 Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm Precedence: bulk Reply-To: ibatis-user-java@incubator.apache.org List-Help: List-Unsubscribe: List-Post: List-Id: Delivered-To: mailing list ibatis-user-java@incubator.apache.org Received: (qmail 54762 invoked by uid 99); 10 Mar 2005 15:10:53 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from MX2.NYU.EDU (HELO mx2.nyu.edu) (128.122.109.22) by apache.org (qpsmtpd/0.28) with ESMTP; Thu, 10 Mar 2005 07:10:52 -0800 Received: from [192.168.1.10] (ELVIS.ADMIN.SCPS.NYU.EDU [128.122.40.132]) by mx2.nyu.edu (8.12.10/8.12.10) with ESMTP id j2AFAl3L015468 for ; Thu, 10 Mar 2005 10:10:48 -0500 (EST) Message-ID: <423062FD.9050703@nyu.edu> Date: Thu, 10 Mar 2005 10:08:45 -0500 From: Paul Barry Reply-To: paul.barry@nyu.edu Organization: NYU SCPS OIT User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: ibatis-user-java@incubator.apache.org Subject: Re: Questions on N+1 and ParameterMap References: <6216396AEBE36247BF62B505A88ADA0A1380F1@uk-ex002.groupinfra.com> <42305D14.8010102@nyu.edu> In-Reply-To: <42305D14.8010102@nyu.edu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N Here is an example: http://article.gmane.org/gmane.comp.java.ibatisdb.user/383 From comparing this example to your original problem Marty, looks like you have the groupBy on the wrong resultMap. I think you want this: Paul Barry wrote: > Sounds like as of 2.0.9 there is a way to do what Marty is asking: > > # Solution for N+1 selects for 1:M and M:N. Version 2.0 was always > designed for this, I just didn't have a chance to implement it, until > now. It's made possible by two small additions to the mappings > > * > * > > Is there any documentation or examples about the "groupBy" property? > > > James, Steven wrote: > >> 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. >> >> >> 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:Marty.Tomasi@sas.com] >> Sent: Thu 3/10/2005 1:20 AM >> To: ibatis-user-java@incubator.apache.org >> 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: >> >> >> >> >> > type="com.myorg.alerts.AlertCriteria"/> >> > type="com.myorg.alerts.AlertParameterMap"/> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> . . . >> >> >> 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 >> 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 >> Marty.Tomasi@sas.com >> >> >> >> 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.