title: SQL Query to select lockers with room for package
body: <p>I am building a smart locker reservation system with the following Postgres tables:</p>
  
  <pre><code>CREATE TABLE lockers (
    id serial primary key
  )
  
  CREATE TABLE doors (
    id serial primary key,
    locker_id integer NOT NULL,
    size integer
  );
  
  CREATE TABLE packages (
    id serial primary key,
    locker_id integer NOT NULL,
    size integer
  );
  </code></pre>
  
  <p>A package can be reserved to a locker by setting the <code>locker_id</code> column of the package. The packages will eventually get a <code>door_id</code> when they are delivered to the locker, but that is out of the scope of this question. For now, I am just interested in the reservations, and this setup gives me a lot of flexibility (e.g. if a small package is reserved to a locker that has a single large door at the time of reservation, but the locker has smaller doors available at the time of delivery, I don't need to rewrite a <code>door_id</code>. I simply just assign it to the smallest possible door). </p>
  
  <p>Everything is working perfectly, but now I want to write a query that can select all lockers that have room for a new package with a given size. My problem is that I cannot just do a <code>JOIN</code> to select empty doors because the packages only know about the locker. For each locker, I basically need to do something like <code>Find the smallest possible door for each package, and see if the new package fits in any of the remaining doors</code>. I can easily write this in JavaScript like this:</p>
  
  <pre><code>const canFitPackage = (reservations, doors, newPackage) =&gt; {
    const sortedReservations = reservations
      .slice()
      .sort((a, b) =&gt; a.size - b.size)
      .reverse();
    const sortedDoors = doors.slice().sort((a, b) =&gt; a.size - b.size);
  
    for (let i = 0; i &lt; sortedReservations.length; i++) {
      const res = sortedReservations[i];
      for (let j = 0; j &lt; sortedDoors.length; j++) {
        const door = sortedDoors[j];
        if (door.size &gt;= res.size) {
          sortedDoors.splice(j, 1);
          break;
        }
      }
    }
    return sortedDoors.some(door =&gt; door.size &gt;= newPackage.size);
  };
  </code></pre>
  
  <p>I have spent days trying to figure out how to do this in SQL, but so far I'm out of luck. I am wondering whether this really can be done in a SQL call or whether I need to write a Postgres function. Any help appreciated.</p>
  
  <p><strong>Update</strong></p>
  
  <p>After hours of trying different things, I have come up with this SQL query which does it for me. I am sure this is slow and ineffective, so any input and tips appreciated.</p>
  
  <pre><code>WITH 
  locker_doors AS (
    SELECT * FROM doors 
    --ADD INNER JOIN ON LOCKER 
    --ADD WHERE CLAUSE ON LOCKERS
  ),
  all_combinations AS (
    SELECT locker_doors.id AS door_id, locker_doors.size AS door_size, locker_doors.locker_id, packages.id AS package_id, packages.size AS package_size
    FROM locker_doors 
    JOIN packages ON locker_doors.locker_id = packages.locker_id AND locker_doors.size &gt;= packages.size
    ORDER BY packages.size DESC, locker_doors.size ASC
  ), 
  distinct_doors AS (
    SELECT DISTINCT ON (door_id) * FROM all_combinations
  ), 
  package_placements AS (
    SELECT DISTINCT ON (package_id) * FROM distinct_doors
  )
  SELECT DISTINCT ON (locker_id) locker_id 
  FROM locker_doors 
  WHERE id NOT IN (SELECT door_id FROM package_placements)
  </code></pre>