Description

The PR adds an endpoint to return a paginated list of all the persons with no members.

While working on this PR I also made some small changes to get_orphaned_patients:

• added an explicit ORDER_BY after discovering some inconsistent results when I included a limit but not a cursor. I also updated the tests accordingly.
• genericized schemas.PaginatedRefs so that the paginated results for both get orphaned patients and persons are the same.

Related Issues

#163

Additional Notes

I spent a good amount of time thinking about how we should execute this query to make it as efficient as possible. My working assumptions were that:

1. the patient table will be larger than the person table,
2. both tables will be large, and
3. orphaned persons will be relatively rare.

I considered two approaches, "LEFT JOIN" (which I ultimately landed on) and "NOT EXISTS", but I am open to hearing others.

LEFT JOIN approach:

SELECT p.*
FROM mpi_person p
LEFT JOIN mpi_patient pt ON pt.person_id = p.id
WHERE pt.id IS NULL

From EXPLAIN QUERY PLAN, we can see that we are scanning mpi_person, using a Bloom filter when scanning the larger patient table to more quickly eliminate rows that definitely do not match any rows in Person, and uses a covering index on mpi_patient.id, which should keep things as quick as possible.

NOT EXISTS approach:

SELECT *
FROM mpi_person p
WHERE NOT EXISTS (
    SELECT 1 
    FROM mpi_patient pt 
    WHERE pt.person_id = p.id
)

This approach is less efficient because of the subquery executes multiple times checking for matching rows in Patient for each row in Person. It would be more efficient if we add an index on Patient.person_id, but I still think LEFT JOIN is a better choice given the assumptions stated above (especially #1).

I also briefly considered a view, but given the number of updates to the Patient and Person tables, I don't think this is our best path forward.

All that said, I am open to other approaches and would love to get folks' thoughts.

<--------------------- REMOVE THE LINES BELOW BEFORE MERGING --------------------->

Checklist

Please review and complete the following checklist before submitting your pull request:

• I have ensured that the pull request is of a manageable size, allowing it to be reviewed within a single session.
• I have reviewed my changes to ensure they are clear, concise, and well-documented.
• I have updated the documentation, if applicable.
• I have added or updated test cases to cover my changes, if applicable.
• I have minimized the number of reviewers to include only those essential for the review.

Checklist for Reviewers

Please review and complete the following checklist during the review process:

• The code follows best practices and conventions.
• The changes implement the desired functionality or fix the reported issue.
• The tests cover the new changes and pass successfully.
• Any potential edge cases or error scenarios have been considered.