JPA find entities in database if list of nested objects has only one particular value?

Question

I have the following two tables, one which contains a list of instructions and another which is basically an audit trail of state changes for the instruction. So for example an instruction has an initial status of "PENDING", and then it can be move to "Authorised" or "CANCELLED" or "IN Progress" etc., and we keep a trail of who moved it to what state, and when etc.

@Entity
@Table(name = "INSTRUCTION")
public class Instruction {

    @Id
    @Column(name = "ID", nullable = false, unique = true)
    public Long id;

    @Column(name = "ACCOUNT", nullable = false)
    public String account;

    @OneToMany(cascade = CascadeType.All, fetch = FetchType.EAGER)
    @JoinColumn(name = "INSTRUCTION_ID", referenceColumnName = "ID")
    @OrderBy("lastUpdated")
    private List<Audit> auditItems = new ArrayList<>();

    //Getters & Setters
}

.

@Entity
@Table(name = "AUDIT")
public class Audit {

    @Id
    @Column(name = "ID", nullable = false, unique = true)
    public Long id;

    @Column(name = "INSTRUCTION_STATUS", nullable = false)
    public InstructionStatus status;

    @Column(name = "LAST_UPDATED", nullable = false)
    public LocalDateTime lastUpdated;

    @Column(name = "LAST_UPDATED_BY", nullable = false)
    public String lastUpdatedBy;

    //Getters & Setters
}

I have to write a query that will return me all the "PENDING" instructions for a given account. So far I have worked out List<Instruction> findByAuditItemsStatusAndAccount(InstructionStatus status, String account) which will return me all the instructions that have a "PENDING" audit item in that list. However, this will return instructions that have other audit items too, for example ones with both "PENDING" and "AUTHORISE", which I don't want, because this has now moved to the authorised state and is no longer pending. What I want is to return only the instructions where the latest audit item is the provided status (in this example "PENDING"), but where I could also say pass it "AUTHORISED" and it would retrieve me ones where the latest audit item has a status of "AUTHORISED" and so on.

Is there a way to write the query to fetch me this? I know I could keep my query as is and then filter this using java, but I'd rather try and do it at the database level if possible.

For some clarification, see this sqlfiddle link. Basically I want a query that only returns the account H from that example. Is that possible?

wafa mhamdi · Accepted Answer · 2021-04-30 17:31:18Z

2

For this problem you can use a subquery like :

SELECT INSTRUCTION.ACCOUNT, INSTRUCTION.ID FROM INSTRUCTION 
INNER JOIN INSTRUCTION_AUDIT ON INSTRUCTION.ID = INSTRUCTION_AUDIT.INSTRUCTION_ID
WHERE INSTRUCTION.ID not in (SELECT INSTRUCTION_ID
                             FROM INSTRUCTION_AUDIT 
                             WHERE STATUS !='PENDING');

You can test the query in SQL Fiddle.

answered Apr 30, 2021 at 17:31

wafa mhamdi

213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JPA find entities in database if list of nested objects has only one particular value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related