Ejbql query.Create correct clause

[rjkz_83]

Hello all.

I need help with ejbql query creation.

I need to process the next situation.

user
--------------
level|subject|
--------------
  1  |   1   |
--------------
  2  |   2   |
--------------
  3  |   3   |
--------------
  4  |   4   |
--------------
  5  |   5   |
--------------


cluster
--------------
level|subject|
--------------
  1  |   1   |
--------------
  2  | null  |
--------------
 null|   3   |
--------------
  4  |   5   |
--------------
  4  |   5   |
--------------

* I miss some columns(socialNetworkUserId,socialCluster.id) because of it is constants

I need to get first three lines.

So I created the following query:


@NamedQuery(name="test",
                query ="select userEd,clusterEd " +
                        "from EducationEntity userEd,ClusterEducationsEntity clusterEd " +
                        "where userEd.socialNetworkUserId =1 " +
                        "and clusterEd.socialCluster.id =1 " +
                        "and ((userEd.educationLevel.id =clusterEd.educationLevel.id and userEd.educationSubject.id =clusterEd.educationSubject.id) or (userEd.educationLevel.id =clusterEd.educationLevel.id and clusterEd.educationSubject is null) or (userEd.educationSubject.id =clusterEd.educationSubject.id and clusterEd.educationLevel is null))" )


In the log I see that "test" was interpreted as

SELECT t0.id,
       t0.social_network_user_id,
       t0.education_level_id,
       t0.education_subject_id,
       t1.id,
       t1.education_level_weight,
       t1.education_subject_weight,
       t1.education_level_id,
       t1.education_subject_id,
       t1.socio_cluster_id

FROM   public.education_subject t6,
       public.education_subject t5,
       public.education_level t4,
       public.education_level t3,
       public.socio_cluster t2,
       public.cluster_educations t1,
       public.education t0

WHERE ((((t0.social_network_user_id = ?)
      AND (t2.id = ?))
      AND ((((t3.id = t4.id)
      AND (t5.id = t6.id))
      OR ((t3.id = t4.id)
      AND (t1.education_subject_id IS NULL)))
      OR ((t5.id = t6.id)
      AND (t1.education_level_id IS NULL))))
      AND (((((t2.id = t1.socio_cluster_id)
      AND (t3.id = t0.education_level_id))
      AND (t4.id = t1.education_level_id))
      AND (t5.id = t0.education_subject_id))
      AND (t6.id = t1.education_subject_id)))



So my problem is in the following

these conditions
 AND (t5.id = t0.education_subject_id))
 AND (t6.id = t1.education_subject_id)))

and these one
AND (t1.education_subject_id IS NULL)))
AND (t1.education_level_id IS NULL))))


will never present simultaneously.

these conditions

 AND (t5.id = t0.education_subject_id))
 AND (t6.id = t1.education_subject_id)))

appear because of the relationships between entities

 

So the result of the query is empty.



Please, help me with this issue.
I can do what I need with three queries but it can slow down the application.

thanks in advance



I have the following entities:
------------------------------------------------------------------------------------
@Entity
@Table(schema = "public", name = "education")
public class EducationEntity implements Serializable{
    private Integer id;

    @Id
    @Column(name = "id", nullable = false, length = 10)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    private Integer socialNetworkUserId;

    @Basic
    @Column(name = "social_network_user_id", length = 10)
    public Integer getSocialNetworkUserId() {
        return socialNetworkUserId;
    }

    public void setSocialNetworkUserId(Integer socialNetworkUserId) {
        this.socialNetworkUserId = socialNetworkUserId;
    }

    private EducationSubjectEntity educationSubject;

    @ManyToOne
    @JoinColumn(name = "education_subject_id", referencedColumnName = "id")
     public EducationSubjectEntity getEducationSubject() {
        return educationSubject;
    }

    public void setEducationSubject(EducationSubjectEntity educationSubject) {
        this.educationSubject = educationSubject;
    }
    private EducationLevelEntity educationLevel;

    @ManyToOne
    @JoinColumn(name = "education_level_id", referencedColumnName = "id")
    public EducationLevelEntity getEducationLevel() {
        return educationLevel;
    }

