Seems ok to me (but I'm not an expert). The only thing I might say is that if "remoteAddress" and "userAgent" do not have a 1-to-many relationship, you might as well do a single join (which it seems you want to avoid if you've got 30 tables to join), otherwise, your 1 query turns into 30+ queries (times the number of rows returned in the original query). I know you want to avoid it, but there may be no simple solution to that, regardless of the framework you use. One "drawback" to a highly normalized database is a large number of joins. You could always use the "dynamic" clause in your SQL to dynamically apply joins as necessary (so it's not 30 joins ALWAYS, but a limited amount based upon criteria supplied in the parameter map).
-Ryan On Fri, Mar 14, 2008 at 6:11 AM, Eric Andres <[EMAIL PROTECTED]> wrote: > Thank you for the hint, Ryan. > > I tried using the subselect, and it works. However, the way I made it work > seems awkward, I think I did not fully understand your proposal. What I > managed to do is this: > > I made a result map for the base class: > > <resultMap id="eventsMap" class="EventBaseClass"> > ..... > <discriminator javaType="string" column="type" jdbcType="VARCHAR"> > <subMap value="UserLoggedIn" > resultMap="userLoggedInMap"></subMap> > </discriminator> > </resultMap> > > and here's the submap for a concrete event type: > > <resultMap id="userLoggedInMap" class="UserLoggedInEvent" > extends="eventsMap"> > <result property="remoteAdress" select="fetchRemoteAddr" > column="eventid"/> > <result property="userAgent" select="fetchUserAgent" > column="eventId"/> > </resultMap> > > The select queries are all analogous, they look like this: > > <select id="fetchRemoteAddr" parameterClass="int" resultClass="string"> > select ip from eventuserloggedin where eventid=#value# > </select> > > What I would like to do is, like you described, to have the subselect add > the parameters to the result map in one shot... Am I on the right track or > did I miss it completely? Sorry for being slow off the mark... > > On Mar 13, 2008, at 7:50 PM, Ryan Shelley wrote: > > Could you use a nested select? Check out page 34, under "Complex > Properties": > http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf > > The idea is that one ResultMap contains a complex type that consists of > the results from another select. So if tableA has a primary key, and tableB > has a foreign key to the primary key in tableA, you'd have a ResultMap of > tableA results, and in each result from tableA exists a List (or Object) of > ResultMap(s) that match the foreign key in tableB. One iBATIS query would > execute the primary select to gather tableA results, and then automatically, > a separate select will execute to gather the results for each tableA result > and insert them into tableA's ResultMap. > > I might be incorrect, but I think the docs might be wrong. It says: > <result property="category" column="PRD_CAT_ID" select="getCategory"/> > and it should be: > <result property="category" column="PRD_ID" select="getCategory"/> > because you want to map PRD_ID from PRODUCT into the getCategory select. > > -Ryan > > On Thu, Mar 13, 2008 at 2:54 AM, Eric Andres <[EMAIL PROTECTED]> > wrote: > > > Hello, > > > > I have a problem writing a polymorphic query. As I am new to iBatis, I > > started digging the list archive and stumbled upon the discriminator > > and submap tags, and I also found this in the archives: > > http://www.mail-archive.com/[email protected]/msg00070.html > > , my situation is pretty close to Niels', but there was no solution in > > that thread... > > The situation I'm in is this: I have a type hierarchy representing > > events consisting of a base class (call it Event) and a subclass for > > each event type (30+ currently). It's representation in the database > > was done with a table-per-subclass strategy. > > > > I want to write a query that fetches an event based on it's ID. The > > problem is that I don't know the event type I'm going to fetch in > > advance, and I want to avoid a 30-table join. Is there a way a > > delaying the join to a second query after the 'discrimination'? > > Something like > > > > <resultMap id="Event" class="BaseEvent"> > > ... > > <result property="ts" column="timestamp/> > > <discriminator javaType="string" column="type"> > > <subMap value="userLoggedIn" > > select="addUserLoggedInProps"/> > > ... > > </discriminator> > > </resultMap> > > > > ... > > > > <select id="addUserLoggedInProps" parameterClass="int" > > resultMap="userLoggedInEvent"> > > select * from event, userloggedinevent > > where event.id=userloggedin.eventid and event.id=#value# > > </select> > > > > ... > > <resultMap id="userLoggedInEvent" extends="Event"> > > ... > > <result property="userId" column="userid"/> <!-- specific to > > userLoggedInEvent --> > > ... > > </resultMap> > > > > > > Sorry for the lengthy post. Any help greatly appreciated... > > > > Thanks for your time, > > > > Eric > > > > > >
