Jump to content

Recommended Posts

Posted

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.

Posted

Hi,

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

  • Like 1
Posted (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 by LostKobrakai
added parenthesis
  • Like 5
Posted

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

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

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?

Posted
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

Posted
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

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
×
×
  • Create New...