How can I use the elgg pagination with my own sql-query?

Assume you have a user defined table in the elgg database, which recordfields content userguid and you would like to view the data in a elgg-module in a elgg group for example, but WITH PAGINATION.
I found no function, which converts normal recordsets for use in a view with pagination and I am not such a good coder to make my own pagination.
Is there a way to use the elgg pagination with my own sql-query?

  • Pass count, limit and offset to navigation/pagination view. From there use the offset and limit in your custom query

  • yes :-)
    thank you ismayil for your quick response
    have a good time :-)

  • for elgg-novices I describe how I solve my problem:

    on top I initialize the two variables:  
     
    $limit = max ((int) get_input ("limit", 5), 0);
    $offset = sanitise_int (get_input ("offset", 0), false);

    // found in Jeroen Dalsem Profile Manager :-)


    then I execute the sql statement:

    $query = "SELECT elgg_entity_relationships.guid_one, elgg_entity_relationships.guid_two, SUBSTRING(relationship,22) AS group_guid

    FROM elgg_entity_relationships

    GROUP BY elgg_entity_relationships.guid_one, elgg_entity_relationships.relationship, SUBSTRING(relationship,22)

    HAVING (((elgg_entity_relationships.relationship) Like 'group_certified_vote_%')) LIMIT " . $limit . " OFFSET " . $offset;

    $result_recordsets = get_data ($query);   


    and format the html-output

           

                foreach ($result_recordsets as $dataset) {

                content .= . . . .  .

    . . . . .


    then I count the recordsets for pagination

     $query = "SELECT COUNT( * ) as total FROM ( SELECT elgg_entity_relationships.guid_one FROM elgg_entity_relationships GROUP BY elgg_entity_relationships.guid_one, elgg_entity_relationships.relationship HAVING (((elgg_entity_relationships.relationship) LIKE 'group_certified_vote_%'))

    ) derivedTable";

    $result =  get_data ($query);

    $recordset_count = $result[0]->total;

     

    then I add the pagination:

    $content .= elgg_view ("navigation/pagination", array ("offset" => $offset, "limit" => $limit, "count" => $recordset_count));


    and then I display the html in a group widget for instance:

                     echo elgg_view ('groups/profile/module', array (

                        'title' => elgg_echo ('audit:group module:group_auditors:certified'),

                        'content' => $content,

                        'all_link' => Null,

                        'add_link' => Null,

                    ));


    Note:

    best practice is to use the functions:
    elgg_list_entities_from_relationship ($options);
    or
    elgg_get_entities_from_relationship ($options);
    with that functions pagination is much more easier :-)

    but in my case I (mis)used the field "relationship" as a fourth filter of the relationship table
    and I had to make the query with a LIKE clause

    thanks again to Ismayil Khayredinov