Support Anti-Joins

[WebFreak001]

In some scenarios it's not possible or not recommended to extend a table with a foreign key not actually belonging in it.

E.g. imagine you have a table Reservation and a table Human. You want to create a 1-N relationship between them so that 1 Reservation can have N Humans in it. However not all Humans may have a Reservation.

Usually you'd want to express it as a field reservation of type ForeignModel<Reservation> inside of Human. However, if you have a constraint that you cannot modify the table Human (e.g. it does not make sense for it to have the field or you want to keep separation of concerns), you'd need to create third table (satellite table), which maps this relationship. Also, you might want to go with this approach if you have a lot of similar columns and want to save space on the Human table.

This satellite table would be defined like this per textbook SQL: (rorm code)

struct Satellite { // (Human - Reservation helper table)
    // the primary_key here ensures that it's a 1-N and not an N-M relationship:
    #[rorm(primary_key, on_delete = "Cascade", on_update = "Cascade")]
    pub human: ForeignModel<Human>,

    #[rorm(on_delete = "Cascade", on_update = "Cascade")]
    pub reservation: ForeignModel<Reservation>,
}

Now if I want to query all Humans without Reservations, you'd do it similar to this in SQL:

SELECT human.*
FROM human
WHERE NOT EXISTS (
    SELECT 1 
    FROM satellite 
    WHERE satellite.human_id = human.id
);

or with equivalent performance:

SELECT human.*
  FROM human
  LEFT JOIN satellite ON (satellite.human_id = human.id)
  WHERE satellite.human_id IS NULL;

However now rorm is missing features to do this:

• you can't query the full foreign table, at most you can get only the primary key or a tuple of keys (I'd like to rorm::query(db, Satellite.human) and get out the full Human struct)
• you can't do a condition on a ForeignModel to check for IS NULL when it's not optional.
• you can't join without a BackRef, but touching the table to add a BackRef is illegal per definition above (e.g. like what you might find in microservices) and I don't think you get a left join out of it

The LEFT JOIN approach would probably fit quite well in rorm, but the exact syntax is a bit tricky to define.