simplify query for all events by latest 20 "created-events"



  • Given this structure:

    CREATE TABLE notification_event (
        id                  bigserial   PRIMARY KEY,
        user_id             bigint      NOT NULL,
        notification_id     bigint      NOT NULL,
        server_timestamp    timestamptz NOT NULL,
        type                varchar(50) NOT NULL,
        data                jsonb       NOT NULL
    );
    

    and it is known that there is only a single type='created' event per notification_id.

    I want to select all events associated with the notification_id of the latest 20 type='created' events for user_id=8.

    I came up with this query, which does what I want, but it feels cumbersome to have 2 subqueries:

    SELECT * FROM notification_event
    WHERE notification_id IN (
      SELECT notification_id
      FROM (
          SELECT DISTINCT notification_id, server_timestamp
          FROM notification_event
          WHERE user_id = 8
            AND type = 'created'
          ORDER BY server_timestamp DESC
      ) x
      ORDER BY server_timestamp DESC
      LIMIT 20
    )
    ORDER BY server_timestamp DESC
    

    How can I simplify this query?



  • Yeah one level of subquery seems entirely redundant. If each notification_id can only have one type = 'created' record, then the distinct seems pointless and so does the subquery it is in.

    SELECT * FROM notification_event
    WHERE notification_id IN (
      SELECT notification_id
      FROM notification_event
      WHERE user_id = 8 AND type = 'created'
      ORDER BY server_timestamp DESC
      LIMIT 20 
    )             
    ORDER BY server_timestamp DESC;
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2