Comments (16)
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?
from atlas.orm.
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.
from atlas.orm.
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.
from atlas.orm.
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.
from atlas.orm.
@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.
from atlas.orm.
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.
from atlas.orm.
(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.)
from atlas.orm.
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.
from atlas.orm.
@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:
- having a request-independent query layer, which broker between app and datastore
---- this is basically out of scope for the package, so not this - 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
from atlas.orm.
@yespire yes, see http://php.net/manual/en/pdostatement.nextrowset.php .
from atlas.orm.
@harikt
Great, it means that it is doable
Thanks for sharing, I wasn't aware of the function
from atlas.orm.
@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
from atlas.orm.
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
from atlas.orm.
@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?
from atlas.orm.
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.
from atlas.orm.
Fixed by 3642fce
from atlas.orm.
Related Issues (20)
- Cached Queries HOT 8
- The name of the static method 'new' in the Atlas class seems to generate a parse error in third party libraries like phpDocumentor HOT 14
- joinWith doesn't work when specifying direction
- On updates, 1 and true and not being treated as the same for bit/boolean columns and values HOT 1
- Cannot delete all records in a many-to-many HOT 4
- Wrong parameters for PDOException? HOT 1
- Updating Many to Many?
- Postgres: problem when using uuid type field HOT 3
- Question: how to use raw insert values in ORM HOT 3
- Support for read-only MySQL generated columns
- Help: how to use advanced ORM feature HOT 5
- Help: Only the first result in a select includes data from related records HOT 7
- [Help Required] Getting Atlas\Mapper\Exception when trying to fetch a record from SQLite HOT 3
- How to use PHP-DI PSR 11 and Repository HOT 1
- Does Atlas.Orm suffer of the problem of useless order by on some ORM prefetches ?
- Row name for Exception::rowNotMapped() and Exception::rowAlreadyMapped() HOT 1
- orderBy missing HOT 1
- Issue with character encoding HOT 12
- Relationships manyToOne NULL HOT 8
- Missing get PDO connection from Atlas ORM HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from atlas.orm.