Recursive Query using HQL

Question

Hi every one, I have this Table

CREATE TABLE IF NOT EXISTS `branch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `studcount` int(11) DEFAULT NULL,
  `username` varchar(64) NOT NULL,
  `branch_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKADAF25A2A445F1AF` (`branch_fk`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;
ALTER TABLE `branch`
  ADD CONSTRAINT `FKADAF25A24CEE7BFF` FOREIGN KEY (`login_fk`) REFERENCES `login` (`id`);

as you can see each table has a foreign key that point to other Branch Row (self Relation) I want a Query using HQL(prefer HQL) to get a username (or id) from me and return a List<String> (for username) or List<Integer> (for id) that was a list of all of my subBranch;

let me show in Example

id         studentcount            username            branch_fk
1          312                     user01                NULL
2          111                     user02                1
3          432                     user03                1
4          543                     user04                2
5          433                     user05                3
6          312                     user06                5
7          312                     user06                2
8          312                     user06                7

when I call GetSubBranch(3) I want return:

5, 6

and when call GetSubBranch(2) I want return:

4, 7, 8

Answer 1

I believe there is no portable SQL to do this. Even more, I think several major databases' SQL cannot express this.

Therefore, this capability is not part of what you can do in HQL. Sorry :-(

I read a few ways to go. Most of them involve tradeoffs depending of the number of levels (fixed in advance ? how many ?) , the number of records (hundreds ? millions ?) etc :

1. Do the recursive queries yourself, leveling down each time (with a in(ids)), until some level is empty.
2. Do a query with a fixed number of left joins (your depth need to be known in advance ; or you might need to repeat the query to find the rest of the records if needed, see point 1).
3. Have the denormalized information available somewhere : it could be a denormalized table copying of the indexes. But I would prefer a cached in-memory copy, that may be filled completely in only one request, and be updated or invalidated ... depending on your other requisites, like the table size, max depth, write-frequency etc).