This is what I want to do. I want to delete all TraitInstances that have a trait within EntityTypeTrait as identify by an entityType.
I have this EQL:
[code]
delete from TraitInstance ti where ti.trait in (select e.trait from EntityTypeTrait e
where e.entityType = :entityType and e.trait = :trait)
[/code]
These are the (I think) relevant details from the classes involved, these are not the complete classes however:
[code]
public class TraitInstance {
@Id
@Column(name = "eis_trait_instance_key")
private Integer traitInstanceKey;
@JoinColumn(name = "eis_trait_key", referencedColumnName = "eis_trait_key")
@ManyToOne
private Trait trait;
}
@Table(name = "eis_entity_type_trait")
public class EntityTypeTrait extends KanaEntity {
@Id
private Integer entityTypeTraitId;
@JoinColumn(name = "eis_entity_type_key", referencedColumnName = "eis_entity_type_key")
@ManyToOne
private EntityType entityType;
@JoinColumn(name = "eis_trait_key", referencedColumnName = "eis_trait_key")
@ManyToOne
private Trait trait;
}
[/code]
First, I want to make sure that the EQL is even valid, it obviously compiles, but I get bad SQL from it.
The SQL I get is:
[code]
DELETE FROM eis_trait_instance
WHERE EXISTS(
SELECT t1.eis_trait_instance_key
FROM eis_trait t0, eis_trait_instance t1
WHERE ( IN (
SELECT t2.*
FROM eis_entity_type_trait t4, eis_entity_type_trait t3, eis_trait t2
WHERE (((t4.eis_entity_type_key = ?)
AND (t4.eis_trait_key = ?))
AND (t2.eis_trait_key = t3.eis_trait_key)))
AND (t0.eis_trait_key = t1.eis_trait_key))
AND t1.eis_trait_instance_key = eis_trait_instance.eis_trait_instance_key)
[/code]
I elided the fields from t2 in the SELECT and replaced it with t2.*, just for brevity, in truth all of the columns were listed.
The problem is the "lone" IN operator after the second WHERE.
This is for Postgres, using latest Glassfish.
[Message sent by forum member 'whartung' (whartung)]
http://forums.java.net/jive/thread.jspa?messageID=286070