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.
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
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"
@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?
Selecting from derived tables (or having subqueries in the FROM clause) is not currently supported by JPA.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments