JPA QueryDSL存在并计数

Question

我正在尝试将这些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

有人能给我一个提示或解决方案吗？

这是我的域名(摘录)：

@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>();

}

希望这能帮到更多的忙。我认为主要的一点是不可能用querydsl访问连接表。

Answer 1

这是一个解决方案的草图

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);