JPA: Join and order by/limit constraint on child table of OneToMany relation with fluent or @Query-CodePudding

Given the following model:

@Entity
open class Parent {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    open val id: Long? = null

    @OneToMany(cascade = [CascadeType.ALL])
    @JoinColumn(name = "parent_id")
    @OrderColumn(name = "version")
    private var children: MutableList<Child> = mutableListOf()

    fun addChild(child: Child) {
        child.parent = this
        children.add(child)
    }

    fun getChildPosition(child: Child): Int {
        return children.indexOf(child)
    }
}

@Entity
open class Child {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    open val id: Long? = null

    @ManyToOne
    @JoinColumn(name = "parent_id")
    open var parent: Parent? = null

    @Column(nullable = false)
    open var data: String? = null

    @Column(name = "version")
    open var version: Int? = null

    @PrePersist
    @PreUpdate
    private fun prepareVersion() {
        if (parent != null) {
            version = parent?.getChildPosition(this)
        }
    }
}

interface MyProjection {
  // parent.id, child.id, child.version, child.data 
}

@Repository
interface ParentRepository : JpaRepository<Parent, UUID> {
  @Query("?")
  fun findWithLatestChild(id: Long?): MyProjection?
}

Is it possible to use either the JPA method naming convention or a JPQL query to do the following:

join parent and child on the parent.id.
order the children on the version (desc) and limit the result to 1.
project the properties/columns as noted in the mock projection interface above.

Update: If this isn't feasible, the following native query describes my intentions:

select p.id,c.* from parent p join child c on p.id = c.parent_id
where p.id = :parentId
order by c.version desc
limit 1

CodePudding user response：

So you can create a query like this:

@Query("select new packagename.MyProjection(c.parent.id, c.id, c.version, c.data ) "  
       "from Child where c.parent.id = :parentId order by c.version desc")
fun findWithLatestChild(id: Long?, pageable: Pageable): MyProjection?

And then call it with a PageRequest to limit the result to 1

findWithLatestChild(PageRequest.of(0,1));