gebeer Posted April 4, 2016 Share Posted April 4, 2016 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 More sharing options...
szabesz Posted April 4, 2016 Share Posted April 4, 2016 Hi, How about Tracy Debugger's "Debug Mode" panel? There aren't too many queries listed for an almost empty frontend page. 1 Link to comment Share on other sites More sharing options...
LostKobrakai Posted April 4, 2016 Share Posted April 4, 2016 (edited) $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 5 Link to comment Share on other sites More sharing options...
kongondo Posted April 4, 2016 Share Posted April 4, 2016 (edited) 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 3 Link to comment Share on other sites More sharing options...
gebeer Posted April 5, 2016 Author Share Posted April 5, 2016 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. 1 Link to comment Share on other sites More sharing options...
adrian Posted February 21, 2017 Share Posted February 21, 2017 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. 6 Link to comment Share on other sites More sharing options...
gebeer Posted February 22, 2017 Author Share Posted February 22, 2017 @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 3 Link to comment Share on other sites More sharing options...
Mackski Posted February 6, 2018 Share Posted February 6, 2018 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? Link to comment Share on other sites More sharing options...
Mackski Posted February 6, 2018 Share Posted February 6, 2018 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? Link to comment Share on other sites More sharing options...
adrian Posted February 7, 2018 Share Posted February 7, 2018 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. 1 Link to comment Share on other sites More sharing options...
adrian Posted February 7, 2018 Share Posted February 7, 2018 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now