How to limit a criteria to fk's in another table?

Question

I have a table, car, each car has an id that may appear in option (options are stuff like defrosters, power windows, etc) in the column FK_car_id. So car has a 0-to-many relationship with option.

I need cars with options.

The SQL looks like this:

SELECT c.* from car c
  LEFT JOIN options o on c.id = o.FK_car_id
  GROUP BY c.id;

How do I do this in hibernate? Here's what I've tried:

var c = newCriteria().readOnly();

c.createAlias('car','car');
c.sqlRestriction('SELECT c.* from car c LEFT JOIN options o on c.id = o.FK_car_id GROUP BY c.id');

c.return c.list(asQuery = false);

But I get "Could not extract Result Set".

---

Note: this is a simplified representation of my code to highlight the main issue and produce a minimal example.

Answer 1

Use this:

c.createCriteria(Car.class, "car")
      .createAlias("car.options", "options", JoinType.LEFT_OUTER_JOIN)
      .setProjection(Projections.groupProperty("car.id"));