    public void setEducationLevel(EducationLevelEntity educationLevel) {
        this.educationLevel = educationLevel;
    }
}
------------------------------------------------------------------------------------
@Entity
@Table(schema = "public", name = "cluster_educations")
public class ClusterEducationsEntity implements Serializable {
    private Integer id;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, length = 10)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    private SocioClusterEntity socialCluster;

    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.REFRESH, CascadeType.PERSIST})
    @JoinColumns({@JoinColumn(name = "socio_cluster_id", referencedColumnName = "id")})
    public SocioClusterEntity getSocialCluster() {
        return socialCluster;
    }

    public void setSocialCluster(SocioClusterEntity socialCluster) {
        this.socialCluster = socialCluster;
    }

    private EducationSubjectEntity educationSubject;

    @ManyToOne
    @JoinColumn(name = "education_subject_id", referencedColumnName = "id")
    public EducationSubjectEntity getEducationSubject() {
        return educationSubject;
    }

    public void setEducationSubject(EducationSubjectEntity educationSubject) {
        this.educationSubject = educationSubject;
    }

   
    private EducationLevelEntity educationLevel;

    @ManyToOne
    @JoinColumn(name = "education_level_id", referencedColumnName = "id")
    public EducationLevelEntity getEducationLevel() {
        return educationLevel;
    }

    public void setEducationLevel(EducationLevelEntity educationLevel) {
        this.educationLevel = educationLevel;
    }
}
------------------------------------------------------------------------------------

@Entity
@Table(schema = "public", name = "education_level")
public class EducationLevelEntity implements Serializable {
    private Integer id;

    @Id
    @Column(name = "id", nullable = false, length = 10)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    private List<EducationEntity> educationEntity;

    @OneToMany(targetEntity = EducationEntity.class, mappedBy = "educationLevel")
    public List<EducationEntity> getEducationEntity() {
        return educationEntity;
    }

    public void setEducationEntity(List<EducationEntity> educationEntity) {
        this.educationEntity = educationEntity;
    }

     private List<ClusterEducationsEntity> clusterEducationEntity;

    @OneToMany(targetEntity = ClusterEducationsEntity.class, mappedBy = "educationLevel")
     public List<ClusterEducationsEntity> getClusterEducationEntity() {
        return clusterEducationEntity;
    }

    public void setClusterEducationEntity(List<ClusterEducationsEntity> clusterEducationEntity) {
        this.clusterEducationEntity = clusterEducationEntity;
    }
   
}
------------------------------------------------------------------------------------
@Entity
@Table(schema = "public", name = "education_subject")
public class EducationSubjectEntity implements Serializable {
    private Integer id;

    @Id
    @Column(name = "id", nullable = false, length = 10)
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    private List<EducationEntity> educationEntity;

    @OneToMany(targetEntity = EducationEntity.class, mappedBy = "educationSubject")

    public List<EducationEntity> getEducationEntity() {
        return educationEntity;
    }

    public void setEducationEntity(List<EducationEntity> educationEntity) {
        this.educationEntity = educationEntity;
    }

    private List<ClusterEducationsEntity> clusterEducationEntity;

    @OneToMany(targetEntity = ClusterEducationsEntity.class, mappedBy = "educationSubject")
    public List<ClusterEducationsEntity> getClusterEducationEntity() {
        return clusterEducationEntity;
    }

    public void setClusterEducationEntity(List<ClusterEducationsEntity> clusterEducationEntity) {
        this.clusterEducationEntity = clusterEducationEntity;
    }

}

[James Sutherland]

My guess is you need to be using an outer-join on the relationship to the subject,

i.e. something like,

"select userEd,clusterEd " +
                        "from EducationEntity userEd left outer join userEd.educationSubject userSubject, ClusterEducationsEntity clusterEd left outer join clusterEd.educationSubject clusterSubject" +
                        "where userEd.socialNetworkUserId =1 " +
                        "and clusterEd.socialCluster.id =1 " +
                        "and ((userEd.educationLevel.id =clusterEd.educationLevel.id and userSubject.id =clusterSubject.id) or (userEd.educationLevel.id =clusterEd.educationLevel.id and clusterEd.educationSubject is null) or (userSubject.id =clusterSubject.id and clusterEd.educationLevel is null))"

[rjkz_83]

Hello
thanks for response.

Sorry but your proposal does not work.
But i found the solution:

1) Using view. Create view based on the "clean" sql.This view should agregate all data you need.
Then create entity based on this view.
And it's qiute easy to make the query to the agregated data.


2) Using stored functions(PL/pgSQL).

best regards