JPA QueryDSL exists and count

Question

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.

Solution

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