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 5
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By David Karich
      The Page Hit Counter module for ProcessWire implements a simple page view counter in backend. Page views of visitors are automatically tracked on defined templates, with monitoring of multiple page views. This gives you a quick overview of how many visitors have read a news or a blog post, for example, without first having to open complex tools such as Google Analytics. This module quickly provides simple information, e.g. for editors. Or, for example, to sort certain news by most page views. For example for "Trending Topics".

       
      Works with ProCache and AdBlockers. With a lightweight tracking code of only ~320 bytes (gzipped). And no code changes necessary! In addition GDPR compliant, since no personal data or IP addresses are stored. Only session cookies are stored without information. 
      In addition, there are some options, for example filtering IP addresses (for CronJobs) and filtering bots, spiders and crawlers. You can also configure the lifetime of the session cookies. Repeated page views are not counted during this period. It is also possible to exclude certain roles from tracking. For example, logged in editors who work on a page are not counted as page views.

      Sort by hits and access page views (hit value)
      Each trackable template has an additional field called phits. For example, you want to output all news sorted by the number of page views.
      // It is assumed that the template, e.g. with the name "news", has been configured for tracking. $news = $pages->find("template=news, sort=-phits"); To output the page views of a tracked page, use:
      echo $page->phits; Example: Reset counter per API
      $modules->get("PageHitCounter")->resetPageViews("template=whatever", false); Example: Tracking a page hit via API and jQuery
      If you want to track a template that does not represent a full page to automatically inject a tracking script, you can define allowed API templates in the module that you can track. Below is an example of how you can track a click on news tag using jQuery. This will allow you to find out which keywords are clicked the most. For example, you can sort and display a tag cloud by the number of hits. Suppose your keywords have the template "news_tag". The template "news_tag" was also configured in the Page Hit Counter Module as a trackable API template.
      Example PHP output of keywords / tags:
      // Required: the data attribute "data-pid" with the ID of the template to be tracked. echo $pages->find("template=news_tag, sort=-phits")->each("<a href='{url}' class='news_tag' data-pid='{id}'>{title}</a>"); Example Tracking Script with jQuery:
      /** * Required: Data attribute "data-pid" with the ID of the news tag template * Required: Send the POST request to the URL "location.pathname.replace(/\/?$/, '/') + 'phcv1'" * Required: The POST parameter "pid" with the ID of the template */ $(function(){ if($('a.news_tag').length > 0) { $('a.news_tag').each(function(){ var tPID = $(this).data("pid"); if(tPID) { $(this).on("click", function(){ $.post(location.pathname.replace(/\/?$/, '/') + 'phcv1', {pid: tPID}); }); } }); } }); So simply every click on a tag is counted. Including all checks as for automatic tracking. Like Bot Filtering, Session Lifetime, etc.
      Notice: Tracking with URL segments
      If the option "Allow URL Segments" is activated on a template, the hits are only counted if the base URL of the page is called. If you want the hit to be counted even when a segment is requested, you MUST configure the segments in the template configuration. How to do this can be found here. If you use dynamic segments, configure them as RegEx. There is currently no other option. The problem is that the Page Hit Counter hooked into the PageNotFound process. If URL segments are allowed but not defined, a 404 is never triggered. This means that the Page Hit Counter cannot be called.
      New since 2.0.0: Ignore URL segments
      If a template has URL segments configured, each hit on a different segment is counted as a new hit. Enable "Ignore URL segments" so that dynamic segments are not counted individually on the base template / page.
      New since 2.0.0: Use cookieless tracking (Experimental)
      Enable this option to not use individual cookies for tracking or if you have many different pages you want to track. The limit for cookies is 50 per domain for all cookies on the page. If the option is enabled, PHP session storage is used. Downside: you can't set the lifetime higher than configured in your PHP.ini and the session will be terminated as soon as the browser is closed.
      Upgrade note for 2.0.0 from previous versions!
      Version 2.0.0 requires an update in the database schema, so that additionally the date of the last access / hit on the page can be displayed ($page->lastPageHit). To make this possible, you have to do the update via the upgrade module, upload the ZIP itself and do an update directly via the backend AND DO A MODULE REFRESH DIRECTLY AFTER UPLOAD/UPDATE. If you do not do this, you will get an error that a column is missing in the database table.
      _______________________________________________________
      Background: This module is the result of a customer requirement, where the editors are overwhelmed with analytics or no tracking tools were allowed to be used. However, a way had to be found to at least count page views in a simple form for evaluations. Furthermore, by using ProCache, a way had to be found to count views of a page without clearing the cache.
      _______________________________________________________
      Pros
      Automatic Page View Tracking Lightweight tracking code, only ~320 bytes (gzipped) No code or frontend changes necessary Works with ProCache! Even if no PHP is executed on the cached page, the tracking works Works with browser AdBlockers No cache triggers (for example, ProCache) are triggered. The cache remains persistent GDPR compliant, session-based cookie only, no personal information Filtering of IPs and bots possible Exclude certain roles from tracking Ability to reset Page Views Works with all admin themes Counter database is created as write-optimized InnoDB API to track events for templates that are not viewable No dependencies on libraries, pure VanillaJS (Automatic tracking script) Works in all modern browsers Pages are sortable by hits Cons
      Only for ProcessWire version 3.0.80 or higher (Requires wireCount()) Only for PHP version 5.6.x or higher No support for Internet Explorer <= version 9 (Because of XMLHttpRequest()) No historical data, just simple summation (Because of GDPR) Segment URLs can only be counted if the segments are defined Planned Features / ToDos
      API access to hit values Since version 1.2.1 Possibility to sort the pages by hits (Request by @Zeka) Since version 1.2.0 Don't track logged in users with certain roles (Request by @wbmnfktr) Since version 1.1.0 Possibility to reset the counter for certain pages or templates (Request by @wbmnfktr) Since version 1.1.0 Better bot filter Since version 1.1.0 Disable session lifetime, don't store cookies to track every page view (Request by @matjazp) Since version 1.2.1 Option to hide the counter in the page tree (Request by @matjazp) Since version 1.2.1 Option to hide the counter in the page tree on certain templates Since version 1.2.1 API to track events for templates that are not viewable Since version 1.2.2 Cookieless tracking Since version 2.0.0 Show last hit Since version 2.0.0 Ignore URL segments (Request by @bernhard) Since version 2.0.0 Add hookable method after pageview was tracked (Request by @bernhard) Since version 2.0.0 Changelog
       
      2.0.0
      Feature request: Add hookable method after pageview was tracked (___pageViewTracked($pageID)) (Requested by @bernhard) Feature request: Ignore URL segments option (Requested by @bernhard) New: Cookieless tracking New: Show date of last hit Update: Botlist Enhancement: Documentation improvement 1.2.7
      Feature request: make buildPageListHitCounter-Function public (Requested by @bernhard) 1.2.6
      Bug-Fix: Set the counter of a cloned page to 0 Enhancement: The function for resetting counters is now available in the module as a public function to reset counters via own scripts on the API side (Request by @VeiJari) Enhancement: Documentation improvement API reset 1.2.5
      Bug-Fix: When counting 404 hits, cookies are no longer set. The session lifetime is deactivated for the 404 page Enhancement: Documentation improvement regarding URL segments 1.2.4
      Bug-Fix: Resetting the counters on system pages (e.g. 404) does not work (Reported by wbmnfktr) Bug-Fix: Tracking endpoint is logged as 404 if module "Jumplinks" is installed (Reported by wbmnfktr) Enhancement: Corrected few typos (Merged from Sergio #6 – THX!) 1.2.3
      Bug-Fix: Tracking script triggers 404 if pages are configured without slash (#3) Reported by @maxf5 Enhancement: Reduction of the tracking script size if it's gzipped (~320 bytes) Enhancement: Documentation improvement Enhancement: Corrected few typos 1.2.2
      New feature: API to track events for templates that are not viewable Enhancement: Documentation improvement 1.2.1
      API access to hit values Use $page->phits Bug-Fix: No tracking on welcomepage (Reported by wbmnfktr; Thx to matjazp) Bug-Fix: Tracking script path on subfolders (Reported by matjazp) Bug-Fix: Tracking on pages with status "hidden" Enhancement: Change database engine to InnoDB for phits field Enhancement: Option to disable session lifetime set session lifetime to 0, no cookies Enhancement: Better installation check Enhancement: AJAX Request asyncron Enhancement: Reduction of the tracking script size by ~20% Enhancement: Option to hide the counter in the page tree You can output the counter with the field name "phits" Enhancement: Option to hide the counter in the page tree on certain templates Enhancement: Option for activate general IP validation Enhancement: Reduction of tracking overhead up to ~30ms Enhancement: Better bot list for detection 1.2.0
      New feature: Sort pages by hits – New field phits Migrate old counter data to new field 1.1.0
      New feature: Exclude tracking of certain roles New feature: Reset Page Views Better bot filter and detection 1.0.0
      Initial release Notes
      By default, the page views are stored as INT in the database. This allows a maximum counter value of 4.2 billion views (4,294,967,295) per page. If you need more, change the type to BIGINT directly in the database. But I recommend to use Google Analytics or similar tools if you have such a large number of users.
      _______________________________________________________
      Download GitHub: ProcessWire Page Hit Counter (Version 2.0.0)
      PW Module Directory: ProcessWire Page Hit Counter (Version 2.0.0)
      Install via ProcessWire (Classname): PageHitCounter
      _______________________________________________________
      Update information
      If you have used version 1.2.1 from the DEV branch, please replace it completely with the new master version.
      Old stable version
      Download GitHub: ProcessWire Page Hit Counter (Version 1.2.7)
    • By jom
      Chäs & Co is a b2b cheese trader. They love good cheese and are proud of their excellent knowledge und connections to small cheese producers. They maintain a custom made database and used to copy/paste (!) the cheese infos to the former CMS (Contao). Since substituting the database with ProcessWire was no option, they export now the database to a csv file and import it to the new website (expanded version of module ImportPagesCSV).
      From there, the pw magic begins. The staff works with the admin interface: They search and find products in notime using the build-in search field (using cheese names or id. Using ids only possible by adding hook, see this forum thread). They generate price lists easily based on a template, providing checkboxes and option buttons for some restrictions, choose i.e. the cheese origin and price audience (all implemented as page selectors). By saving the page a PDF list gets generated (using mpdf, a php pdf library).
      The visitors can sort and filter the products using a form by shuffle.js. Product details are shown in a popup (fancybox). There's also the option for generating a nice pdf data sheet. No CSS framework being used.
      Other modules used: ProcessJumpLinks, ProcessProMailer, ProcessWireUpgrade.
      Visual design: Nicole Haller


    • By Juergen
      Hello @ all,
      I am creating a new inputfield/fieldtype to store opening hours, but I am struggeling to save values from multiple dynamic created inputfields in 1 column of the database.
      Scenario:
      The user can enter one or more opening times per day in a UI.
      Fe:
      Monday open from 08:00 to 12:00 and from 14:00 to 17:00 Tuesday open from 08:00 to 12:00 and from 14:00 to 19:00 and so on
      Via a little JavaScript you can add as much opening times as you need per day - the additional inputfield will be created dynamically.
      After form submission all the values are in the POST array -> this works (see example below):
      ProcessWire\WireInputData Object ( [openinghours_mo-0-start] => 09:00 [openinghours_mo-0-finish] => 13:00 [openinghours_mo-1-start] => 14:00 [openinghours_mo-1-finish] => 18:00 [openinghours_mo-2-start] => 21:00 [openinghours_mo-2-finish] => 23:00 [openinghours_tu-0-start] => 09:00 [openinghours_tu-0-finish] => 13:00 [openinghours_tu-1-start] => 14:00 [openinghours_tu-1-finish] => 18:00 [openinghours_we-0-start] => 09:00 [openinghours_we-0-finish] => 13:00 [openinghours_we-1-start] => 14:00 [openinghours_we-1-finish] => 18:00 [openinghours_th-0-start] => 09:00 [openinghours_th-0-finish] => 13:00 [openinghours_th-1-start] => 14:00 [openinghours_th-1-finish] => 18:00 [openinghours_fr-0-start] => 09:00 [openinghours_fr-0-finish] => 13:00 [openinghours_fr-1-start] => 14:00 [openinghours_fr-1-finish] => 18:00 [openinghours_sa-0-start] => [openinghours_sa-0-finish] => [openinghours_so-0-start] => [openinghours_so-0-finish] => ) The property name is always the name attribute of the field 😉 . If the property is empty means closed on that day.
      Now I need to combine all those values into 1 array (or json array) and store it in the database in 1 column called 'hours' in my case (see screenshot below):

      In my ___processInput(WireInputData $input) method I have tried to make it work like this:
      public function ___processInput(WireInputData $input): self { $name = $this->attr('name'); $value = $this->attr('value'); //input object includes always every input on the page, so lets filter out only inputs from this field //we need to do this, because the number of values is variable - so extract only values that starts with $name.'_' $nameAttributes = []; foreach($input as $key=>$value){ if(substr($key, 0, strlen($name.'_')) === $name.'_'){ $nameAttributes[$key] = $value; } } // loop through all inputfields of this fieldtype $time_values = []; foreach($nameAttributes as $nameAttr => $value) { $time_values[$nameAttr] = $value; } } //save it in the database $input->set('hours', serialize($time_values)); return $this; } The only important part of this code is the last part with the serialize function.
      After saving it will create a record in the database, but the value is always NULL (default value) (see below).

      Checking $time_values returns all the values, but printing out "$this" shows me that the property "hours" inside the Openinghours object is empty (see below) - so the mistake must be there, but I dont know where?!?!?!?
      [title] => Home [openinghours] => ProcessWire\OpeningHours Object ( [data] => Array ( [hours] => ) ) If I check the sleepValue() method or the sanitizeValue() - they are also empty. So it seems that the values will not reach these methods. I havent found a clear documentation of whats going on behind the saving process of an inputfield.
      As far as I know the saving process starts with the form submission. The values are in the POST array and will be processed by the processInput() method. Before they will be saved in the database they will be sanitized by the sanitizeValue() mehtod and afterwards they will be prepared for storage in the sleepValue() method.  The last step is the storage itself.
      Has someone an idea what is missing by storing values from multiple fields into 1 database column or has someone a working example of such a scenario on github to help me out.
      A clear explanation of the storage process will be also helpful.
      Thanks and best regards
    • By Pip
      Hi Everyone 
      I've been working on Processwire for two months now. Structuring the website as needed. Unsure why but I'm getting this one now. Seems my fields has crashed. 
      I've tried googling some answers but can't seem to find a step by step guide on how to rectify this. Any advise? Practically new on this. 
      TIA. 

    • By Rodd
      Hi everyone!
      I have a website in a production environment and I want to duplicate it in a local environment. I exported the content of the website (with the 'Site Profile Exporter' module) but I cannot use it actually. I've got an issue with the database. I imported this one in MAMP then.

      I also exported the pages (with the 'ProcessPagesExportImport' module), but I cannot import it to my local website because the fields don't exist. So I created this fields, but I have this error :
      How can I use the elements that already exist and are presents in my database? How can I duplicate correctly the templates, fields and pages?
      Thanks by advance
      PS: Sorry if my english is bad
       
×
×
  • Create New...