deprecate getPDO

[item]

Hello Yuri,
you have deprecate https://github.com/espocrm/espocrm/c...c58ff986f63005

But in my 2 cents, we need getPDO
my sample is :
- patient : more than 12000 record
- care : more than 7610578 record

I know, i the past, i try use orm with result : long time result or no result (database go away).

Please, dont deprecate PDO

Kind Regards

$pdo = $this->getEntityManager()->getPDO();
$entityManager = $this->getEntityManager();
$patientList = $entityManager->getRepository('Patient')->where([
'deleted' => 0
])->find();

foreach ($patientList as $patient) {
$sql = "SELECT MAX(DATE(date_time)) as dateLastCare, MIN(DATE(date_time)) as dateFirstCare FROM care WHERE national_number='" .$patient->get('nationalNumber') ."' AND deleted='0' GROUP BY national_number;";

$sth = $pdo->prepare($sql);
$sth->execute();
$row = $sth->fetch();
if ($row){
$patient->set('dateLastCare', $row['dateLastCare']);
$patient->set('dateFirstCare', $row['dateFirstCare']);
$entityManager->saveEntity($patient, ['skipAll' => true]);
}
} 

]

[yuri]

Hi,

> i try use orm with result : long time result or no result

I don't think it's a valid argument. ORM builds same SQL queries. Currently in espo we don't have any raw sql and there's no any problem.

You can still use $sth = `$em->getSqlExecutor()->execute($sql)` if you like to use raw query. No 'prepare' though.

`getPDO` won't be removed soon, but I'd recommend to rewrite your code gradually.

I assume that in the future we may need to get rid of PDO. The main possible reason: it is not async. Also I don't encourage to use any raw SQL. That's why the method is deprecated.

Your example does not require using raw query. There's no any problem to use ORM (query builder) here.

$query = $entityManager->getQueryBuilder()
    ->select(... expression ... can be in OOP way as of v6.2.0)
    ->from(...)
    ->where(...)
    ->groupBy(...)
    ->build();

$row = $entityManager
    ->getSqlExecutor()
    ->execute($query)
    ->fetch();

$patient->set('dateLastCare', $row['dateLastCare']); 

And you have much more readable, maintainable code w/o sql injection risks.

[telecastg]

You can still use $sth = `$em->getQueryExecutor()->execute($sql)` if you like to use raw query. No 'prepare' though.

I tried that calling format using a raw SQL string but got error:
Argument 1 passed to Espo\ORM\QueryExecutor::execute() must implement interface Espo\ORM\QueryParams\Query, string given
could you tell me what is the correct way to call a raw SQL string for execution using the getQueryExecutor() method please ?

Thanks

[item]

Thanks Yuri...
I understand not all but your sample give me the way to resolve

Hello telecastg
I use this in a jobs.. and not problem on 6.1.7
if you send me your sql.. i can try

Regards

$query = $em->getQueryBuilder()
->select()
->from( 'Care')
->select([
'MAX:(dateTime)',
'MIN:(dateTime)'
])
->where( [
'nationalNumber' => $patient->get('nationalNumber')
])
->groupBy( [ 'nationalNumber' ])
->build();
$row = $em
->getQueryExecutor()
->execute($query)
->fetch();

$GLOBALS['log']->warning( $row['MIN:(dateTime)'] .' => ' .$row['MAX:(dateTime)'] ); 

Edit :
Incredible.. i do this and it's perfect : Many Thanks Yuri :

->select([
'MAX:DATE:(dateTime)',
'MIN:DATE:(​dateTime)'
]) 

so my field dateTime is format in date by sql

[yuri]

Here should be getSqlExecutor() instead of getQueryExecutor(). There was my mistake.

[telecastg]

Thanks @item,

I don't have a specific SQL string that needs to be resolved at the moment, but since yuri stated that a raw string could also be used, I just wanted to learn the correct way to do it if it's possible.

Although I understand the reasoning behind yuri's goal of substituting raw SQL with Espo's ORM "meta-language", for ease of maintenance and flexibility to incorporate different database technologies in the future, I find that for customizations, the job of translating universally (or almost universally) used SQL strings to ORM, an unnecessary burden, for other than basic operations, since whereas there are countless tools online to help with SQL design and testing raw SQL, in comparison, the documentation and samples of Espo ORM query building are quite limited.

So I would like to use ORM, as recommended, for relatively simple queries, but would like to have the flexibility of using raw SQL for more complex jobs, like the one proposed here: https://forum.espocrm.com/forum/gene...1796#post71796 when convenient or necessary.


Best Regards

[item]

Ha i understand,

yes email and phone are quite complicate as SQL

Maybe yuri or teams can post on doc this kind of QueryBuilder.

I will try, if find a solution .. i post.

Regards

[telecastg]

Found my answer, here it is for others that might want to use raw SQL when required or preferred without having to use the deprecated getPDO() function:

$sql = 'PLAIN VANILLA SQL STATEMENT '

$sth = $this->getEntityManager()->getSqlExecutor()->execute($sql);

[yuri]

Right. I've fixed my post.

[yuri]

I don't plan to remove getPDO method any soon. Even if we remove it, you can still define it in a custom EntityManager class to support a legacy code.

[telecastg]

Thanks for the clarification !

[esforim]

I want to add this to the "wiki" as it seem to be quite useful for power user, but have no idea what to write as heading. Any suggestions will be accepted.

[telecastg]

Maybe "Using ORM to build queries" ?