JPA为每个项目选择最新实例 [英] JPA Select latest instance for each item

问题描述

假设我有一个会议实体。每次会议都有一位与会者和一个会面日期。在我的会议表中，每个与会者可能会有多个会议，每个会议的日期各不相同。我需要一个JPA查询，它只为所有与会者选择最新的会议。例如，如果我的表看起来像这样

Let's say I have a Meeting entity. Each meeting has a single attendee and a meeting date. Within my meeting table I may have multiple meetings for each attendee, with different dates for each. I need a JPA query that will select only the latest meeting for all attendees. For instance, if my table looks like this

Meeting ID | Attendee ID | Meeting Date
1          | 1           |  6/1/2011
2          | 2           |  6/1/2011
3          | 1           |  6/6/2011
4          | 3           |  6/6/2011

我的结果应该是

Meeting ID | Attendee ID | Meeting Date
2          | 2           |  6/1/2011
3          | 1           |  6/6/2011
4          | 3           |  6/6/2011

对postgres使用JPA 2。会议有1-1参加者和一个简单的时间戳日期。我怀疑我需要做一个小组by和max（blah）并且可能加入到我自己，但我不确定最好的方法来解决这个问题。

Using JPA 2 against postgres. Meeting has 1-1 to attendee and a simple timestamp date. I suspect I'm going to need to do a group by and max(blah) and maybe a join to myself, but I'm not sure of the best way to approach this.

更新：
在晚上玩这个之后，我仍然没有可接受的JPQL解决方案。以下是我到目前为止的信息：

Update: After spending the evening playing with this, I still do not have an acceptable JPQL solution to this. Here is what I have so far:

select m from Meeting m 
where m.meetingDate in 
    ( select max(meet.meetingDate) 
      from Meeting meet group by meet.attendee )

我有其他与此问题无关的其他条件，例如由与会部门过滤等等。这有效的唯一原因是因为我们将会议日期跟踪到第二（或更精细），并且在同一时间召开两次会议的可能性很小。我们正在为它们添加一些Java内容，以便只为每个与会者提供最后一次会议，以防我们同时获得两个会议，但这是一个非常糟糕的解决方案。在查询中获取所有内容真的不应该太难，但我还没弄明白。

I have various other conditions that are not relevant to this question, like filtering by attendee department and whatnot. The only reason this works is because we are tracking meeting date to the second (or finer) and the chance that there will be two meetings at exactly the same time is minimal. We are putting some java stuff around it to keep only hte last meeting for each attendee just in case we do get two at the same time, but that's a pretty crappy solution. It really shouldn't be too difficult to get it all in a query, but I have yet to figure it out.

Update2：添加sql标签，因为如果我需要使用sql创建一个视图并创建一个JPA对象来映射到视图我就可以了。

Update2: Adding sql tag because if I need to use sql to create a view and create a JPA object to map to the view I'm ok with that.

推荐答案

我想我已经得到了这个查询。

I think I've got it with this query.

select m from Meeting m 
    where m.meetingDate = 
        (select max(m1.meetingDate) 
            from Meeting m1 
            where m1.attendee = m.attendee )
    and not exists 
        (select m2 from Meeting m2 
            where m2.attendee = m.attendee 
            and m2.meetingDate > m.meetingDate)

这篇关于JPA为每个项目选择最新实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！