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 与会者和一个简单的时间戳日期.我怀疑我需要和 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屋！