MySQL is filtered away from self-reference

TABLE event (
 `id` varchar(32) NOT NULL,
 `start` datetime,
 `end` datetime,
 `derivedfrom_id` varchar(32),
 `parent_id` varchar(32) NOT NULL
)

 SELECT id, start, parent_id
FROM event
WHERE parent_id in ()
 AND start >='some date'
LIMIT x

SELECT id, start, parent_id
FROM event
WHERE parent_id in ()
 AND start >='some date' 
 AND (/* the part below duplicates the previous conditions */
 derivedfrom_id is not null
 or id not in (
 SELECT derivedfrom_id
 FROM event
 WHERE parent_id in ()
 AND start >='some date' 
 AND derivedfrom_id is not null
 )
 )
LIMIT x

│ *ID* │ *DERIVEDFROM_ID* │ *PARENT_ID* │ *START*
├─────┼─────────────────┼──── ─────────┼────────────────────────
│ 100 │-│ A │ 2014-11-18 15:00
 │ 101 │ 100 │ B │ 2014-11-18 15:00
│ 150 │-│ A │ 2014-11-20 08:00

SELECT e.*
FROM `event` e #'e' from'event'
 LEFT JOIN `event` d #'d' from'derived'; `LEFT JOIN` gets ALL entries from `e`
 ON e.id = d.derivedfrom_id # match an event `e` with all those `d` derived from it
WHERE d.id IS NULL # keep only events `e` without de rived events `d`
;

We have a table that contains events for regular queries (such as The start and end time in the calendar event):

TABLE event (
 `id` varchar(32) NOT NULL,
 `start `datetime,
 `end` datetime,
 `derivedfrom_id` varchar(32),
 `parent_id` varchar(32) NOT NULL
)

> parent_id points to a calendar table that provides some other information.
>Some events are created by another event, so there is a reference to the “origin” event in the derivedfrom_id column.

Retrieve a group For events, we usually query by date (start/end) and calendar (parent_id), and limit the number of results by restricting pagination.

The problem we are facing now is: sometimes we need to compare the user’s related Events are combined into a single representation. So we do normal queries

SELECT id, start, parent_id
FROM event
WHERE parent_id in ()
 AND start >='some date'
LIMIT x

…then filter out the original event, because the derivatives have different information and reference their source anyway .

As you may have seen (earlier than us), we limit before filtering, so we receive a set of events with a smaller base than we initially expected, i.e. the number of results is lower than the filtering After the “x”.

The only thing I can think of is to copy the query and make sub-selects:

SELECT id, start, parent_id
FROM event
WHERE parent_id in ()
 AND start >='some date'
 AND (/* the part below duplicates the previous conditions */
 derivedfrom_id is not null
 or id not in (
 SELECT derivedfrom_id
 FROM event
 WHERE parent_id in ()
 AND start> ='some date'
 AND derivedfrom_id is not null
 )
 )
LIMIT x

But I hardly believe that this is the only way to achieve this goal Method. Especially, because our query is much more complicated.

Is there a better way?

Sample data

(as requested by the comment)

In view of the following three events:

│ *ID* │ *DERIVEDFROM_ID* │ *PARENT_ID* │ *START*
├─────┼─────────────────┼──── ─────────┼────────────────────────
│ 100 │-│ A │ 2014-11-18 15:00
 │ 101 │ 100 │ B │ 2014-11-18 15:00
│ 150 │-│ A │ 2014-11-20 08:00

…and the limit is 2, I want to get Event 101 and 150.

Instead, use the current method:

>A query limited to 2 will result in event 100 and 101
>after filtering, event 100 is discarded, The only remaining event is 101

Explanation about the expected answer

The SQL above was actually generated from a Java application using JPA. My current solution is to generate a where clause and copy it. If there is a specific JPA specific thing, I would appreciate any pointers.

Try this:

SELECT e.*
FROM `event` e #'e' from'event'
 LEFT JOIN `event` d #'d' from'derived '; `LEFT JOIN` gets ALL entries from `e`
 ON e.id = d.derivedfrom_id # match an event `e` with all those `d` derived from it
WHERE d.id IS NULL # keep only events `e` without derived events `d`
;

LEFT JOIN selects all events from e and compares them with those derived from them Event d is paired. It ensures that all entries of e have a chance to be selected, regardless of whether they have derived events or not. The WHERE clause only retains events in e that have no derived events. It retains derived events and original events without derived events, but deletes Those original events with derived events.

Add other WHERE conditions to the fields of table e as needed, use the LIMIT clause, stir evenly, and cool.

< /p>