CODEWITHSUNDEEP
javasqlhibernatecriteria
Boris Horvat
Boris Horvat

Reputation: 573

Complex hibernate query using criteria API

I am trying to write a complex query using criteria API. I could use some help with this.

I have 3 classes, Assets, AssetComponent and Tasks. The Task class has a 1 to M relationship with AssetComponent and AssetComponent has 1 to M relationship with Asset. I need to find all Assets (it should be unique list) that have some tasks associated with them (the task has a component and the component knows a assets it is connected).

This is what I have so far

public List<Asset> retrieveTask(Project project, boolean assigned) {

  DetachedCriteria subquery = DetachedCriteria.forClass(Task.class);

  subquery.add(Restrictions.eq("project", project));
  if (assigned) {
     subquery.add(Restrictions.isNotNull("assignedTo"));
  } else {
     subquery.add(Restrictions.isNull("assignedTo"));
  }

  subquery = subquery.createCriteria("component");
  subquery.add(Restrictions.isNotNull("asset"));

  Criteria criteria = super.createCriteria();
  criteria.add(Subqueries.in("id", subquery));

  return criteria.list();
}

The deataced query should return all of the tasks that have asset assigned to them and are a part of the give project. Now I need to see what are the unique assets for all of those tasks.

Thanks for helping me with this.

Cheers

Upvotes: 0

Views: 2631

Answers (1)

John Ericksen
John Ericksen

Reputation: 11113

A bit of a shot in the dark, but how does this work for you:

public List<Asset> retrieveTask(Project project, boolean assigned) {

    DetachedCriteria subquery = DetachedCriteria.forClass(Task.class);

    subquery.add(Restrictions.eq("project", project));
    DetachedCriteria assetCriteria = subquery
        .createCriteria("component")
        .createCriteria("asset").setProjection(Projections.groupProperty("id"));

    Criteria criteria = super.createCriteria();
    if(assigned) {
        criteria.add(Subqueries.propertyIn("id", subquery));
    }
    else {
        criteria.add(Subqueries.propertyNotIn("id", subquery));
    }
    return criteria.list();
}

Upvotes: 1

Related Questions