expected got in spring-data hibernate

Question

Working with spring-data and hibernate and trying to write this request in a repository :

    @Query("select u from AppUser u inner join u.roles r inner join u.contrats c where r = :role and not exists ( from c.project p where p = :project)")

My goal is to get List of AppUser (Users) from entity AppUer ,but with a condition , only those who don't have the entity project .

To explain more , there is an entity Contrat between Project and AppUser , it contains Project project , AppUser appuser .

There is a mapping between one_to_many between AppUser and Contrat(AppUser can have multiple contrats) .

Also a mapping of one_to_many between Contrat and project ( One project can have multiple contrat ) .

The problme is that this request returns a empty list while it should return a list of 12 users as they don't have the specified project i passe to it.

I remark in this request (from c.project p where p = :project) 'from' is underlined with red line saying :

<expression> expected got <from> in spring-data hibernate.

Any idea ?

Edit

Entity AppUser

@Entity
@Data
@AllArgsConstructor @NoArgsConstructor
public class AppUser implements Serializable {

    @Id @GeneratedValue
    private Long id;
    @Column(unique = true)
    private String username;
    private String password;
    private String prenom;
    private String nom;
    private Long tel;
    private String cin;
    private String email ;
    @ManyToMany(fetch = FetchType.EAGER)
    private Collection<AppRole> roles = new ArrayList<>();


    @OneToMany(mappedBy = "appUser" )
    @Fetch(value = FetchMode.SUBSELECT)
    @JsonManagedReference(value="appuser-contrat")
    private Collection<Contrat> contrats = new ArrayList<Contrat>();



    public void addToContrats(Contrat contrat){
        this.contrats.add(contrat);
    }


}

Entity Contrat

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Contrat implements Serializable{

        @Id @GeneratedValue
        private Long id;
        private Date dateDebut ;
        private Date dateFin ;
        private Long idDevloppeur;
        @ManyToOne
        @JoinColumn(name = "Id_Project")
        @JsonBackReference(value="projet-contrat")
        private Project project;

        @ManyToOne
        @JoinColumn(name = "Id_AppUser")
        @JsonBackReference(value="appuser-contrat")
        private AppUser appUser;


}

Entity Project

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Project implements Serializable{

        @Id @GeneratedValue
        private long id;
        private String intitule;
        private String description;

        @OneToMany(mappedBy = "project" )
        @Fetch(value = FetchMode.SUBSELECT)
        @JsonManagedReference(value="projet-contrat")
        private Collection<Contrat> contrats  = new ArrayList<Contrat>();



        public void addToContrats(Contrat contrat){
                this.contrats.add(contrat);
        }



 }

I was following this link http://www.codewrecks.com/blog/index.php/2010/03/09/power-of-hql-condition-on-a-collection/ if you can see it please . — dEs12ZER, Jun 05 '18 at 13:17
There is no more the underlined line but always i get an empty list i don't know why — dEs12ZER, Jun 05 '18 at 13:26
the missing select isn't the problem (I thought only the last part was your problem sorry). What is in our db ? could it be that the would be selected AppUser entities have no roles or contrats? — Michal, Jun 05 '18 at 13:31
I have edited the post with the tree entities , the 12 users have the right role it's CLIENT , as when i try to list only users with this role i get exactly List of 12 users , the second thing is these users have not any contrats yet , that why im waiting to get all these 12 users , if the user doesnt have any contrat so he doesnt have any field project in contrat , so i should get it in list result . — dEs12ZER, Jun 05 '18 at 13:38
Thank you so much for help , this worked fine for me , can you please explain to me what's the different between both of this types of join ? — dEs12ZER, Jun 05 '18 at 13:49

score 1 · Accepted Answer · answered Jun 05 '18 at 13:57

(Posting answer to summarize solution in comments)

In given select query "inner join u.contrats" was used, but there were no contracts entities for users. Solution: replace with "left join u.contrats".

Inner join vs left join already solved here What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

expected got in spring-data hibernate

1 Answers1