1

I have the following query and I don't know how can I write it using criteria.

SELECT questions_cnt, COUNT(*) users_cnt
FROM (
    SELECT COUNT(DISTINCT question) questions_cnt
    FROM UserAnswer
    GROUP BY user
) t
GROUP BY questions_cnt

I have started to write criteria query. It looks like the following

final DetachedCriteria subCriteria = DetachedCriteria.forClass(UserAnswer.class)
    .setProjection(
        Projections.projectionList()
            .add(Projections.alias(Projections.countDistinct("question"), "questions_cnt"))
            .add(Projections.groupProperty("user"))
    );
final DetachedCriteria criteria = DetachedCriteria.forClass(???);

I have succesfully written subquery, but I don't know how main query can be written. DetachedCriteria requires entity class to be created, but my main query does not use any tables. It uses another query in from clause as its source.

UserAnswer entiry has the following structure

@Entity
@Table(name = "user_answer")
public final class UserAnswer extends AbstractEntity {

    private static final long serialVersionUID = -3149418434619291049L;

    private Long id;
    private Date timestamp = new Date();
    private Answer answer;
    private Question question;
    private String userProvidedAnswer;
    private CourseSession courseSession;
    private User user;
    private Boolean checked;

    @Id
    @Override
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long getId() {
        return id;
    }

    @Override
    public void setId(Long id) {
        this.id = id;
    }

    @Version
    @Column(name = "timestamp", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    public Date getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(Date timestamp) {
        this.timestamp = timestamp;
    }

    @ManyToOne
    @ForeignKey(name = "fk__user_answer__answer")
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JoinColumn(name = "answer_id")
    public Answer getAnswer() {
        return answer;
    }

    public void setAnswer(Answer answer) {
        this.answer = answer;
    }

    @ManyToOne
    @ForeignKey(name = "fk__user_answer__question")
    // @OnDelete(action = OnDeleteAction.CASCADE) // SQL Server does not support a cascade operation for this column
    @JoinColumn(name = "question_id", nullable = false)
    public Question getQuestion() {
        return question;
    }

    public void setQuestion(Question question) {
        this.question = question;
    }

    @Column(name = "user_provided_answer", length = 255)
    public String getUserProvidedAnswer() {
        return userProvidedAnswer;
    }

    public void setUserProvidedAnswer(String userProvidedAnswer) {
        this.userProvidedAnswer = StringUtils.trimToNull(userProvidedAnswer);
    }

    @ManyToOne
    @ForeignKey(name = "fk__user_answer__course_session")
    @JoinColumn(name = "course_session_id", nullable = false)
    public CourseSession getCourseSession() {
        return courseSession;
    }

    public void setCourseSession(CourseSession courseSession) {
        this.courseSession = courseSession;
    }

    @ManyToOne
    @ForeignKey(name = "fk__user_answer__user")
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JoinColumn(name = "user_id", nullable = false)
    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Column(name = "is_checked")
    public Boolean getChecked() {
        return checked;
    }

    public void setChecked(Boolean checked) {
        this.checked = checked;
    }
}
J2James
  • 23
  • 4
  • what is ur requirement?table structres? – Salil Jul 06 '10 at 09:33
  • I have added the structure of UserAnswer table. I think that is all you need, but if no, I can provide other information. My query I have provided in this question has been simplified. A real query is more complex. – J2James Jul 06 '10 at 09:55
  • Tables structure for my question consists of only one table which is CREATE TABLE user_answer ( id INT NOT NULL PRIMARY KEY, question_id INT NOT NULL, answer_id INT NOT NULL, user_id INT NOT NULL ) – J2James Jul 06 '10 at 13:41

0 Answers0