Get database query for $pages->find() operation

[gebeer]

Hello,

How would I get the DB query that is used to gather the data for something like

wire('pages')->find("template=log, id_gc={$serverId}, timestamp>={$dateStart}, timestamp<={$dateEnd}, sort=timestamp");

Tried using the Debug  Mode Tools in the backend on a lister with similar selector. But  I couldn't make out the right query in there.

I'd like to use that query directly to dump data from the DB to a csv via SELECT INTO.

Processing 10.000s of pages in chunks of 100 via the API into a csv. This is quite time consuming (several minutes for ~20.000 pages that result in a ~13MB csv file).

Any help would be much appreciated.

[szabesz]

Hi,

How about Tracy Debugger's "Debug Mode" panel? There aren't too many queries listed for an almost empty frontend page.

[LostKobrakai]

$pages->getPageFinder()->find(new Selectors($selector), array('returnQuery' => true))->getQuery();

I seem to recall teppo posting a less verbose version of this, but I cannot find it currently.

Edited April 5, 2016 by LostKobrakai
added parenthesis

[kongondo]

Try getQueryLog(); (with $config->debug to true)

https://processwire.com/talk/topic/11483-duplication-in-query-results/?p=106994

Edited April 4, 2016 by kongondo

[gebeer]

Thank you all for your help on this one.

@szabesz

I would rather not like to install Trasy Debugger if there was a simpler method through the API

@LostKobrakai

the suggested code returned null. But this version (with parentheses appended to the getQuery method) works:

wire('pages')->getPageFinder()->find(new Selectors($selector), array('returnQuery' => true))->getQuery();

It returned exactly the query I was looking for

SQL_CALC_FOUND_ROWS pages.id,pages.parent_id,pages.templates_id 
FROM `pages` 
JOIN field_id_gc AS field_id_gc ON field_id_gc.pages_id=pages.id AND (((field_id_gc.data='16800' ) )) 
JOIN field_timestamp AS field_timestamp ON field_timestamp.pages_id=pages.id AND (((field_timestamp.data>='2016-01-28 15:00:42' ) )) 
JOIN field_timestamp AS field_timestamp1 ON field_timestamp1.pages_id=pages.id AND (((field_timestamp1.data<='2016-02-16 00:19:53' ) )) 
LEFT JOIN field_timestamp AS _sort_timestamp ON _sort_timestamp.pages_id=pages.id 
WHERE (pages.templates_id=44) 
AND (pages.status<1024) 
GROUP BY pages.id 
ORDER BY _sort_timestamp.data 
LIMIT 0,100 

@kongondo

$queries = $database->getQueryLog(); returns an array of queries with a lot more info than LostKobrakai's code and the specific query is not included. The querie here look somewhat like the output of Debog Mode Tools in backend.

[adrian]

On 4/4/2016 at 7:31 PM, gebeer said:

I would rather not like to install Trasy Debugger if there was a simpler method through the API

Actually, it's pretty nice running your API call through the Tracy Console - save it as a snippet so you always have it on hand. This way you can easily see the results of the selector as well as the SQL query used to generate them.

PS I think this is the thread that was mentioned regarding teppo's version: https://processwire.com/talk/topic/9408-is-there-a-way-to-convert-a-selector-in-sql-using-pw-engine/ although I think it's actually longer.

[gebeer]

@adrian Thank you for the input. I have been able to get the query like mentioned in the post above yours. This is quite some time back now.

In the meantime I've come to love Tracy Debugger. It is very easy to setup and makes debugging fun. Wouldn't want to miss it now

[Mackski]

I've enabled $config->debug = true; however $database->getQueryLog() returns an empty array.

Does this need to be supported by mysql settings in any way?

[Mackski]

On 2/21/2017 at 5:38 PM, adrian said:

Actually, it's pretty nice running your API call through the Tracy Console - save it as a snippet so you always have it on hand. This way you can easily see the results of the selector as well as the SQL query used to generate them.

PS I think this is the thread that was mentioned regarding teppo's version: https://processwire.com/talk/topic/9408-is-there-a-way-to-convert-a-selector-in-sql-using-pw-engine/ although I think it's actually longer.

Tracey doesnt show the full query?

[adrian]

7 hours ago, Mackski said:

Tracey doesnt show the full query?

You just need to make use of the maxDepth and maxLength options when making the d() call.

You can read the docs here: https://adrianbj.github.io/TracyDebugger

Also note that the Debug Mode panel > Selector Queries section now shows the SQL query for all selectors used to generate the current page.

[adrian]

20 hours ago, Mackski said:

however $database->getQueryLog() returns an empty array.

It depends on the version of PW. Take a look at my logic here:

https://github.com/adrianbj/TracyDebugger/blob/805e09c8641a438deeb1b7a7983bc59a148e43a1/panels/DebugModePanel.php#L221-L224