Sample PetaPoco Page<T> Use Case for Pull Request to support Custom Count and Page SQL

SamplePetaPocoPageCustomSql.cs

public Page<SampleOption> GetAvailableSampleOptionsFor(int pageNumber, int pageSize, Guid companyId, Guid requirementId, string whereClause, string searchText, string orderBy)
{
    // Build our own Paging Count(*) query to use
    var countQuery = Sql.Builder.Append(@"
            WITH pcr(Id, ParentSampleGroupId, GroupLevel) AS
                    (SELECT cg.Id, cg.ParentSampleGroupId, 0 as GroupLevel
                    FROM SampleGroup cg INNER JOIN SampleWidgetGroupLimit l ON l.SampleGroupId = cg.Id
                    WHERE l.SampleWidgetId = @1
                    union all
                    select np1.Id, np1.ParentSampleGroupId, GroupLevel + 1
                    from SampleGroup np1 inner join pcr ON pcr.ParentSampleGroupId = np1.Id),
            data AS (
                    SELECT co.*
                    FROM SampleOption co INNER JOIN SampleOptionGroupLimit l on l.SampleOptionId = co.Id
                    INNER JOIN pcr ON pcr.Id = l.SampleGroupId
                    WHERE co.OrgId = @0 AND co.Id not in (select ro.SampleOptionId from WidgetOption ro where WidgetId = @1)
                    UNION ALL
                    SELECT gco.*
                    FROM SampleOption gco
                    WHERE (gco.OrgId IS NULL OR (gco.OrgId = @0 AND (SELECT OrgId FROM SampleWidget WHERE Id = @1) IS NULL))
                    AND gco.Id not in (select gro.SampleOptionId from WidgetOption gro where WidgetId = @1))
            SELECT COUNT(*) FROM data co", companyId, requirementId);

    if (!string.IsNullOrEmpty(whereClause) && whereClause.Contains("@0") && !string.IsNullOrEmpty(searchText))
        countQuery.Append("WHERE (" + whereClause + ")", "%" + searchText + "%");

    // Build our own Paging Results query to use
    var query = Sql.Builder.Append(@"
    WITH pcr(Id, ParentSampleGroupId, GroupLevel) AS
        (SELECT cg.Id, cg.ParentSampleGroupId, 0 as GroupLevel
        FROM SampleGroup cg INNER JOIN SampleWidgetGroupLimit l ON l.SampleGroupId = cg.Id
        WHERE l.SampleWidgetId = @1
        union all
        select np1.Id, np1.ParentSampleGroupId, GroupLevel + 1
        from SampleGroup np1 inner join pcr ON pcr.ParentSampleGroupId = np1.Id),
    co(Id, Name, Code, OrgId, Type, Source, Active) AS (
        SELECT ico.*
        FROM SampleOption ico INNER JOIN SampleOptionGroupLimit l on l.SampleOptionId = ico.Id
        INNER JOIN pcr ON pcr.Id = l.SampleGroupId
        WHERE ico.OrgId = @0
        AND ico.Id not in (select ro.SampleOptionId from WidgetOption ro where WidgetId = @1)
        UNION ALL
        SELECT gco.*
        FROM SampleOption gco
        WHERE (gco.OrgId IS NULL 
        OR (gco.OrgId = @0
            AND (SELECT OrgId FROM SampleWidget WHERE Id = @1) IS NULL))
            AND gco.Id not in
        (select gro.SampleOptionId
        from WidgetOption gro
        where WidgetId = @1)),
    pageset(peta_rn, Id, Name, Code, OrgId, Type, Source, Active) AS (
        select ROW_NUMBER() OVER (ORDER BY " + (string.IsNullOrEmpty(orderBy) ? "Code desc" : orderBy) + @") peta_rn, * From co", companyId, requirementId);

    if (!string.IsNullOrEmpty(whereClause) && whereClause.Contains("@0") && !string.IsNullOrEmpty(searchText))
        query.Append("WHERE " + whereClause, "%" + searchText + "%");

    query.Append(") SELECT * FROM pageset WHERE peta_rn > @0 AND peta_rn <= @1", (pageNumber-1)*pageSize, pageNumber*pageSize);

    db.EnableAutoSelect = false;

    return db.Page<SampleOption>(pageNumber, pageSize, countQuery, query);
}