groupBy issue

[Chris O'Connell <oc...@gorillachicago.com>]

I'm trying to get the iBatis groupBy to work for a join between two tables
with a parent child relationship.

I  have two tables, KPF_FOLDER_ROOM_TYPES and KPF_FOLDER_ITEMS (this is a
legacy table, so I am not responsible for anything, including names.  Nor
can I change anything).  The FOLDER_ROOM_TYPES table has a FOLDER_ID and a
ROOM_NAME.  The ITEMS table has a FOLDER_ID that corresponds to the
FOLDER_ID in the ROOM_TYPES table and it has a ROOM_TYPE column that
corresponds to the ROOM_NAME in the ROOM_TYPES table.

So, if an item has the same FOLDER_ID and ROOM_TYPE as the room table, then
that item belongs to the room.  I set up a test, and I have two rooms, one
room with one item and one room with two items.  I turned on logging and I
can see that my query returns the three rows that I expect to get back (one
room joined to one item and the other row joined to two items).  However, I
am missing an item from what I get back from iBatis.  It looks like iBatis
is only processing one row for each room.  So, each room has the 'items'
attribute populated, but it is only populated with a single item.  The
second item in room 2 never makes it back from iBatis.

I'm sure I am missing something basic, but I have been staring at this and
my eyes are glazed over.  If someone spots something, I would really
appreciate a point in the right direction.  Code and sql map info follows...


So, the Room class looks like this (I have trimmed the java classes to
remove the extra attribute methods, just for brevity sake):

public class ProjectFolderRoom {

    private String _folderId;
    private String _roomName;
    private BigDecimal _roomOrder;
    private List<ProjectFolderItem> _items;

    public void setItems(List<ProjectFolderItem> items){
        _items = items;
    }

    public List<ProjectFolderItem> getItems(){
        return _items;
    }

    public String getFolderId() {
        return _folderId;
    }

    public void setFolderId(String folderId) {
        _folderId = folderId;
    }

    public String getRoomName() {
        return _roomName;
    }

    public void setRoomName(String roomName) {
        _roomName = roomName;
    }
}

The Item class looks like this:
public class ProjectFolderItem {

    private String _lineItemId;
    private String _description;
    private String _folderId;
    private String _tablePrefix;
    private BigDecimal _itemId;
    private String _itemNumber;
    private String _parentId;
    private BigDecimal _colorItemId;
    private String _colorFinishName;
    private BigDecimal _price;
    private BigDecimal _priceAltCurrency;
    private BigDecimal _rawtotal;
    private BigDecimal _total;
    private BigDecimal _sequenceNum;
    private String _roomType;
    private String _itemType;
    private String _productType;
    private String _accessoryType;
    private String _requiredAccessOnly;
    private String _catalogNum;
    private Long _quantity;

    public String getFolderId() {
        return _folderId;
    }

    public void setFolderId(String folderId) {
        _folderId = folderId;
    }

    public String getRoomType() {
        return _roomType;
    }

    public void setRoomType(String roomType) {
        _roomType = roomType;
    }
}

Here is the select from my sqlMap file:
    <select id="selectRoomsWithItemsByFolderId"
resultMap="projectFolderRoomResultDeluxe">
        SELECT ROOM.FOLDER_ID as ROOM_FOLDER_ID,
            ROOM.ROOM_NAME as ROOM_ROOM_NAME,
            ROOM.ROOM_ORDER as ROOM_ROOM_ORDER,
            ITEMS.LINE_ITEM_ID as KPF_LINE_ITEM_ID,
            ITEMS.DESCRIPTION as KPF_DESCRIPTION,
            ITEMS.FOLDER_ID as KPF_FOLDER_ID,
            ITEMS.TABLE_PREFIX as KPF_TABLE_PREFIX,
            ITEMS.ITEM_ID as KPF_ITEM_ID,
            ITEMS.ITEM_NUMBER as KPF_ITEM_NUMBER,
            ITEMS.PARENT_ID as KPF_PARENT_ID,
            ITEMS.COLOR_ITEM_ID as KPF_COLOR_ITEM_ID,
            ITEMS.COLOR_FINISH_NAME as KPF_COLOR_FINISH_NAME,
            ITEMS.PRICE as KPF_PRICE,
            ITEMS.PRICE_ALT_CURRENCY as KPF_PRICE_ALT_CURRENCY,
            ITEMS.RAWTOTAL as KPF_RAWTOTAL,
            ITEMS.TOTAL as KPF_TOTAL,
            ITEMS.SEQUENCE_NUM as KPF_SEQUENCE_NUM,
            ITEMS.ROOM_TYPE as KPF_ROOM_TYPE,
            ITEMS.ITEM_TYPE as KPF_ITEM_TYPE,
            ITEMS.PRODUCT_TYPE as KPF_PRODUCT_TYPE,
            ITEMS.ACCESSORY_TYPE as KPF_ACCESSORY_TYPE,
            ITEMS.REQUIRED_ACCESS_ONLY as KPF_REQUIRED_ACCESS_ONLY,
            ITEMS.CATALOG_NUM as KPF_CATALOG_NUM,
            ITEMS.QUANTITY as KPF_QUANTITY
        FROM KPF_FOLDER_ROOM_TYPES ROOM, KPF_FOLDER_ITEMS ITEMS
        WHERE ROOM.FOLDER_ID = #folderId#
        AND ROOM.ROOM_NAME = ITEMS.ROOM_TYPE
        AND ROOM.FOLDER_ID = ITEMS.FOLDER_ID
    </select>

And here are the two result maps:
    <resultMap id="projectFolderRoomResultDeluxe"
class="com.gorilla.domain.users.ProjectFolderRoom"
               groupBy="roomName">
        <result property="folderId" column="ROOM_FOLDER_ID"
jdbcType="VARCHAR" />
        <result property="roomName" column="ROOM_ROOM_NAME"
jdbcType="VARCHAR" />
        <result property="roomOrder" column="ROOM_ROOM_ORDER"
jdbcType="DECIMAL" />
        <result property="items"
resultMap="User.projectFolderItemResultDeluxe" />
    </resultMap>
    
    <resultMap id="projectFolderItemResultDeluxe"
class="com.gorilla.domain.users.ProjectFolderItem"
            groupBy="roomType">
        <result column="KPF_LINE_ITEM_ID" property="lineItemId"
jdbcType="VARCHAR" />
        <result column="KPF_DESCRIPTION" property="description"
jdbcType="VARCHAR" />
        <result column="KPF_FOLDER_ID" property="folderId"
jdbcType="VARCHAR" />
        <result column="KPF_TABLE_PREFIX" property="tablePrefix"
jdbcType="VARCHAR" />
        <result column="KPF_ITEM_ID" property="itemId" jdbcType="DECIMAL" />
        <result column="KPF_ITEM_NUMBER" property="itemNumber"
jdbcType="VARCHAR" />
        <result column="KPF_PARENT_ID" property="parentId"
jdbcType="VARCHAR" />
        <result column="KPF_COLOR_ITEM_ID" property="colorItemId"
jdbcType="DECIMAL" />
        <result column="KPF_COLOR_FINISH_NAME" property="colorFinishName"
jdbcType="VARCHAR" />
        <result column="KPF_PRICE" property="price" jdbcType="DECIMAL" />
        <result column="KPF_PRICE_ALT_CURRENCY" property="priceAltCurrency"
jdbcType="DECIMAL" />
        <result column="KPF_RAWTOTAL" property="rawtotal" jdbcType="DECIMAL"
/>
        <result column="KPF_TOTAL" property="total" jdbcType="DECIMAL" />
        <result column="KPF_SEQUENCE_NUM" property="sequenceNum"
jdbcType="DECIMAL" />
        <result column="KPF_ROOM_TYPE" property="roomType"
jdbcType="VARCHAR" />
        <result column="KPF_ITEM_TYPE" property="itemType"
jdbcType="VARCHAR" />
        <result column="KPF_PRODUCT_TYPE" property="productType"
jdbcType="VARCHAR" />
        <result column="KPF_ACCESSORY_TYPE" property="accessoryType"
jdbcType="VARCHAR" />
        <result column="KPF_REQUIRED_ACCESS_ONLY"
property="requiredAccessOnly" jdbcType="VARCHAR" />
        <result column="KPF_CATALOG_NUM" property="catalogNum"
jdbcType="VARCHAR" />
        <result column="KPF_QUANTITY" property="quantity" jdbcType="DECIMAL"
/>
    </resultMap>

RE: groupBy issue

[Chris O'Connell <oc...@gorillachicago.com>]

Sorry for the original long post.  For anyone who is interested, the
solution was that the 'included' result map also had a groupBy.  I didn't
need that.  As soon as I removed the groupBy from the resultMap for the
ITEMS, problem solved.

Chris

-----Original Message-----
From: Chris O'Connell [mailto:oconnell@gorillachicago.com] 
Sent: Thursday, June 12, 2008 10:09 AM
To: user-java@ibatis.apache.org
Subject: groupBy issue

I'm trying to get the iBatis groupBy to work for a join between two tables
with a parent child relationship.

... long boring stuff removed ...