Semi-complicated doctrine query

I have a query to write that's a bit too complicated for me to figure out. Help would be appreciated. My table is, in 开发者_运维技巧essence:

Table: Foo
Columns: id
         timestamp
         bar1
         bar2
         bar3

Bar1, bar2, and bar3 are not unique for each record. In fact, the combination of the 3 specifically relates multiple records. For example:

0 | 2011-01-01 00:01:01 | 100 | 5 | 'Hello'
1 | 2011-01-01 12:12:00 | 100 | 5 | 'Hello'
2 | 2011-01-01 07:43:00 | 101 | 8 | 'Monkey'
3 | 2011-01-01 17:46:08 | 102 | 9 | 'Cat'
4 | 2011-01-01 23:15:00 | 100 | 5 | 'Hello'
5 | 2011-01-01 10:00:00 | 100 | 6 | 'Goodbye'

Records 0, 1, and 4 are related while 2, 3, and 5 are unique.

With that in mind I would like to find all records with bar1 == 100, bar2 == 5, and bar3 == 'Hello', IF all records in the group have a timestamp <= the current time OR one of the records in the group has a timestamp <= to the current time - 30 minutes.

Hopefully that makes sense. If it doesn't, just let me know and I'll do my best to clarify.

Edit: I was hoping that having just bar would be sufficient for an example, but in reality my actual table has multiple columns which must match to indicate a record is related. I've update the example accordingly.

---

If I understand your question correcty, you WANT all records for (bar1, bar2, bar3) = (100, 5, 'Hello') and you HAVE a certain current_time as the only one input criteria.

As a first step I rephrase the two conditions: In order to retrieve the members of a group the group's largest timestamp must be in the past (your first condition) or the group's smallest timestamp must be at least 30 minutes old (equivalent to your second condition).

So you can view the group and its timestamps with this query:

    SELECT bar1, bar2, bar3, max(timestamp) group_end, min(timestamp) group_start 
        FROM foo 
        GROUP BY bar1, bar2, bar3;

Adding the filters: (note the special syntax of psql for \set and : - this is for testing only)

    \set current_time '''2011-01-01 17:00:00'''
    SELECT bar1, bar2, bar3, max(timestamp) group_end, min(timestamp) group_start 
        FROM foo 
        GROUP BY bar1, bar2, bar3 
        HAVING max(timestamp)  <= :current_time OR min(timestamp) +'30min' <= :current_time

This will return nothing for current_time = 2011-01-01 00:31:00 because no group has finished and no unfinished group id old enough. After that the (100, 5, Hello) group is returned due to the OR clause and the other group are returned while further increasing current_time.

So far so good - the last piece is to retrieve the members of all found groups:

    SELECT * FROM foo WHERE (bar1, bar2, bar3) in (
        SELECT bar1, bar2, bar3 FROM foo 
        -- add WHERE clause with constraints based on bar1, bar2, bar3 here
        GROUP BY bar1, bar2, bar3 
        HAVING max(timestamp)  <= :current_time OR min(timestamp) +'30min' <= :current_time
    )
    -- add further constraints here

If my first assumption about your input paramters are wrong, you could of course add filter to every suitable step of the query. Filtering in earlier steps will be more performant of course. I have added suitable marker comments.

---

I'm not very familiar with postgresql, but here is an answer in T-SQL which should return the results you are looking for. I don't think it uses any commands that are unavailable in postgresql but I'm not certain. Also, it might choke on the DISTINCT in the sub-selection, if so just remove it as it isn't necessary. Good luck.

SELECT *
FROM Foo
WHERE 
(
bar IN (SELECT bar 
        FROM (SELECT bar, MAX(timestamp) As HighestTime FROM Foo GROUP BY bar) HT 
        WHERE HT.HighestTime <= GETDATE())
OR bar IN (SELECT DISTINCT bar FROM Foo WHERE timestamp <= DATEADD(mm, -30, GETDATE())
) 
AND
bar = 100

The first "bar in" uses a sub-selection to get only those bar numbers which have ALL timestamps lower than or equal to the current datetime by simply checking the largest one.

The second "bar in" uses a sub-selection to get any bar numbers which have at least 1 record with a timestamp lower than or equal to the current time - 30 minutes.

The "bar =" just limits it to a specific bar number.