That was the correct combination: - the groupBy must be on the parent resultMap - the value of the groupBy attribute must be set to the value of the property on which the grouping occurs
Thank you, Paul and Karen! Marty -----Original Message----- From: Paul Barry [mailto:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 3:40 PM To: [email protected] Subject: Re: Questions on N+1 and ParameterMap Importance: Low Karen is correct, once I changed my groupBy from "author.id" to "id", it works as expected. Marty, do you have groupBy="id" or groupBy="alert_id" on the findAlertsResult resultMap? I think it should be groupBy="id". Karen Koch wrote: > Is your groupBy referring to the parent Java object's property name > rather than the database column name? I see that Paul's example is > using the database column name, which is not right -- and perhaps, > rather than raising an error, the grouping simply does not happen in that > case. > > > --- Marty Tomasi <[EMAIL PROTECTED]> wrote: > > >>Just to follow up, Paul's suggestion to move the "groupBy" from the >>criteria resultMap did correct the problem I had with the null values >>showing up on the List of AlertCriteria. The groupBy needs to be on the >>parent resultMap. >>My results now match what Paul outlines more succinctly below. >> >>That is, my case still returns more objects than expected. >> >>Still puzzled as to what to try next, >> >>Marty >> >>P.S. Thanks, Paul. >> >>-----Original Message----- >>From: Paul Barry [mailto:[EMAIL PROTECTED] >>Sent: Thursday, March 10, 2005 11:19 AM >>To: [email protected] >>Subject: Re: Questions on N+1 and ParameterMap >>Importance: Low >> >>I tried to make a simple example myself using the new groupBy feature >>and I think I am having a problem similar to Marty's. I created a >>simple "author has many books" example. I have an Author Object like this: >> >>public class Author { >> >> private Long id; >> private String name; >> private List<Book> books; >> >>} >> >>With getters and setters. Then I have a Book Object like this: >> >>public class Book { >> >> private Long id; >> private String title; >> private Author author; >> >>} >> >>Then I have authors and books database tables: >> >>mysql> select * from authors; >>+----+---------------------+ >>| id | name | >>+----+---------------------+ >>| 1 | Charles Dickens | >>| 2 | Ernest Hemmingway | >>| 3 | William Shakespeare | >>+----+---------------------+ >>3 rows in set (0.00 sec) >> >>mysql> select * from books; >>+----+----------+---------------------------+ >>| id | authorId | title | >>+----+----------+---------------------------+ >>| 1 | 1 | Tale of Two Cites | >>| 2 | 1 | Oliver Twist | >>| 3 | 1 | Great Expectations | >>| 4 | 2 | For Whom the Bell Tolls | >>| 5 | 2 | Sun Also Rises | >>| 6 | 2 | The Old Man and The Sea | >>| 7 | 3 | Hamlet | >>| 8 | 3 | Othello | >>| 9 | 3 | A Midsummer Night's Dream | >>+----+----------+---------------------------+ >>9 rows in set (0.00 sec) >> >>Then I have this sqlmap: >> >><sqlMap> >> >> <resultMap id="author" class="author" groupBy="author.id"> >> <result property="id" column="author.id"/> >> <result property="name" column="author.name"/> >> <result property="books" resultMap="book"/> >> </resultMap> >> >> <resultMap id="book" class="book"> >> <result property="id" column="book.id"/> >> <result property="title" column="book.title"/> >> </resultMap> >> >> <select id="getAuthors" resultMap="author"> >> SELECT >> a.id "author.id", >> a.name "author.name", >> b.id "book.id", >> b.title "book.title" >> FROM >> authors a, >> books b >> WHERE >> a.id = b.authorId >> </select> >> >></sqlMap> >> >>Here is what the query results look like: >> >>mysql> SELECT >> -> a.id "author.id", >> -> a.name "author.name", >> -> b.id "book.id", >> -> b.title "book.title" >> -> FROM >> -> authors a, >> -> books b >> -> WHERE >> -> a.id = b.authorId; >>+-----------+---------------------+---------+--------------------------+ >>| author.id | author.name | book.id | book.title | >>+-----------+---------------------+---------+--------------------------+ >>| 1 | Charles Dickens | 1 | Tale of Two Cites | >>| 1 | Charles Dickens | 2 | Oliver Twist | >>| 1 | Charles Dickens | 3 | Great Expectations | >>| 2 | Ernest Hemmingway | 4 | For Whom the Bell Tolls | >>| 2 | Ernest Hemmingway | 5 | Sun Also Rises | >>| 2 | Ernest Hemmingway | 6 | The Old Man and The Sea | >>| 3 | William Shakespeare | 7 | Hamlet | >>| 3 | William Shakespeare | 8 | Othello | >>| 3 | William Shakespeare | 9 | A Midsummer Night's Dream| >>+-----------+---------------------+---------+--------------------------+ >>9 rows in set (0.00 sec) >> >>So when I use this in Java code, I should get 3 Author objects, each >>with 3 different Book objects in their books property, right? Well I >>get 9 objects, >>1 book each. When I run this code: >> >>List<Author> authors = sqlMap.queryForList("getAuthors",null); >>System.out.println(authors.size()); >>for(Author author: authors) { >> System.out.println(author.getName()); >> for(Book book: author.getBooks()) { >> System.out.println(" "+book.getTitle()); >> } >>} >> >>This is the output: >>9 >>Charles Dickens >> Tale of Two Cites >>Charles Dickens >> Oliver Twist >>Charles Dickens >> Great Expectations >>Ernest Hemmingway >> For Whom the Bell Tolls >>Ernest Hemmingway >> Sun Also Rises >>Ernest Hemmingway >> The Old Man and The Sea >>William Shakespeare >> Hamlet >>William Shakespeare >> Othello >>William Shakespeare >> A Midsummer Night's Dream >> >>I expected: >>3 >>Charles Dickens >> Tale of Two Cites >> Oliver Twist >> Great Expectations >>Ernest Hemmingway >> For Whom the Bell Tolls >> Sun Also Rises >> The Old Man and The Sea >>William Shakespeare >> Hamlet >> Othello >> A Midsummer Night's Dream >> >>What am I missing? >> >>Marty Tomasi wrote: >> >>>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] >>> >> >
