pagination with resultset

Question

pagination with resultset

geass Oct '13

Created Oct '13	Last Reply Feb '14	Replies 6	Views 2615	Votes 1

geass 39.3k

Oct '13

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

1

Phalcon · Answer 1 · 2013-11-01T11:24:45-07:00

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
)
);

Metzen2k · Answer 2 · 2014-02-27T10:20:23-07:00

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.

Phalcon · Answer 3 · 2014-02-27T10:31:41-07:00

@Metzen2k Check Phalcon\Mvc\Model\Query\Builder https://docs.phalcon.io/en/latest/reference/pagination.html#data-adapters

Metzen2k · Answer 4 · 2014-02-27T10:59:45-07:00

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.

Phalcon · Answer 5 · 2014-02-27T11:09:24-07:00

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

Metzen2k · Answer 6 · 2014-02-27T11:49:51-07:00

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.created_at FROM conversations c LEFT JOIN messages m ON c.id = m.conversations_id 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.