Pagination

[atlasphp]

Pagination

[pmjones]

All of SqlQuery Select is exposed, so the page() and paging() methods should be available (though they may not be described in the README). Have you discovered that they are not there?

[harikt]

Have you discovered that they are not there?

No. I was just going through docs and doing a quick search on the code to see if there is any reference.
I will come again when I start playing .

Thank you.

[harikt]

Hi @pmjones ,

I would like to reopen this due to a few doubts / questions / suggestions .

I was trying to make use of

SELECT SQL_CALC_FOUND_ROWS id, title, body FROM <table> LIMIT 20;
SELECT FOUND_ROWS();

Is there a way we can achieve the total count like the above.

Currently what I was forced to do is run two queries as

$this->query = $atlas->select(Mapper:class)->page(1)->paging(20);
$result = $this->query->fetchRecordSet();

$query = clone $this->query;

foreach($atlas->mapper(Mapper:class)->getTable()->getColNames() as $column) {
    $query->removeCol($column);
}
$query->limit(1);
$total = $query->cols(['COUNT(id)'])->fetchValue();

A few things you may note here, are we need a removeCols in sqlbuilder or a way built into Atlas.Orm for easy way to remove column and get the total count.

Any suggestions / thoughts ?

Do you think it will be good if we have a method getTotal on mapper which can do the above functionality or something better?

Thank you.

[pmjones]

Good question. Let me think about this a while. Whatever the solution, if it's at the Atlas ORM level, it'll need to be portable across different databases.

[harikt]

@pmjones true. I don't know more on postgres but from some of the older post I don't think SQL_CALC_FOUND_ROWS is available.

[pmjones]

Because the Atlas select object proxies through to Aura SqlQuery select object, you should be able to do something like this:

// for a MySql query object
$mapper = $atlas->mapper(Mapper::CLASS);
$recordSet = $mapper
    ->select()
    ->page(1)
    ->paging(20)
    ->calcFoundRows()
    ->fetchRecordSet();
$count = $mapper
    ->select()
    ->cols('FOUND_ROWS()')
    ->fetchValue();

Try that and let me know if it works for you.

[pmjones]

(Actually it might not work. Atlas will issue several queries when building related objects, and I'm pretty sure FOUND_ROWS() will operate only on the last one.)

[harikt]

oh @pmjones I wasn't aware / remembered there was calcFoundRows method. Will try it, and you may be right regarding the FOUND_ROWS() probably if Atlas have something by its own it can run this query after the query.

[yespire]

@pmjones @harikt
Is it possible for PDO to run 2 statements at once, then capture results in 2 separate variables OR as array of 2 elements ?

IF no, there are 2 options for FOUND_ROWS() to be done:

1. having a request-independent query layer, which broker between app and datastore
   ---- this is basically out of scope for the package, so not this
2. move the 2 statements into into a stored procedure, which returns result-set and row-count in one call
   ---- under this assumption: MySQL / Select / calcFoundRow() should be removed, it is not a supported by other db type anyway

[harikt]

@yespire yes, see http://php.net/manual/en/pdostatement.nextrowset.php .

[yespire]

@harikt
Great, it means that it is doable
Thanks for sharing, I wasn't aware of the function

[yespire]

@harikt @pmjones
since package may construct multiple queries when using "with" etc. How about the following idea / assume we have a multi-queries-statement ($mq):

• $mq = function prepare_mq( $mq ) --- add SELECT FOUND_ROWS(); after each query with calcFoundRows flag set
• function analyze_mq( $mq ) --- produce "plan instruction" on how to use nextrowsets + fetchall to retrieve the data into record or recordset

[yespire]

one of the optimization for app-database is to minimize the number calls/round trip to cut down latency.
Let's assume we want to manage dynamic queries from primarily App side (instead of db side with store procedures)

how about this idea:
-- a new class -- "Call"
-- through which we organize multiple queries objects, these query objects return statements + bindvalues, which are merged separately into combined_statements + combined_bindvalues
-- $call->execute() to send combined_* in one call

FOUND_ROWS feature and this seem to be related

[pmjones]

@yespire With the proposed reset*() methods in Aura.Sql 2.x, having multi-result-set support may not be needed. The original top-level query can be cloned and rebuilt for a count; e.g., in a fetchCount() method somewhere:

$counter = clone $query;
$counter->resetCols();
$counter->cols('COUNT(*)');
$count = $query->fetchValue();

Thoughts?

[harikt]

Hi @pmjones ,

The issue #26 is related to the clone. We need to have a clone method which will clone the TableSelect in MapperSelect . I will send a patch when I get some time.

Thank you.

[pmjones]

Fixed by 3642fce