tawman/PetaPoco — pull request #1 by tawman (2011-12-27 21:53:42+00:00)
Title: Page<T> Support for SELECT ROW_NUMBER() PARTITION BY Queries - Regex rxOrderBy is too aggressive

The Regex used by the Page<T> to isolate the ORDER BY clause for query re-write is too aggressive when multiple ORDER BY clauses are present for a partitioned query. The current Regex will remove the PARTITION BY ORDER BY causing a SQL runtime error.

The following pull request changes the rxOrderBy to search Right to Left and on the Regex.Replace it will only remove the first occurrence of the ORDER BY clause found.

The following Page query is looking for only the most recent report generated on a given date. The query casts the report timestamp to date and filters on the most recent for that given date:

```C#
public Page GetReportsForMember(int pageNumber, int pageSize, string userName, Guid complianceGroupId, DateTime startDate, DateTime endDate, string orderBy)
{
var query = Sql.Builder
.Append("SELECT cr.*")
.Append("FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CAST(cr.ReportDate AS DATE) ORDER BY ReportDate Desc) as samp_rn, cr.*")
.Append("FROM ComplianceReport cr")
.Append("INNER JOIN ScheduledReport sr ON cr.ComplianceGroupId = sr.ComplianceGroupId")
.Append("WHERE (sr.UserName = @0)", userName)
.Append("AND (sr.ComplianceGroupId = @0)", complianceGroupId)
.Append("AND (cr.Published = 1)");

if (startDate != DateTime.MinValue)
query.Append("AND (cr.ReportDate >= @0 AND cr.ReportDate <= @1)) cr", startDate, endDate);

query.Append("WHERE samp_rn = 1");
query.Append("ORDER BY " + orderBy);

return CmsDatabase.Page(pageNumber, pageSize, query);
}
```

This approach will fail if the inner ORDER BY is not guarded by the final ORDER BY. I was trying to avoid changing the Regex pattern itself, but I think a better approach would be to check for an ORDER BY not present in the context of a ROW_NUMBER() usage.