[FIXED] Hibernate query to get a list of objects, each desired object is inside a list of other objects. The list is part of another object

Issue

I am struggling from the morning with a query. I have the following JSON :

{
    "id": "87ee51c7-3f15-4772-a2fb-bee379e1054d",
    "description": "What color do you like?",
    "option1": "Yellow",
    "option2": "Blue",
    "option3": "Green",
    "option4": "Red",
    "option5": "Pink",
    "votes": [
      {
        "id": "26bf4e30-c75a-4267-8850-4f04101fdd35",
        "answerOption": "Pink",
        "user": {
          "id": "f836bd80-53d8-4623-8198-ca375abfdbbc",
          "authId": "auth0|5ff8fe4bee9af4febf00762c3f1e",
          "firstName": "Test",
          "lastName": "Name",
          "phone": "07545166181",
          "email": "[email protected]",
          "buildingStreet": "test",
          "buildingNumber": "15",
          "buildingName": "8",
          "buildingEntrance": "A",
          "town": "town",
          "country": "country",
          "other": ""
        },
        "date": "2021-01-30"
      },
      {
        "id": "0047474f-ecf1-424b-960a-a512e6bb14f5",
        "answerOption": "Blue",
        "user": {
          "id": "7da77dff-a22a-47ac-b995-41fa90866016",
          "authId": "auth0|60055f048ec4a3006ee36d64",
          "firstName": "John",
          "lastName": "Doe",
          "phone": "0755888999",
          "email": "[email protected]",
          "buildingStreet": "street",
          "buildingNumber": "15",
          "buildingName": "8",
          "buildingEntrance": "A",
          "town": "town",
          "country": "country",
          "other": ""
        },
        "date": "2021-01-30"
      }
    ],
    "status": "active",
    "endDate": "2021-02-05"
  }

So I have a Poll, with an id, description and 5 options to vote. The Poll has a list of votes, so i have multiple votes. Each vote contains an answer, date and user.

Is there a query to build a list of users knowing the question id ?

The idea is to build this list of user who already vote so i restrict them to vote twice for the same Poll.

I am using Hibernate as ORM, Spring Boot for back and React for front.

List findAllUsers....where poll id is...

Is there a way to get there with a statement ? Lately I am using Hibernate and I am very rusty with handmade queries.

Thank you in advance

Solution

Assuming the data structure looks nearly like this:

public class User {

  @Id private Long id;
  
  // getters & setters

}

public class Vote {

  @Id private Long id;
  
  @ManyToOne private Poll poll;
  
  @ManyToOne private User user;
  
  // getters & setters

}

public class Poll {

  @OneToMany List<Vote> votes;
  
  // getters & setters
  
}

---

Using repositories, IMHO the cleanest place where to specify would be the VoteRepository. For that, the repository should look like this (using query generation with method names, see Query Methods in Spring Data JPA Reference Guide):

public interface VoteRepository extends JpaRepository<Vote> {

  List<Vote> findByPoll(Poll poll);

}

Then, to get all the users related to a poll, e. g. in a controller, mapping from votes to users should be done:

@Autowired
VoteRepository voteRepository;

public List<User> getUsersRelatedToPoll(Poll poll) {
  List<Vote> votes = voteRepository.findByPoll(poll);
  return votes.stream().map(Vote::getUser).collect(Collectors.toList());
}

---

Another way, IMHO not the cleanest way (^1), could be a @Query method (see Using @Query in Spring Data JPA Reference Guide). This method would look like this:

public interface FooRepository extends JpaRepository<Foo> {

  @Query("SELECT v.user FROM VOTE as v WHERE v.poll = :poll")
  List<User> findUsersByPoll(@Param("poll") Poll poll);

}

^1: I think, e. g. in the UserRepository should not know about or use Poll or Vote ...

Answered By - hjoeren