CODEWITHSUNDEEP
javasqljpa
Spring
Spring

Reputation: 11835

JPA how to join these entities

Given the JPA entities below, I want to get all Debits which has a Request with at least one succesful status.

There can be many requests with the same debit_id and different statuses

Should I use something like this or there is a better way of doing things

entityManager.createQuery( "select c from Debit d join d.id where request.status =Succesful"

@Entity(name = "T_DEBIT")
public class Debit {
  public enum Status { NEW, OLD } 

  @Column(name = "STATUS", nullable = false, length = 20)
  @Enumerated(value = EnumType.STRING)
  private Status status;
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

  @ManyToOne(optional = false)
  @JoinColumn(name = "ACCOUNT_ID", updatable = false, nullable = false)
  private Account account;

}

and other entity is

@Entity(name = "T_REQUEST")
public class Request{  

  public enum Status { PENDING, FAILED, SUCCESFUL}  

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "ID")
  private Long id;

  @ManyToOne(optional = false)
  @JoinColumn(name = "DEBIT_ID", updatable = false, nullable = false)
  private Debit debit;

  @Column(name = "STATUS", nullable = false, length = 20)
  @Enumerated(value = EnumType.STRING)
  private Status status;

 }

Please do comment if anything is missing instead of closing or downvoting the question!

Upvotes: 0

Views: 232

Answers (1)

gpeche
gpeche

Reputation: 22504

Basically:

select d
from T_DEBIT d
where exists (
    select r
    from T_REQUEST r
    where 
    r.debit.id = d.id and
    r.status = SUCCESSFUL
)

Check for enum syntax inside JPQL, I do not usually use enums for entities and it could be wrong in this example.

As a style issue, i would make entity name == class name instead of entity name == table name. It makes the fact that JPQL IS NOT SQL clearer

UPDATE

Spring asks for a solution to a similar problem. The way to work these things out is very systematic:

a) Rewrite your problem, using only basic filters and the following expressions:

  1. "exists some ... such that condition is true"
  2. "for all ... condition is true"

b) Translate:

  1. This case becomes exists (select ... where condition)
  2. This case becomes not exists (select ... where NOT condition)

In Spring's particular question, "exclude all succesfull requests", the goal is not very clear. If he/she means "get all debits without successful requests", then you would do:

a) Rewrite the question as "get all debits such that for all associated requests, request status is not SUCCESSFUL". b) Translate as

select d
from T_DEBIT d
where not exists (
    select r
    from T_REQUEST r
    where 
    -- This is the join condition, so it should not be negated
    r.debit.id = d.id and 
    -- This is the actual filtering condition, negate as this is a FOR ALL
    not (r.status != SUCCESSFUL)
)

Then you could simplify that last condition, getting:

select d
from T_DEBIT d
where not exists (
    select r
    from T_REQUEST r
    where 
    r.debit.id = d.id and
    r.status = SUCCESSFUL
)

Upvotes: 2

Related Questions