ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Laccetti [c]" <mich...@s2g-limited.com>
Subject Nested Lists [signed]
Date Thu, 17 Nov 2005 03:56:04 GMT
I've got a bit of a tricky question.  I'm trying to retrieve a list of
objects from the database.  That's the easy part.  The hard part is that two
properties in the object are also lists.  So, when I call the list query, it
in turn will call two sub-queries to populate the properties.  I've read
about doing it, and thought that I knew what to do, but I cannot make it
work.  The other problem is that no actual exceptions get thrown.  If I
wasn't debugging the app I would have never even noticed it (aside from a
lack of objects in the lists).

Here are links to two screenshots that will show you the exceptions:
(Note: they're somewhat large, I apologize in advance.)

http://images.nihilist.ca/gallery/main.php?g2_view=core.ShowItem&g2_itemId=6
6&g2_imageViewsIndex=2&g2_navId=xb4c396b6
http://images.nihilist.ca/gallery/main.php?g2_view=core.ShowItem&g2_itemId=7
0&g2_imageViewsIndex=2&g2_navId=xb4c396b6


Here is the actual configuration for everything.  The query starts at the
bottom, the statement with id getChannelInformation.  That then calls
getLeaguesForGame and getLocationsForGame.  It is also attempting to pass in
two properties, though I do not know if I've got it right, or got it at all.
I'm trying to pass in the channel ID, but that's from the first query, and I
do not know if it propagates properly.

<resultMap id="get-locations-for-game" class="java.lang.String">
      <result property="value" column="flag_location" />
   </resultMap>

   <statement id="getLocationsForGame" resultMap="get-locations-for-game">
      SELECT
         flag_location
      FROM
         scrimbot_location
      WHERE
         id IN (
         SELECT
            scrimbot_channel_games.scrim_location_id
         FROM
            scrimbot_channels
         INNER JOIN
            scrimbot_channel_games ON
(scrimbot_channels.id=scrimbot_channel_games.channel_id)
         WHERE
            scrimbot_channels.channel = '#channelID#'
         AND
            scrimbot_channel_games.game_id=#gameID#
      )
   </statement>

   <resultMap id="get-leagues-for-game" class="java.lang.String">
      <result property="value" column="league" />
   </resultMap>

   <statement id="getLeaguesForGame" resultMap="get-leagues-for-game">
      SELECT
         scrimbot_leagues.league
      FROM
         scrimbot_leagues
      WHERE
         id IN (
         SELECT
            scrimbot_channel_games.league_id
         FROM
            scrimbot_channels
         INNER JOIN
            scrimbot_channel_games ON (scrimbot_channels.id =
scrimbot_channel_games.channel_id)
         WHERE
            scrimbot_channels.channel = '#channelID#'
         AND
            scrimbot_channel_games.game_id=#gameID#
      )
   </statement>

   <resultMap id="get-games-for-channel" class="game">
      <result property="gameID" column="gameID" />
      <result property="gameFlag" column="game"/>
      <result property="gametypeID" column="gametypeID"/>
      <result property="gametypeFlag" column="gametype"/>
      <result property="leagueFlags"
column="{channelID=channelID,gameID=gameID}" select="getLeaguesForGame" />
      <result property="locationFlags"
column="{channelID=channelID,gameID=gameID}" select="getLocationsForGame" />
   </resultMap>

   <statement id="getGamesForChannel" resultMap="get-games-for-channel"
parameterClass="java.lang.String">
      SELECT
         scrimbot_games.id as gameID,
         scrimbot_games.game,
         scrimbot_gametypes.id as gametypeID,
         scrimbot_gametypes.gametype,
         scrimbot_channels.id as channelID
      FROM
         scrimbot_channels
      INNER JOIN
         scrimbot_channel_games ON
(scrimbot_channels.id=scrimbot_channel_games.channel_id)
      INNER JOIN
         scrimbot_games ON
(scrimbot_channel_games.game_id=scrimbot_games.id)
      INNER JOIN
         scrimbot_gametypes ON
(scrimbot_channel_games.gametype_id=scrimbot_gametypes.id)
      WHERE
         scrimbot_channels.channel = #value#
   </statement>

   <resultMap id="get-channel-info" class="channel">
      <result property="channelID" column="id"/>
      <result property="channel" column="channel"/>
      <result property="channelLocation" column="location"/>
      <result property="owner" column="username" />
      <result property="ownerEmail" column="emailaddress" />
      <result property="dateAdded" column="date_added" />
      <result property="games" column="channelID"
select="getGamesForChannel"/>
   </resultMap>

   <statement id="getChannelInformation" resultMap="get-channel-info">
      SELECT
         scrimbot_channels.id,
         scrimbot_channels.channel,
         scrimbot_channels.date_added,
         gg_users.username,
         gg_users.emailaddress,
         scrimbot_location.flag_location as location,
      FROM
         scrimbot_channels
      INNER JOIN
         scrimbot_location ON
(scrimbot_channels.location=scrimbot_location.id)
      INNER JOIN
         gg_users ON (scrimbot_channels.owner=gg_users.userID)
      WHERE
         scrimbot_channels.channel='#value#'
   </statement>

So, if anybody can shed some light on what I'm doing wrong, or not doing at
all, I'd appreciate it.  

Mike



-- 
------------------------ [ SECURITY NOTICE ]
------------------------
To: user-java@ibatis.apache.org.
For your security, michael@s2g-limited.com
digitally signed this message on 17 November 2005 at 03:56:13 UTC.
Verify this digital signature at http://www.ciphire.com/verify.
------------------- [ CIPHIRE DIGITAL SIGNATURE ]
-------------------
Q2lwaGlyZSBTaWcuAjh1c2VyLWphdmFAaWJhdGlzLmFwYWNoZS5vcmcAbWljaGFlbEBzM
mctbGltaXRlZC5jb20AZW1haWwgYm9keQDDDwAAfAB8AAAAAQAAAF3/e0PDDwAAaQMAAg
ACAAIAIIXYnAvwVJnXYkeNqqLiR5ZyHRK7W0tiTAYcV7p1uX8mAQAxTkdmLdtsIbMe4FE
6BpyDx/u9eVN0WyoQPcWCmWDyDzCTaFHUFZU31VMik4qp0s6ynpylm9X6gWnCmCyhTaNw
2wK+U2lnRW5k
--------------------- [ END DIGITAL SIGNATURE ]
---------------------



Mime
View raw message