Criteria builder group by and having count does not work

Question

This is the query i want to achieve:

 SELECT *
FROM ROOMENTITY R
JOIN CALENDARENTITY C ON R.id = C.room_id
WHERE C.available = TRUE AND
      C.date BETWEEN :checkin AND
      :checkout
GROUP BY C.room_id
HAVING COUNT(C.room_id)>= diffdays   

This is my code:

manager = factory.createEntityManager();
boolean hasPredicates = false;
final CriteriaBuilder criteriaBuilder = manager.getCriteriaBuilder();

final CriteriaQuery<RoomEntity> q = criteriaBuilder.createQuery(RoomEntity.class);

List<Predicate> predicates = new ArrayList<Predicate>();

Root<RoomEntity> fromRooms = q.from(RoomEntity.class);
String checkinDate = filter.getCheckinDate();
String checkoutDate = filter.getCheckoutDate();
if (checkinDate != null || checkoutDate != null) {
    hasPredicates = true;
    java.sql.Date checkin = java.sql.Date.valueOf(filter.getCheckinDate());
    java.sql.Date checkout = ava.sql.Date.valueOf(filter.getCheckoutDate());
    int diffInDays = (int)( (checkout.getTime() - checkin.getTime()) / (1000 * 60 * 60 * 24) ) + 1;
    System.out.println("Days: " + diffInDays);
    Join<RoomEntity, CalendarEntity> calendarEntityJoin= fromRooms.join("calendarEntities", JoinType.INNER);
    calendarEntityJoin.on(
                        criteriaBuilder.equal(calendarEntityJoin.join("calendarId").<Integer>get("room_id"),  fromRooms.<Integer>get("id"))
                );
    predicates.add(criteriaBuilder.and(
                        criteriaBuilder.equal(calendarEntityJoin.<String>get("available"), true),
                        criteriaBuilder.between(calendarEntityJoin.join("calendarId").<Date>get("date"),
                                checkin, checkout)
                ));
    q.groupBy(calendarEntityJoin);
    q.having(criteriaBuilder.gt(criteriaBuilder.count(fromRooms), diffInDays));
}

My Room Entity:

public class RoomEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;

    @OneToMany(mappedBy = "roomEntity", cascade = CascadeType.ALL)
    private Collection<CalendarEntity> calendarEntities;
}

My CalendarEntity:

public class CalendarEntity {

@EmbeddedId
private CalendarId calendarId;

@Basic
@Column(name = "available", nullable = false)
private Boolean available;

@ManyToOne
@JoinColumn(name = "room_id", referencedColumnName = "id", insertable = false, updatable = false)
private RoomEntity roomEntity;
}

My CalendarEntity Embedded id:

@Embeddable
public class CalendarId implements Serializable {

    @Column(name= "date", nullable = false)
    private Date date;

    @Column(name = "room_id", nullable = false)
    private Integer room_id;
}

Everything is good up until group by and having count. When i run this and i print the query with this code below, then there is groupby/having:

if (hasPredicates)
            q.where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
final TypedQuery<RoomEntity> TQ = manager.createQuery(q);
TQ.setFirstResult(filter.getStart());
TQ.setMaxResults(filter.getSize());
System.out.println("\n\nQuery:\n" + +Q.unwrap(JpaQuery.class).getDatabaseQuery().getSQLString() + "\n\n\n");

The query in the end is not what i want and it does implement group by and having count.

UPDATE: This is the query i am getting right now:

SELECT t1.id, t1.bathrooms, t1.bedrooms, t1.beds, t1.description, t1.exta_person_price, t1.house_rules, t1.max_people, t1.min_overnights, t1.neightborhood, t1.overnight_price, t1.square_meters, t1.title, t1.transport, t1.room_host, t1.room_location, t1.room_type_id FROM CALENDARENTITY t0, ROOMENTITY t1 WHERE (((t0.available = ?) AND (t0.date BETWEEN ? AND ?)) AND ((t0.room_id = t1.id) AND (t0.room_id = t1.id))) ORDER BY t1.overnight_price ASC

It is missing group by and having count!

Answer 1

The query worked after i used multiselect.

    Q.multiselect(fromRooms);