Hi all,
I have a simple graph structure like this (in MySQL):
Code:
create table Node(
id int not null auto_increment,
name varchar(255),
primary key(id)
) engine=InnoDB;
create table Edge(
source int not null,
target int not null,
cost int,
primary key(source, target),
foreign key (source) references Node(id),
foreign key (target) references Node(id)
) engine=InnoDB;
(Entities are trivially mapped)
Then I wish to find in the graph all edges that point to a specific node and have no inverse edges (e.g. there are edges 1->2, 2->1 and 3->1; then when consulting node 1, the query finds edge 3->1 since there's no 1->3).
In SQL:
Code:
select source, target, cost
from Edge
where target = 1
and (source, target)
not in
(select target, source from Edge)
According to the Hibernate documentation (section 14.13), matching tuples with subqueries should work:
Code:
from Cat as cat
where not ( cat.name, cat.color ) in (
select cat.name, cat.color from DomesticCat cat
)
I tried issuing the following EJBQL query:
Code:
select e
from Edge e
where e.target.name = #{blah}
and not (e.target, e.source) in
(select e.source, e.target from Edge e)
But it translated to:
Code:
select (...)
from Edge edge0_, Node node1_
where edge0_.target=node1_.id
and node1_.name = (...)
and ((edge0_.target, edge0_.source)
not in
(select edge2_.source, node1_.id as id8_1_ <-- ???????
from Edge edge2_, Node node3_
where edge2_.source=node3_.id))
Which, of course, doesn't not work as intended.
I still haven't looked at the source to try figuring out why the subquery is translated that way. Any hint as to if the problem is in my query, mapping, entities or even Hibernate itself ?
PS: I'm using Hibernate 3.2.4.SP1 and Hibernate EntityManager 3.3.1.GA, the versions that come with JBoss Seam 2.1.0.SP1
PS2: No, I'm not trying to solve combinatorial optimization problems with a relational database, this is just an example ;-)
Thanks in advance.
Login
FAQ
Search