Select clients that that don't have an event after a certain date-CodePudding

I have two tables involved in this situation:

(apologies if this is not the best way to give the structure, not really sure)

tools_events

COLUMN_NAME	DATA_TYPE
event_id	int
event_type	varchar
event_code	varchar
event_value	varchar
event_user	int
event_client	int
event_timestamp	datetime

tools_clients

COLUMN_NAME	DATA_TYPE
client_id	int
client_name	varchar
client_status	int
client_developer	int
client_designer	int
client_manager	int
client_location	varchar
client_traffic	int
client_implementations	int
client_md_split	int
client_skip_approval	int
client_isolated_testing	int
client_platform	varchar
client_primary_metric	varchar
client_clickup_id	varchar
client_live_tests	int

The events table obviously stores events that are occurring throughout a day, and clients contains a list of clients.

There is a particular event I am looking for the lack of. For this particular event the event_type would be taskUpdated and event_code would be needs approval (dev).

Currently I have a statement put together that will pull all clients that have gotten an event logged this week which seems to be working well, albeit extremely slow:

SELECT t1.event_id, t1.event_user, t1.event_client, t1.event_timestamp
FROM tools_events 
as t1 
WHERE t1.event_id = (
  SELECT t2.event_id 
  FROM tools_events 
  as t2 
  WHERE t2.event_type = 'taskUpdated' 
    AND t2.event_value = 'needs approval (dev)' 
    AND t2.event_client = t1.event_client 
    AND t2.event_timestamp >= '2022-04-04 00:00:00' 
  ORDER BY t2.event_timestamp DESC LIMIT 1
)

What I'm looking for is a way to get the inverse of this. Short of pulling this list into PHP and cross referencing a full list of the clients to see which ones aren't there, I am unsure of how to tackle this in a SQL query.

On the other end, it seems that just getting a list of events that match the initial criteria seems to run pretty much instantly so perhaps it would be more efficient to just pull those and do all of the logic in PHP? My understanding as I am learning more in depth SQL is that oftentimes SQL is more efficient if you can optimize the query correctly which I don't think I am.

Any assistance in this even if it's a point in the right direction is greatly appreciated.

CodePudding user response：

A simple way to negate a set of where clause predicates is to use NOT(...)

SELECT
      t1.event_id
    , t1.event_user
    , t1.event_client
    , t1.event_timestamp
FROM tools_events AS t1
WHERE NOT (
        t1.event_type = 'taskUpdated'
        AND t1.event_value = 'needs approval (dev)'
        )
    AND t1.event_timestamp >= '2022-04-04 00:00:00'
ORDER BY t1.event_timestamp DESC LIMIT 1

and your existing query could be simplified too:

SELECT
      t1.event_id
    , t1.event_user
    , t1.event_client
    , t1.event_timestamp
FROM tools_events AS t1
WHERE t1.event_type = 'taskUpdated'
    AND t1.event_value = 'needs approval (dev)'
    AND t1.event_timestamp >= '2022-04-04 00:00:00'
ORDER BY t1.event_timestamp DESC LIMIT 1

You do not specify which database or version, but if your db/version support window functions such as row_number() then you might be able to get both records in one query like this:

SELECT
      event_id
    , event_user
    , event_client
    , event_timestamp
FROM (
    SELECT 
          event_id
        , event_user
        , event_client
        , event_timestamp
        , row_number() OVER (
            PARTITION BY CASE WHEN event_type = 'taskUpdated'
                    AND event_value = 'needs approval (dev)' THEN 1 ELSE 0 END
            ORDER BY event_timestamp DESC
            ) AS rn
    FROM tools_events
    WHERE event_timestamp >= '2022-04-04 00:00:00'
    ) AS d
WHERE rn = 1

this divdes the data into 2 "partitions" and for each of those the row with the latest event timestamp will get a row number of 1. So both wanted rows are returned in one query.

CodePudding user response：

Instead of Database query you have to handle this using PHP