0

I'm guessing how the query below could be written through JPQL:

Select all parents(side 1 of a 1-n relationship) which last child (newest id) has a specific value for a column (attribute). My entities look like

Parent.java

@Entity
class Parent {

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

@OneToMany(mappedBy="parent")
private List<Child> children;

}

Child.java

@Entity 
class Child {

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


@ManyToOne()
@JoinColumn(name="ID_PARENT")
private Parent parent;

private String attribute;


}

What would JPQL look like? Thank you all

user7197
  • 157
  • 11

1 Answers1

0

It should not be very different from a SQL. I got this working in a similar DB I have here.

SELECT *
FROM Parent p
INNER JOIN Child c
ON c.ID_PARENT   = p.ID_PARENT
WHERE c.ID_CHILD =
  (SELECT c2.ID_CHILD
  FROM Child c2
  WHERE c.ID_PARENT = c2.ID_PARENT
  AND c2.attribute = <parameter>
  AND c2.ID_CHILD   =
    (SELECT MAX(c3.ID_CHILD)
    FROM apuracao_fator_risco c3
    WHERE c2.ID_PARENT = c3.ID_PARENT
    )
  )

These posts can also be useful:

JPA Select latest instance for each item

JPA sorting query by property of last element in child collection

Community
  • 1
  • 1
Vitor Santos
  • 581
  • 4
  • 15
  • Thank you, Vitor. But the problem is exactly translating it to jpql. I pretended to do something like that, but couldn't get how to do so with jpa, without native queries. – user7197 Oct 24 '14 at 13:01