Jump to content
gebeer

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

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.

Share this post


Link to post
Share on other sites

Hi,

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

  • Like 1

Share this post


Link to post
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 4

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
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 5

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Atlasfreeman
      Hi!
      I want to make a small site, a one page site.
      And i have this idea about doing 2 to 3 diffrent template that i can load into the index / home page.

      I want to do this with an array so that i can keep creating more topics (with the template) id needed.
      <?php include('./head.inc'); // include header markup ?> <?php $children = $page->get('template=onecolmn|twocolumn, sort=sort'); foreach($children as $child) { include($child); } ?> <?php include('./foot.inc'); // include footer markup ?>  
      As you can see i have to template wish i want to control from the backend.
      I know include does not work this way, put what is my other option to make an array that loads the whole page on an other pages.
       
       
    • By DooM
      Hello guys,
      I'm trying to figure out how to sync fields and templates between staging and production environments.
      I've found Migrations module by Lostkobrakai, but with use of it all the fields and templates must be created by API, which is kind of uncomfortable.
      I also tried ProcessDatabaseBackups module which can export only certain tables, but I don't think it's the best practice to do that.
      How do you guys solve this problem? It's very annoying to setup everything three times (dev, staging, production).
      Thanks a lot :)
    • By schwarzdesign
      ProcessWire added the numReferences property in 3.0.107, which returns the count of all pages having a reference to the page object. However, apparently it's not possible to use this property inside a selector. I'm trying to find all pages that are referenced at least once:
      $pages->find('template=service, numReferences>0'); This throws an error: "Field does not exist: numReferences".
      Is there another way to filter by the number of references? Of course, I could manually filter the results of the find query, but that feels overly complicated. Ideally, I would also like to filter the number of references from a specific field; that is, find all pages that are referenced at least once in one specific page reference field.
      Is there a way to do this? I guess supporting numReferences in selectors would be a feature request - if so, is it feasible?
      Thanks!
       
    • By Liam88
      Hi All,
      New user over at Processwire and have been rebuilding my site based on this CMS.
      I have been able to find so many answers through Google but I'm a little stuck on this one.
      I have my services page -> services categories -> category children.
      An example of those would be - domain -> services -> ppc -> management
      I also have a set of tags which have different names - services-tag -> grow-your-traffic
      Under these tags I would have multiple links to pages such as ppc, seo, social media and so on.
      A second example would be - services-tag -> convert-your-traffic
      Under here i would have multiple links to pages such as CRO
      Now the set of tags are not visible on-site as they are only created to give overview content to the main services categories.
      Using the categories and the tags I am looking to produce a layout such as (i have also attached an image as an example:
      Tag_1 headline
      Pull all services categories linking to Tag_1
      Tag_1 snippet
      Tag_2 headline
      Pull all services categories linking to Tag_2
      Tag_2 snippet
      So far I have this snippet which is pulling in the tag content but unable to get the posts to show under each of the tags. 
      If i change the if and statement to "tags" instead of "tag" then all posts show under all tags. Where as i want it to show only the posts which are linked to that tag.
      <?php namespace ProcessWire; $tags = $pages->get("/categories-services/")->children(); // Gets the tags $posts = $pages->get("/services/")->children(); // Gets the services categories $link = $tags->ref_6; // Gets the tags and services categories link - under here you have pages_id (services cat id) and data (tags id) // Tag header and summary foreach($tags as $tag) { // This breaks down the tags into sections echo '<section id="services"> <div class="container"> <div class="row"> <h2 class="heading-1"><span>'. $tag->headline.'</span></h2> <p class="mb-5">'. $tag->summary.'</p> </div> <div class="row justify-content-around services">'; // Main services categories that link to the above tags if ($posts->id === $link->pages_id && $tag->id === $link->data){ foreach($posts as $service){ // This pulls in the services categories under the tag header. echo '<div class="card flex-card" id=""> <div class="card-img"> <a href="/'. $service->name.'" title="'. $service->name .'"> <img class="card-img-top" src="../assets/files/'. $service->id.'/'. $service->img_1.'" alt="'. $service->img_1.'" title="'. $service->img_1.'"></a> </div> <div class="card-body"> <h3 class="card-title">'. $service->headline.'</h3> <p class="card-text">'. $service->summary .'</p> <div class="card-action"> <a href="" title="'. $service->name .'" role="link" class="link">View service<span></span></a> </div> </div> </div> '; } } // Grey snippet text echo '</div> </div> </section> <div class="snip-2 light-grey"> <div class="container"> <div class="row text-center">'. $tag->get('grey') .'</div> </div> </div>'; } ?> I appreciate this is a long post but i'm trying to be clear as I appreciate everyone's time.
      Any insight into where I am going wrong is greatly appreciated.
      Liam



    • By iipa
      Hi everybody!
      I have been reading about Multisite, but it kinda bugs me that every topic talks about having both admin and database same for multiple sites.
      I have a project where customer tests it by adding content to the site, while I still need to do some changes here and there in code, maybe some in database. If something crashes for a while, customer can't keep testing, which is a bit problematic.
      Is there any way that I could have two separate versions of one site ("production" and development) that share the same database, but are otherwise independent? Just the thought of having to migrate database every time I want to show client something new gives me anxiety 😁
×
×
  • Create New...