How to handle pagination queries properly with mongodb and php?

Question

Am I doing this right? I went to look at some old PHP code w/ MySQL and I've managed to get it to work, however I'm wondering if there's a much "cleaner" and "faster" way of accomplishing this.

First I would need to get the total number of "documents"

$total_documents = $collection->find(array("tags" => $tag, 
        "seeking" => $this->session->userdata('gender'), 
        "gender" => $this->session->userdata('seeking')))->count();

$skip = (int)($docs_per_page * ($page - 1));
$limit = $docs_per_page;
$total_pages = ceil($total_documents / $limit);

// Query to populate array so I can display with pagination

$data['result'] = $collection->find(array("tags" => $tag, 
        "seeking" => $this->session->userdata('gender'), 
        "gender" => $this->session->userdata('seeking')))->limit($limit)->skip($skip)->sort(array("_id" => -1));

My question is, can I run the query in one shot? I'm basically running the same query twice, except the second time I'm passing the value to skip between records.

-- New code ...

Ok, unless someone knows of another way to do this (if it's possible), I'm going to say it's not doable. With that said, I changed the way I run my queries through mongodb, which yielded better looking code. ;-) I was trying to minimize the trips to the DB, but oh well hopefully this doesn't take a performance hit. My other attempt was to count the number of elements in the array, but quickly found out that wouldn't work since the $limit & $skip parameters would give ITS total number of docs.

$skip = (int)($docs_per_page * ($page - 1));
$limit = $docs_per_page;

$query = array("loc" => array('$near' => array('lat' => $latitude, 'lon' => $longitute) ),
        "tags" => $tag, "seeking" => $this->session->userdata('gender'),
        "gender" => $this->session->userdata('seeking'));

$fields = array("username", "zipcode", "tags", "birth_date");
$total_documents = $collection->find($query, array("_id"))->count();
$data['result'] = $collection->find($query, $fields)->limit($limit)->skip($skip);

Answer 1

Since the result of find()->limit()->skip() is a Mongo_Cursor you don't have to execute the actual query twice.

The following should work as well :

$skip = (int)($docs_per_page * ($page - 1));
$limit = $docs_per_page;

$query = array("loc" => array('$near' => array('lat' => $latitude, 'lon' => $longitute) ),
    "tags" => $tag, "seeking" => $this->session->userdata('gender'),
    "gender" => $this->session->userdata('seeking'));

$fields = array("username", "zipcode", "tags", "birth_date");
$cursor = $collection->find($query, $fields)->limit($limit)->skip($skip);
$total_documents = $cursor->count();
$data['result'] = $cursor;

btw I first misread your question, I thought you didn't know about limit & skip.

Answer 2

Yes you are doing right. And you can run query in one shot.

Here is a paging example:

function printStudents(pageNumber, nPerPage) {
   print("Page: " + pageNumber);
   db.students.find().skip((pageNumber-1)*nPerPage).limit(nPerPage).forEach( function(student) { print(student.name + "<p>"); } );
}

Reference: Advanced Queries - MongoDB: http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-{{skip%28%29}}

Answer 3

This is what you need in the raw SQL. Not sure how you'd shoehorn it into that framework though. There's things to be said for inline queries.

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;

SELECT FOUND_ROWS();