Let's consider an object :

ObjectA {

Long uid;

ALogicalKey key;
ALevel level;

ObjectA parent;

AnyType attribute1;
AnyType attribute2;
[...]

}

I can have several instances of ObjectA, linked each through the parent attribute. That's what I call my "cascading structure".

Level is a value belonging to an ordered list. Let's imagine this suite :
"VALUE1" -> "VALUE2" -> "VALUE3" -> "VALUE4"

Key is a logical identifier, with this constraint : a key-level pair is unique.

Let's consider my need now :
I want to be able to select every objects having 'key' equals to a particular value (let's say "abc" ) and 'level' equals to a value of the suite or if not found a value "lower" than the value desired (let's say "VALUE3" as the desired value).
I mean, if there is no object for this particular pair ("abc" && "VALUE3"), just consider the next possible option : "abc" && "VALUE2", then "abc" && "VALUE1" until there is one (or not) object matching.

My main question is : what kind of query can make this possible ?
My second question is : is that clear ? ;-)

Here is my first solution, after a few hours thinking about the best way to solve it (I'm not saying this is THE best way, but it works, at least :) ) :

level is an enum
each value is defined in the right order in the Level enum class : VALUE1 first, then VALUE2, and so on.

Then I use this kind of query for a "findAllForLevel" need :

from ObjectA o where o.level = (select max(level) from ObjectA o2 where o2.key = o.key and o2.level <= :level)

And this one if I want the first "node" matching my need for a particular key :

from ObjectA o where o.key = :key and o.level = (select max(o2.level) from ObjectA o2 where o2.key = o.key and o2.level <= :level)

Since enums can be compared to each other depending on their ordinal position, it works.

From the Java doc :


Any feedback regarding this approach ?

FYI...

What you call a "cascading structure" is generally called a self referential association or hierarchical association in the data world. Those phrases will help you track down good information. Unfortunately ANSI SQL has never really properly addressed querying such relationships, but various vendors have (Oracle's CONNECT BY, etc).

Though enums can be compared and have a natural order in java, It won't be useful. You should be able to compare them in sql.(like number, date..)

If you can compare them in sql, your solution will work.

Hi delirii
I don't see any reason why you should be using correlated subquery when it can be solved with a subquery itself.


select * from entity x where
x.key= 'supplied.entity.key'
and x.level = (select max(y.level) from entity y where y.key='supplied.entity.key' and y.level < 'supplied.entity.value');

I hope the above query should address your need.