java JPA QueryDSL 存在并计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27562708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-11-02 12:00:12 来源:igfitidea点击:
JPA QueryDSL exists and count
提问by Philipp Schneider
I'm trying to got these SQL-Statement to a QueryDSL call.
我正在尝试将这些 SQL 语句用于 QueryDSL 调用。
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.
希望这会有所帮助。我认为主要的一点是不可能使用 querydsl 访问连接表。
回答by Timo Westk?mper
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);
