I'm trying to got these SQL-Statement to a QueryDSL call.
select t.asd_id, count(*) as count from asset_object t
left join asset_data ad on (t.asd_id = ad.asd_id)
where
exists (select * from assetobject_parents p
where t.aso_id = p.aso_id and p.ctd_id = 1)
group by t.asd_id
order by count(*) desc, t.asd_id asc
Can anyone give me a hint or a solution?
Here is my Domain (extract):
@Entity
public class AssetObject {
@Id
@Column(name = "ASO_ID")
private Long asoId;
@ManyToMany
@OrderColumn(name = "ASP_ORDER")
@JoinTable(name = "ASSETOBJECT_PARENTS", joinColumns = { @JoinColumn(name = "ASO_ID", referencedColumnName="ASO_ID") }, inverseJoinColumns = { @JoinColumn(name = "CTD_ID", referencedColumnName="CTD_ID") })
private List<CategoryData> parents = new ArrayList<CategoryData>();
}
@Entity
public class CategoryData {
@Id
@Column(name = "CTD_ID")
private Long ctdId;
}
@Entity
@Table(name = "ASSET_DATA")
public class AssetData {
@Id
@Column(name = "ASD_ID")
private Long asdId;
@ManyToMany
@JoinTable(name = "ASSETDATA_CATEGORYDATA", joinColumns = { @JoinColumn(name = "ASD_ID", referencedColumnName="ASD_ID") }, inverseJoinColumns = { @JoinColumn(name = "CTD_ID", referencedColumnName="CTD_ID") })
private List<CategoryData> categoryDataList = new ArrayList<CategoryData>();
}
Hope this helps a little bit more. I think the main point is that it's not possible to access join-tables with querydsl.
Here is a sketch for a solution
QAssetobject t = QAssetobject.assetobject;
QAssetData ad = QAssetData.assetData;
QAssetobjectParents p = QAssetobjectParents.assetobjectParents;
List<Tuple> results = query.from(t)
.leftJoin(ad).on(t.asdId.eq(ad.asdId))
.where(new SQLSubQuery().from(p)
.where(t.asoId.eq(p.asoId), p.ctdId.eq(1)).exists())
.groupBy(t.asdId)
.orderBy(Wildcard.count.desc(), t.asdId.asc())
.list(t.asdId, Wildcard.count);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With