Can I use select from derived tables with JPA?

Question

I have a table 'asset' that has a 1 to n relation with a table called 'asset_properties' that contains a list of properties of that asset, and a many to many (using an intermediate table 'asset_has_tag') relation with table 'tags' that contains a list of tags.

I need to get a list of assets that have BOTH some specific tags AND some property values.

If I needed assets that have EITHER some tags OR some properties I could simply add both results of the following jpa queries to a java.util.Set.

I can get what I want with native SQL using the following query.

---

The Native SQL Query:

SELECT a.*
FROM (SELECT ap.* 
    FROM asset ap JOIN asset_property p
    WHERE p.value LIKE "%asd%" OR ap.name LIKE "%asd%" OR ap.description LIKE "%asd%"
) a
JOIN asset_has_tag r, tag h
WHERE a.uuid = r.asset_id AND h.uuid=r.tag_id AND h.category IN ("asd", "qwe", "zxc")
GROUP BY a.uuid

---

The JPA Queries:

String findByAssetAndTagValues =
"select distinct(a) from Asset a join a.Tags h where a.name like :assetname or a.description like :assetdescription and h.name in :tagnames and h.category in :tagcategories and h.uuid=:taguuids"

String findAssetsWithPropertyByValue =
"select distinct(a) from Asset a join a.assetProperties p where p.value like :value"

---

The Entities (empty constructors, getters and setters removed)

@Entity
public class Asset implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(generator = "myUUID")
    @GenericGenerator(name="myUUID", strategy="uuid2")
    @Column(unique = true, nullable = false, length = 16)
    private UUID uuid;

    private String description;

    // bi-directional many-to-one association to assetProperty
    @OneToMany(mappedBy = "asset", fetch = FetchType.LAZY)
    private List<AssetProperty> assetProperties;

    // bi-directional many-to-many association to tag
    @ManyToMany(mappedBy = "assets", fetch = FetchType.LAZY)
    private Set<tag> tags;

    @Override
    public boolean equals(Object obj) {
        return (obj != null && obj instanceof Asset && ((Asset)obj).getUuid().equals(uuid));
    }
}

@Entity
public class AssetProperty implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique=true, nullable=false)
    private int id;

    @Column(nullable=false, length=255)
    private String name;

    @Column(length=512)
    private String value;

    //bi-directional many-to-one association to Asset
    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="asset_id", nullable=false)
    private Asset asset;
}

@Entity
@Table(name = "hardtag")
public class Hardtag implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(unique = true, nullable = false)
    private UUID uuid;

    @Column(length = 255)
    private String category;

    @Column(nullable = false, length = 255)
    private String name;

    // bi-directional many-to-many association to Asset
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "asset_has_tag", joinColumns = { @JoinColumn(name = "tag_id", nullable = false) }, inverseJoinColumns = { @JoinColumn(name = "asset_id", nullable = false) })
    private Set<Asset> assets;

    @Override
    public boolean equals(Object obj) {
        return obj instanceof Hardtag && ((Hardtag) obj).getUuid().equals(uuid);
    }
}

EDIT: Any alternatives since JPA doesn't support it yet?

EDIT2 (2023): It seems this may now be possible, although I have not tested it: https://openjpa.apache.org/builds/3.2.2/apache-openjpa/docs/#jpa_langref_subqueries

Answer 1

Selecting from derived tables (or having subqueries in the FROM clause) is not currently supported by JPA.