Feature Idea: API Pagination, Sorting and Filtering

[Lobosque]

Implement pagination, sorting and filtering for the APIs when querying data.

[kgrzybek]

I implemented pagination example:

public async Task<List<MeetingGroupProposalDto>> Handle(GetMeetingGroupProposalsQuery query, CancellationToken cancellationToken)
{
    var connection = _sqlConnectionFactory.GetOpenConnection();

    var parameters = new DynamicParameters();
    var pageData = PagedQueryHelper.GetPageData(query);
    parameters.Add(nameof(PagedQueryHelper.Offset), pageData.Offset);
    parameters.Add(nameof(PagedQueryHelper.Next), pageData.Next);

    string sql = "SELECT " +
                 $"[MeetingGroupProposal].[Id] AS [{nameof(MeetingGroupProposalDto.Id)}], " +
                 $"[MeetingGroupProposal].[Name] AS [{nameof(MeetingGroupProposalDto.Name)}], " +
                 $"[MeetingGroupProposal].[ProposalUserId] AS [{nameof(MeetingGroupProposalDto.ProposalUserId)}], " +
                 $"[MeetingGroupProposal].[LocationCity] AS [{nameof(MeetingGroupProposalDto.LocationCity)}], " +
                 $"[MeetingGroupProposal].[LocationCountryCode] AS [{nameof(MeetingGroupProposalDto.LocationCountryCode)}], " +
                 $"[MeetingGroupProposal].[Description] AS [{nameof(MeetingGroupProposalDto.Description)}], " +
                 $"[MeetingGroupProposal].[ProposalDate] AS [{nameof(MeetingGroupProposalDto.ProposalDate)}], " +
                 $"[MeetingGroupProposal].[StatusCode] AS [{nameof(MeetingGroupProposalDto.StatusCode)}] " +
                 "FROM [meetings].[v_MeetingGroupProposals] AS [MeetingGroupProposal] " +
                 "ORDER BY [MeetingGroupProposal].[Name]";

    sql = PagedQueryHelper.AppendPageStatement(sql);

    var meetingGroupProposals = await connection.QueryAsync<MeetingGroupProposalDto>(sql, parameters);

    return meetingGroupProposals.AsList();
}

And unit tests for PagedQueryHelper:

[TestFixture]
public class PagedQueryHelperTests
{
    [TestCase(1, 5, 0, 5)]
    [TestCase(3, 10, 20, 10)]
    [TestCase(null, 20, 0, 20)]
    [TestCase(5, null, 0, int.MaxValue)]
    [TestCase(null, null, 0, int.MaxValue)]
    public void PagedQueryHelper_GetPageData_Test(int? page, int? perPage, int offset, int next)
    {
        IPagedQuery query = new TestQuery(page, perPage);
        var pageData = PagedQueryHelper.GetPageData(query);

        Assert.That(pageData, Is.EqualTo(new PageData(offset, next)));
    }

    private class TestQuery : IPagedQuery
    {
        public TestQuery(int? page, int? perPage)
        {
            Page = page;
            PerPage = perPage;
        }

        public int? Page { get; }

        public int? PerPage { get; }
    }
}

[GianlucaLocri]

Hi, wit this implementation how can I know how many items there are in total?