Typeorm MINUS operator

I’m trying to do a set difference in SQL. I’ve discovered the MINUS operator, which seems to do exactly what I need: https://www.geeksforgeeks.org/sql-minus-operator/

I’m trying to figure out if there’s a way to use this operator via typeorm. I’m currently using connection.getRepository().createQueryBuilder(), and I don’t see any methods on the resulting query builder that look like they correspond to the MINUS operator.

Extra context on what I’m trying to do: I have a Request table/entity, and each request has a field that specifies an internal resource that the request is related to, let’s call it target_id. I want to find all the Requests older than 1 month, but only where there are no other requests on the same target_id that are newer than 1 month.

EDIT: Actually, thinking about this more, I’m not even sure the MINUS operator does exactly what I want either. I could write something like SELECT * from request WHERE request.updated_at < :deadline MINUS SELECT * from request WHERE request.updated_at > :deadline, but the problem is that that’s always just going to return the result of the first SELECT since there will never be any exact overlap between the two result sets. What’s missing is a way to compare the two result sets and find the ones that share a target_id and remove the results from the first set that share a target_id with any results in the second set. I assume there’s probably some way to do this with a JOIN of some kind, but it’s beyond my abilities with SQL. Any guidance here would be much appreciated!

Answer

Typeorm doesn’t seem to support MINUS directly, but you can achieve the same effect in this case by using a subquery:

SELECT *
FROM request
WHERE request.updated_at < :deadline
    AND target_id NOT IN (
        SELECT target_id
        FROM request
        WHERE requeted.updated_at > :deadline
    )

Advertisement

Answer