Jump to content

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


gebeer
 Share

Recommended Posts

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.

Link to comment
Share on other sites

$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 by LostKobrakai
added parenthesis
  • Like 5
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

  • 10 months later...
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.

58abedc2d60b0_ScreenShot2017-02-20at11_33_46PM.thumb.png.5469d9c1896e4652855d619895dabcff.png

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.

  • Like 6
Link to comment
Share on other sites

@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 :)

  • Like 3
Link to comment
Share on other sites

  • 11 months later...
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.

58abedc2d60b0_ScreenShot2017-02-20at11_33_46PM.thumb.png.5469d9c1896e4652855d619895dabcff.png

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?

image.png.bd8a81ce42e7cad67ca21976b79d8080.png

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...