our database setup involves tables with one-to-many and many-to-many relationships. we need to correctly count the total number of distinct entries that match the query criteria across these related tables. our current implementation relies on the per_page limit and offset query params to filter out the data. however, the way these two parameters are used seem to be incompatible with the assumptions made in handle_pagination() helper function.
the problem becomes evident when working with /clips endpoint which requires joining clip, clipproject and project tables. the pagination is not accurately reflecting the total count of distinct entries, leading to discrepancies in the total pages and entries reported versus the actual data fetched.
our database setup involves tables with one-to-many and many-to-many relationships. we need to correctly count the total number of distinct entries that match the query criteria across these related tables. our current implementation relies on the per_page limit and offset query params to filter out the data. however, the way these two parameters are used seem to be incompatible with the assumptions made in
handle_pagination()helper function.the problem becomes evident when working with /clips endpoint which requires joining clip, clipproject and project tables. the pagination is not accurately reflecting the total count of distinct entries, leading to discrepancies in the total pages and entries reported versus the actual data fetched.
Originally posted by @andersy005 in https://github.com/carbonplan/offsets-db-api/issues/91