In my experience, both SQL and real-world database schema are each complex enough beasts that to ensure everything is fetched reasonably optimally, you either need tons of entity-specific (i.e. not easily interface-able) methods for every little use case, or you need to expose some sort of builder, at which point why not just use the query builder you're almost certainly already calling underneath?

Repository patterns are fine for CRUD but don't really stretch to those endpoints where you really need the query with the two CTEs and the four joins onto a query selecting from another query based on the output of a window function.