Home find all historyRequest where a status does not exist using distinct foreign key in subquery (JPA,HQL,JAVA)
Reply: 0

find all historyRequest where a status does not exist using distinct foreign key in subquery (JPA,HQL,JAVA)

Max Mazur
1#
Max Mazur Published in 2017-12-07 09:56:43Z

Hello everyone I have a problem writing the correct query using HQL, maybe someone can guide me in the right direction. I want to find all historyRequests based on a cvr number and where it doesn't have a specific status.

Entities:

@Data
@EntityListeners(AuditingEntityListener.class)
@MappedSuperclass
public class AbstractEntity {
  @Id
  @GeneratedValue
  Long id;

  @JsonFormat(shape = Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
  @CreatedDate
  LocalDateTime created;

  @JsonFormat(shape = Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss")
  @LastModifiedDate
  LocalDateTime modified;
}

public class HistoryRequest extends AbstractEntity {

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
  @JoinColumn(name = "history_request_id")
  private List<Status> statusList;

  @Column(name = "cvr")
  private String cvr;
}

public class Status extends AbstractEntity {

  @Enumerated(EnumType.STRING)
  @Column(name = "event")
  private StatusEvent event;
}

@Query("select request from HistoryRequest as request where request.cvr = :cvr "
    + "AND request.id not in (SELECT DISTINCT status.historyRequestId FROM Status as status WHERE event = :event)")
List<HistoryRequest> findAllByCvrWhereStatusEventNotEqual(@Param("cvr") String cvr, @Param("event") String event);

i get this exception when I try to run my program:

org.hibernate.QueryException: could not resolve property: historyRequestId of: com.myprojects.domain.Status

So I can understand from the exception that it is looking for a property in Status named historyRequestId, however it is not a property but an autogenerated column in the status table from the @JoinColumn(name = "history_request_id")

How am I supposed to deal with this if I don't want status to have any relation to historyRequest? Neither property or parent object!

Thanks in advance!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.328986 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO