pagination with resultset

Code:

 // A raw SQL statement
$sql = "SELECT income_id,user_id,sender_id,income_type_id,amount,income_definition,income_notes,transaction_date,status FROM user_income where (user_id = '$user_id' or sender_id = '$user_id')
UNION
SELECT outcome_id,user_id,receiver_id,outcome_type_id,amount,income_definition,income_notes,transaction_date,status FROM user_outcome where (user_id = '$user_id' or receiver_id = '$user_id')";

// Base model
$UserIncome = new UserIncome();

// Execute the query
return new Resultset(null, $UserIncome, $UserIncome->getReadConnection()->query($sql));

this is my resultset and when i want to add it on paginator its working bad (resultset return true result)

       $paginator = new \Phalcon\Paginator\Adapter\Model(
    array(
        "data"  => $account,
        "limit" => 10,
        "page"  => 1
    )
);

// sayfalanmış sonuçları al
$page = $paginator->getPaginate();

when i want to print_r page info there are results with [0] etc and whatever i do couldnt manage to pass.

Thanks



84.1k

Save the resultset into a variable:

$resultset = new Resultset(null, $UserIncome, $UserIncome->getReadConnection()->query($sql));

Then pass it to the paginator:

$paginator = new \Phalcon\Paginator\Adapter\Model(
array(
"data" => $resultset,
"limit" => 10,
"page" => 1
)
);


5.2k

Sorry to resurrect this, but wouldn't that query the entire result set each time, causing slow downs for large data sets?

Is there a way to use \Phalcon\Paginator with raw SQL (maybe with place holders for the LIMIT clause)? That would be much more efficient for large data sets.



5.2k
edited Oct '14

Thanks for the reply.

This is the query I'm trying to paginate:

SELECT DISTINCT ON (c.id)
       c.id, m.id, m.subject, m.created_at
FROM   conversations c
LEFT   JOIN messages m ON c.id = m.conversations_id
ORDER  BY c.id, m.id

I don't think Phalcon / PDO has a way of supporting DISTINCT ON, so the Model and QueryBuilder adapters are out (is that correct?), leaving the NativeArray adapter. But if I use a raw query and use the NativeArray adapter, isn't it expecting an entire result set, which it will then paginate? Is there a way to tell NativeArray that you're already handing it the slice of data (instead of the whole data set) and which page it represents?

I want to avoid reading out 10,000 rows from the database just to get 40 paginated rows for display. But I can't find a way to do that with \Phalcon\Paginator.



84.1k

QueryBuilder uses LIMIT/OFFSET clauses to avoid read the whole number of rows returned by the Query.



5.2k

I realize QueryBuilder uses LIMIT/OFFSET, but can I use QueryBuilder for this query?

SELECT DISTINCT ON (c.id) c.id, m.id, m.subject, m.createdat FROM conversations c LEFT JOIN messages m ON c.id = m.conversationsid ORDER BY c.id, m.id

A Google search for:'"DISTINCT ON" QueryBuilder Phalcon' yields no results, and I haven't been able to get it to work.