Unable to write criteria with reference classes

Question

We have entity like this-

• College has many Departments.
• NGO can visit many Colleges.

We have to write criteria to get- list of five colleges in which any of the department's fest happened recently and visited by NGO name "xyz".

Note: At the time of visit we have to maintain some other record as well,so we have to make oneToMany relationship of both NGO and College with Visit Entity class,rather than manyToMany relationship between College and Visit.

So our domain(Entity) become like this:-

class College{
@OneToMany(mappedBy="visitedTo")
List<Visit> collegeVisites=new ArrayList<Visit>();
@OneToMany(mappedBy="college")
List<Department> departments=new ArrayList<Department>();
}

class NGO{
@OneToMany(mappedBy="visitedBy")
List<Visit> ngoVisites=new ArrayList<Visit>();
@Column
String name;
}

class Department{
@ManyToOne
College college;
@Column
Date festDate;
}

class Visit{
@ManyToOne
College visitedTo;
@ManyToOne
NGO visitedBy;
}

I done this by using java code first i fetched all Department order by recently organised fest,then retrieve desire colleges following some java,as mentioned below.That is not good practice since we are unnecessary fetching all departments from db.Can anyone provide me criteria which can give me only five colleges-

Criteria cr=session.createCriteria(Department.class);
List<Department> departments=cr.add(Restriction.addOrder(Order.desc("festDate"))).list();

List<College> colleges=new ArrayList<College>();
for(Department department:departments){
for(Visit visit :department.getCollege().getCollegeVisites()){
if(visit.getName().equals("xyz")&& !colleges.contains(department.getCollege()){
colleges.add(department.getCollege())
break;
}
if(colleges.size()=>5){
break;
}
}

Answer 1

Unfotunately, I'm a NHibernate user and C# programmer, so bear with me it might have some syntax problems here.

Criteria collageQuery = session
  .createCriteria(Collage.class, "collages")
  .createAlias("collageVisites", "collageVisites")
  .createAlias("departments", "departments")
  .setProjection(Projections.projectionList()
    .add(Projections.groupProperty("collages.id"))
    .add(Projections.min("departments.festDate", "minfestDate")))
  .add(Restrictions.eq("collegeVisites.name","xyz" ))
  .addOrder(Order.desc("minfestDate"))
  .setMaxResults(5);

Should create a query like this (DBMS specific, probably SQL Server syntax here):

SELECT TOP (5) 
  collage.id,
  min(department.festDate)
FROM collage
  inner join collageVisites ...
  inner join department
WHERE collageVisites.name = "xyz"
GROUP BY collage.id
ORDER BY min(department.festDate)

It returns the collage ids and the min(festDate) (because of the order by, this is required). You could try to get full collages back, but it is not so easy. At the end it will be easier to get the collages with session.get(id) afterwards, they are only five anyway.