Hello all.
How can I write a query to restrict a child based on its distant anscestor?
In my case, I have an object, you could call the grandfather or great grandfather. There are n levels of hierarchy between them.

I need a query that can find all children with a specific name that have a specific ancestor.

Here’s my structure;
ObjectA.name (our anscestor)
…
Hierarchy(grandfather)
Hierarchy(parent)
Child.name


For an easier to explain example, pretend I was mapping every computer, and it’s filesystem into a database.
I would have a structure like:
Computer(Windows 2003 Server, Boston office, name=”foo”, etc)
Folder (C:)
Folder(C:\httpdocs\)
Folder(C:\httpdocs\WEB-INF\)
File(C:\httpdocs\WEB-INF\web.xml)

I would want to write a query that found all files named “web.xml” in computers that has the name of “foo”. There are a variable number of folders between foo and any given web.xml.

I have a table representing computers. I have a join table linking root (drive) folders to computers. Each folder has a folder_ID (parent_ID), except for drive roots which have a folder_ID of null. Each file has a folder_id.

I only need the file objects. I’m not interested in the folders or computers.

I’d like to either accomplish this with one query or have hibernate retrieve a computer based on its name with one step and then filter the query to retrieve all files of a specific name with a second step.

Does anyone know how to write the query? What sections of the documentation would cover this situation?

Thanks in advance,
Steven

select file.name from Computer c, Folder fo, File f where f.folder=fo and fo.computer=c and f.name like "%Web.XML" and c.name like "%foo%"

f.folder=fo and fo.computer=c is for joining tables