Is this kind of query possible with NEO?

[Jay Herrick]

I have some tables that I use to track an objects status and the history
of that status.  Something like this:

    <table name="Thing">
        <column name="ThingId" type="INTEGER" required="true"
primaryKey="true"/>
        <column name="SomeData" type="VARCHAR" size="50" required="true" />
    </table>
   
    <table name="ThingStatus">
        <column name="ThingStatusId" type="INTEGER" required="true"
primaryKey="true"/>
        <column name="Name" type="VARCHAR" size="50" required="true"/>
        <column name="IsDefault" type="BIT" required="true" default="0"/>
        <column name="Description" type="VARCHAR" size="500"
required="true"/>
    </table>
   
    <table name="ThingStatusHistory">
        <column name="ThingStatusHistoryId" type="INTEGER"
required="true" primaryKey="true"/>
        <column name="ThingId" type="INTEGER" required="true"/>
        <column name="ThingStatusId" type="INTEGER" required="true"/>
        <column name="Date" type="DATETIME" required="true"/>
        <column name="Notes" type="VARCHAR" size="500" required="false"/>
        <foreign-key foreignTable="Thing" name="Thing">
            <reference foreign="ThingId" local="ThingId"/>
        </foreign-key>
        <foreign-key foreignTable="ThingStatus">
            <reference foreign="ThingStatusId" local="ThingStatusId"/>
        </foreign-key>
    </table>


So the ThingStatus table holds all of the available statuses and the
Thing table holds the important data about an object and the
ThingStatusHistory maps a ThingStatus to a Thing with the newest record
being the current status.  Make sense so far?  In plain old SQL I would
do something like this to get the currect status:

select *
    from ThingStatus
    where ThingStatusId =
    (Select ThingStatusId
        from ThingStatusHistory
        where ThingId = 16
        and [Date] =
        (select max([Date])
            from ThingStatusHistory
            where ThingId = 16))


So far nothing I can't do with NEO and putting the max date logic in the
business objects.  My problem stems from wanting to do queries that use
the current status as a qualifier.  In plain old SQL I would simply nest
the previous query in a more complicated query to get the desired
results.  The only way I can find to do it in NEO is to load all the
data and post process it or do a per record query (read SLOW).  Any
suggestions on a more "correct" way to do this, or are there some
qualifier tricks I can use to improve my search performance?

Thanks,

-Jay  


---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email