Hi Rector Team! I was wondering if rector is suitable for refactoring arbitrary plain SQL query mixed with php code to an orm equivalent query

[alexandreelise]

Hi, everything is in the title. This example is intentionnally using unsafe old code since it's refactoring old query to more secure format automatically if possible.

e.g. use case

$query = 'SELECT COUNT(*) AS counter, id, title FROM whatever WHERE id > 1 AND title = $obj->title';

result expected for that query after potential auto refactor by custom rector rule? maybe?

$db  = Factory::getContainer()->get(DatabaseDriver::class);
$query = $db->getQuery(true);
$query->select('COUNT(w.*) AS counter' . $db->qn(['w.id','w.title']))
      ->from($db->qn('#__whatever', 'w'))
      ->where($db->qn('w.id') . '>1')
      ->where($db->qn('w.title') . '= :myTitle');

$query->bind(':myTitle', $obj->title, ParameterType::STRING);

That's it. Any suggestions?

[alexandreelise]

Maybe I still did not yet grasp the true power of rectorphp even after reading your rector book.

[staabm]

I did similar things in the reverse direction in the past.

If your sql queries look like that and are not built with concat and similar dynamic stuff you can run the query thru https://github.com/SQLFTW/sqlftw and based on the sql-ast create your php code

[alexandreelise]

Hi @staabm Thanks for taking the time to answer. Unfortunately some queries are seperated and scattered through multiple disjoints lines with dynamic stuff everywhere. It seems that in this case, one viable approach would be to detect php call that executes the RAW query using the php parser composer package from nikic, cache the results in a file indexed by in which file the queries are found, then treat each file found manually on a case-by-case basis. Thanks anyway. Take care and enjoy your holidays. Hava a delightful weekend.

[staabm]

In phpstan-dba there is a api to build the query string even in more complicated if/else/concat scenarios