Hibernate version: 3.2.6
Name and version of the database you are using: MSSQL 2000
Hi everyone,
I have a problem with a SQL query that itself is rather simple, but I can't translate it to HQL.
The data-model is as follows: I have the entities F, P, A and FA.
F:P is a one-to-many-relationship, P:A is a one-to-one relationship (optional) and A:FA is a many-to-many-relationship (using a join table named A_FA).
The goal is to get all Fs (meeting certain criteria) and all associated P that have an A that has some certain FAs associated. If no such P to a given F exists, leave it out in the result (nullify it), but gimme that F!
So my SQL looks like this:
Code:
select F.id, F.name, P.someThing
from F
left join P
inner join A
on P.id = A.p_id
and exists (
select *
from A_FA
inner join A on A_FA.a_id = a.id
where A_FA.fa_id in (183, 199) and A.p_id = P.id
)
on P.f_id = f.id
where F.id in (...)
It is basically a left join into an inner join using a subselect.
I simlpy can't get it to work with hibernate.
The mapped classes are:
Code:
class F {
@OneToMany(mappedBy = "f")
public Set<P> getPs() {...}
}
class P {
@ManyToOne
@JoinColumn(name = "f_id")
public F getF() {...}
@OneToOne(mappedBy="p", optional=true)
public A getA() {...}
}
class A {
@OneToOne(optional=false)
@JoinColumn(name="p_id", unique=true)
public P getP() {...}
@ManyToMany
@JoinTable(name="A_FA",
joinColumns={@JoinColumn(name="a_id")},
inverseJoinColumns {@JoinColumn(name="fa_id")})
public Set<FA> getFAs() {...}
}
class FA {
// no associations here, just a few data fields
}
My first HQL-approaches either returned too many rows (the outer join was a little bit too outer) or too few (some inner join condition reduced the number of rows).
Basically the problem seems to be the outer join into an inner join. I transformed the SQL to use a subquery in the from-clause (select ... from F left join (select .. from P inner join ...) wich works of course, but is as in-translatable to HQL as far as I know.
I tried to use the with-keyword in my HQL queries - nothing worked. e.g.
Quote:
org.hibernate.hql.ast.InvalidWithClauseException: with clause can only reference columns in the driving table [select f.id, f, p from a.b.c.F f left join f.ps p with (p in (select a.p from a.b.c.A a inner join a.fas fa where fa.id in (:attribute))) where f.id in (:ids)]
I searched the forums and found another post that contained a link to a bug report regarding the with-clause
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2772 that suggests another syntax (in my case that would mean to use p.id instead of p), but, you might have guessed, that doesn't work either:
Quote:
org.hibernate.hql.ast.QuerySyntaxException: with-clause referenced two different from-clause elements [select ... with (p.id in (select a.p.id from ...]
I've no idea what to do about this :( Any help is appreciated! (Side-note: Hibernate is used as a JPA provider).
Thanks
-markus
Login
FAQ
Search
