Dashboard > iBATIS DataMapper > Home > Frequently Asked Questions > How do I get around the N Plus 1 selects problem
How do I get around the N Plus 1 selects problem
Added by Clinton Begin, last edited by Tim Chen on Jan 02, 2007  (view change)
Labels: 
(None)


Here is one solution.

nplusone.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
    <sqlMap namespace="Calendar">
    
    <resultMap id="quarterMap" class="calendarQuarter" groupBy="quarter">
    <result property="quarter" column="quarter"/>
    <result property="name" column="name"/>
    <result property="description" column="description"/>
    <result property="months" resultMap="Calendar.monthMap"/>
    </resultMap>
    
    <resultMap id="monthMap" class="calendarMonth">
    <result property="name" column="monthName"/>
    <result property="description" column="monthDescription"/>
    <result property="broadcastStartDate" column="broadcastStartDate"/>
    <result property="broadcastEndDate" column="broadcastEndDate"/>
    </resultMap>
    
    <select id="getQuartersForServiceYear" resultMap="quarterMap">
    select distinct
    QuarterNumber as quarter,
    QuarterName as name,
    QuarterDesc as description,
    SeasonYear as year,
    MonthName as monthName,
    MonthDesc as monthDescription,
    min(broadcastDate) as broadcastStartDate,
    max(broadcastDate) as broadcastEndDate
    from BroadcastDate
    where SeasonYear = #year#
    and MonthName is not null
    group by
    QuarterDesc,
    QuarterNumber,
    QuarterName,
    SeasonYear,
    MonthName,
    MonthDesc
    order by broadcastStartDate
    </select>
    
    </sqlMap>

When you call

List myList = executeQueryForList("Calendar.getQuartersForServiceYear", 2005);
the main query is executed, and the results are stored in the myList variable as beans of type "calendarQuarter" (an alias). Each object in that List will have a "months" property that is also a List populated from the same query, but using the "monthMap" result map to populate the beans in the child lists. So, you end up with a list containing sub-lists, and only one database query is executed.

The important items here are the

groupBy
attribute and the
<result property="months" resultMap="Calendar.monthMap"/>
property mapping in the "quarterMap" result map. One other important detail is that the result mapping for the months property is namespace aware - had it been simply "monthMap" it would not work.

Summary: You have a single query that will return results such as

parent1, child1
      parent1, child2
      parent2, child1
      parent3, child1
      parent3, child2
      parent3, child3
      ....

The groupby will take care of figuring out that you really want a list of parent objects with their matching child objects as a list under them.


Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators