We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Beginners

ORM problem

Hi.

All pdo querys ( mysql and postgre ) call to rowcount but this method dont work with MYSQL_ATTR_USE_BUFFERED_QUERY to false.

If you make a selec with 1000 rows and many columns all memory is set in pdo driver when execute the query.

execute or query, not in fetch.

so iterate it's stupid, because the driver download all data in the first.

This is a big problem, because if you have a little memory, you cant stream the rows with fetch.

example:

json_encode(fetchall)

vs

while(fetch)josn_encode(row)

the second option is good, but with MYSQL_ATTR_USE_BUFFERED_QUERY to true, it's stupid.

rowcount will must separate from excute, "I think"

If I want make one slow query and big, I can`t to use orm



12.8k

You shouldn't have a query returning 1000 rows, use offset/limit with pagination.
If you need the total count do a Model::count with the same conditions than the previous request, the query should be cached so very fast.
You can also use SQL_CALC_FOUND_ROWS / FOUND_ROWS() with MYSQL.
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows



402

sorry for duplicate post, server send me 500 error.

I do not want to know the number of lines.

I want download, one json or xml with 100, 200, 300 rows without ram consumition

1000, 100, 10, not is important.

the problem is the scability. one big row x 10 ..., x 100 this is not good.

i like phalcom, but orm and select is not good. I had to change many queries.

phalcon Orm should have the possibility to execute one model:find ( without rowncount and selec count(*) ) for iterate response.



77.7k
// setup
$di->set('db', function(){
    return new Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "<host>",
        "username" => "<user>",
        "password" => "<secret>",
        "dbname" => "<database>",
        "options" => [\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]
    ));
 });
// usage
$data = ModelClass::find();
$data->rewind();
while($data->valid()){
    // process
    $data->next();
}

In theory, this should work



402
edited May '18

In theory, but ... pdo mysql in php 7.x

[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]

pdo execute, query ... malloc all data query. so iterate it's unnecessary, it's like fetchall

[\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false]

it's required if you don't want memory over heap



125.8k

ORMs are typically not intended to be the leanest abstractions in your code. They are extremely convenient, but the tradeoff is that there is always overhead somewhere.

If memory allocation is an issue for you, I'd suggest not using the ORM - use raw sql. I believe you can get the connection with

$PDO = \Phalcon\DI::getDefault()->get('db'); // or
$RawSQLConnection = \Phalcon\DI::getDefault->get('db')->getReadConnection();


402

I do not think the same. the only difference between MYSQL_ATTR_USE_BUFFERED_QUERY true or false it's that we don't have rowsCount.

Get rowscount is not necesary in many situations, because method fetch return null when arrive to end, phalcon code don't need rowcount for work, so ¿why?

I think that it's limitation unnecesary

And select count (*) after slow query is not good idea.

ORM must be scalable, and NOW, it is not