如何使用存储库模式处理复杂的读取（SELECT）？

Question

I've seen a lot of the $repo->findAll() or $repo->findById($id)examples, but I'm looking for how to expand on this for more complex Reads.

For example, let's say I have a datagrid that represents a SELECT query with several JOINs in it. I'm going to need to do these things:

• Sorting
• Filtering (WHERE conditions, some of which happen on the JOIN clauses of the query)
• Columns (I don't want to SELECT *, so I need to specify the fields I want)
• Limit (pagination)
• Count (I need to know the total number of rows from all pages. Perhaps I do this in a seperate repo method/query.)

I'm not sure I'm comfortable using an existing query builder package because I'm not sure how testable and database-agnostic it would be (in other words, it might be too flexible). I do know that I do NOT want to use an ORM for this project. I'm using the Data Mapper + Repository approach instead.

How would I do this using the Repository Pattern?

Answer 1

(Sometimes, I believe the "Answer" to a question involves "lowering expectations".)

I believe you are asking too much for a "Repository Pattern". There are many 3rd party software packages that attempt to isolate the user from MySQL. They generally have limitations. Often a limitation is in scaling -- they are not designed to work with huge datasets in complex ways.

Whenever I use the Repository Pattern, it seems that I am doing little more than encapsulating a one (or a few) SQL statement and putting the encapsulated method (subroutine) in a separate file. Oh, I believe in doing it. I just don't believe in magic.

Let me pick apart two of your 'requirements'. They are good for encapsulating, but not necessarily good for the Repository Pattern.

Pagination using OFFSET and LIMIT... For simple datasets, this works fine. But I watched a project melt down after they did this. They required the obvious parameters (offset and limit) and did the obvious thing (construct and execute SELECT ... OFFSET $offs LIMIT $lim). Then they built a web page that had 126,000 "pages" worth of data. Then something did Next, Next, Next, ... until the system melted down.

The problem was depending on offset and limit instead of "Next" and "Prev", and "remembering where you left off". (I have a blog on that topic.) Note that the "solution" cannot be performed in the encapsulated routine, but involves UI changes and user expectation changes, plus code.

The other one I want to comment on is SQL_COUNT_FOUND_ROWS... So simple, so easy. But so deadly. As recently a as this week I was advising someone who's data had grown so much that he was having performance problems due to that counting technique. Many of the possible solutions involve more than can be stuck in a Repository Pattern. For example, the typical search engine long ago punted on getting the exact count and, instead, "managed the user expectations" by showing "10 items out of about 1,340,000". That, doubtless, took a lot of code in a lot of places, not just a simple enhancement to one SQL statement. It probably took multiple servers.

So, encapsulate - Yes. Repository Pattern - only somewhat. And become an expert in raw SQL.