I have a database structure that is unfortunately out of my control. I am trying to write a jpql query to query on a list of values that is using a parent entity but it is not returning any values.
Classes
This class is probably the root of the problem it has a collection of TagUsage that all the children should have as a collection, but the database is creating a separate join table for all inherited types. Is this happening because of TABLE_PER_CLASS?
@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class Organization extends DomainEntity implements Serializable {
@ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST})
private List<TagUsage> tagUsages = new ArrayList<TagUsage>();
public Organization() {
}
...
@Entity
@Table(name = "tag_usage", indexes = {
@Index(columnList = "id"),
@Index(columnList = "name")
})
public class TagUsage extends DomainEntity implements Serializable {
@ManyToOne(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
@JoinColumn(name = "tagbag_id", referencedColumnName = "id")
private TagBag tagBag;
@ManyToMany(mappedBy = "tagUsages")
private List<Organization> organizations;
...
I am trying to run the named query "TagBag.findWithOrganizations" but when I do this doesn't return me any results.
@Entity
@Table(name = "tag_bag", indexes = {
@Index(columnList = "id")})
@NamedQueries({
@NamedQuery(name = "TagBag.findWithOrganizations", query = "SELECT t from TagBag t LEFT JOIN FETCH t.tagUsages u LEFT JOIN FETCH u.organizations o where t.id = :id")})
public class TagBag extends DomainEntity implements Serializable {
@OneToMany(targetEntity = TagUsage.class, mappedBy = "tagBag", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<TagUsage> tagUsages;
There are multiple classes that extend the Organization class. I am trying to figure out if I can even write a query to fetch all inherited types of Organization TagBag.findWithOrganizations.
BTW my issue comes down to this, when the query tries to fetch the Organizations from the TagUsage class it is querying the organization_tag_usage join table and not descending down to all the inherited types to query their own *_tag_usage table.
Also it we are using eclipselink if that makes any difference.
Update
I have diagnosed the issue but am unsure why it is happening. Looking at the sql output I see that when I try to fetch the organizations that are attached to each one of the tag usages it is using the wrong join table for the query.
Here is one sample that occurrs for each of the inheriting objects of Organization. In this case there is a class called OrgSystem which inherits from the Organizaiton class. The database has a join table for tag usages created for all the inheriting tables of Organization along with a join table for the Organization class itself. Whenever a TagUsage is added to a class that inherits from Organization the relationship is represented in that classes join table. For example in this case the OrgSystem has a table called orgsystem_tag_usages. But when jpa (eclipselink) tries to fetch the relationships it is always joining using the organization_tag_usages table.
SELECT t1.id, t1.DESCRIPTION, t1.NAME, t1.VID FROM ORGANIZATION_tag_usage t0, ORGSYSTEM t1 WHERE ((t0.tagUsages_id = ?) AND (t1.id = t0.organizations_id))
bind => [38]
Postgres output for the list of organization tables.
\dt
| public | organization | table | |
| public | organization_tag_usage | table | |
| public | orgcustomer | table | |
| public | orgcustomer_tag_usage | table | |
| public | orgdevice | table | |
| public | orgdevice_tag_usage | table | |
| public | orgmasteragent | table | |
| public | orgmasteragent_tag_usage | table | |
| public | orgsite | table | |
| public | orgsite_tag_usage | table | |
| public | orgsystem | table | |
| public | orgsystem_tag_usage | table | |
As you can see here the organization_tag_usage is completely empty but the actual join table that the query should be using is orgsystem_tag_usage.
select * from organization_tag_usage;
SELECT 0
select * from orgsystem_tag_usage
+----------------+----------------+
| orgsystem_id | tagusages_id |
|----------------+----------------|
| 1 | 10 |
| 1 | 16 |
| 1 | 17 |
| 1 | 18 |
| 3 | 19 |
| 3 | 20 |
| 2 | 23 |
| 1 | 25 |
| 1 | 26 |
| 1 | 27 |
| 2 | 30 |
| 1 | 38 |
| 1 | 39 |
| 1 | 52 |
| 3 | 53 |
| 3 | 54 |
+----------------+----------------+
SELECT 16
Is this a bug in eclipselink?
