how to return more values in GET response?
My tables in database look like this:
user(id, login, password)
question(id, user_id, value, created_date, status, first_answer, second_answer)
answer(id, question_id, user_id, value)
For every question there can be only two possible answers. I use POST to answer questions (resource /answers).
Now when I do GET /questions/1, it returns this answer
{
"id" : 1,
"value" : "Example",
"createdDate" : 1495825431000,
"firstAnswer" : "A",
"secondAnswer" : "B",
"status" : "accepted"
}
(without user, because I use @JsonIgnore on this)
Now I'd like to make GET request and get in response more values (for example the login of the user who created the question and two counters that show how many times the first answer and the second answer were selected.
{
"id" : 1,
"value" : "Example",
"createdDate" : 1495825431000,
"firstAnswer" : "A",
"secondAnswer" : "B",
"status" : "accepted",
"createdBy" : "John",
"firstCount" : 120,
"secondCount" : 80
}
How to do it? I'd love to do it with one query in Springboot. For example like this:
SELECT q.id, q.value, q.first_answer, q.second_answer, q.created_date, q.status,
(SELECT COUNT(answer) FROM answer WHERE answer = 1 AND question_id = q.id) AS 'first_count',
(SELECT COUNT(answer) FROM answer WHERE answer = 2 AND question_id = q.id) AS 'second_count',
(SELECT u.login FROM user u WHERE u.id = q.user_id) AS 'createdBy'
FROM question q
My class with model looks like this:
@Entity
@Table(name = "question")
public class Question {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column
private Integer id;
@JsonIgnore
@ManyToOne
@JoinColumn(nullable = false)
private User user;
@Column(nullable = false)
private String value;
@Column(nullable = false)
private Timestamp createdDate;
@Column(nullable = false)
private String firstAnswer;
@Column(nullable = false)
private String secondAnswer;
@Column(nullable = false)
private String status;
public Question() {
}
//getters, setters, etc.
}
And my repository class looks like this:
public interface QuestionsRepository extends CrudRepository<Question, Integer> {
}
I don't want to create new rows in database.
I know I can fill @Transient variables in Controller's class. But I don't want to make many database query - I'd like to do it in one query, it's faster.
