Search the Community

Page Query Boss Build complex nested queries containing multiple fields and pages and return an array or JSON. This is useful to fetch data for SPA and PWA. You can use the Module to transform a ProcessWire Page or PageArray – even RepeaterMatrixPageArrays – into an array or JSON. Queries can be nested and contain closures as callback functions. Some field-types are transformed automatically, like Pageimages or MapMarker. Installation Via ProcessWire Backend It is recommended to install the Module via the ProcessWire admin "Modules" > "Site" > "Add New" > "Add Module from Directory" using the PageQueryBoss class name. Manually Download the files from Github or the ProcessWire repository: https://modules.processwire.com/modules/page-query-builder/ Copy all of the files for this module into /site/modules/PageQueryBoss/ Go to “Modules > Refresh” in your admin, and then click “install” for the this module. Module Methods There are two main methods: Return query as JSON $page->pageQueryJson($query); Return query as Array $page->pageQueryArray($query); Building the query The query can contain key and value pairs, or only keys. It can be nested and contain closures for dynamic values. To illustrate a short example: // simple query: $query = [ 'height', 'floors', ]; $pages->find('template=skyscraper')->pageQueryJson($query); Queries can be nested, contain page names, template names or contain functions and ProcessWire selectors: // simple query: $query = [ 'height', 'floors', 'images', // < some fileds contain default sub-queries to return data 'files' => [ // but you can also overrdide these defaults: 'filename' 'ext', 'url', ], // Assuming there are child pages with the architec template, or a // field name with a page relation to architects 'architect' => [ // sub-query 'name', 'email' ], // queries can contain closure functions that return dynamic content 'querytime' => function($parent){ return "Query for $parent->title was built ".time(); } ]; $pages->find('template=skyscraper')->pageQueryJson($query); Keys: A single fieldname; height or floors or architects The Module can handle the following fields: Strings, Dates, Integer… any default one-dimensional value Page references Pageimages Pagefiles PageArray MapMarker FieldtypeFunctional A template name; skyscraper or city Name of a child page (page.child.name=pagename); my-page-name A ProcessWire selector; template=building, floors>=25 A new name for the returned index passed by a # delimiter: // the field skyscraper will be renamed to "building": $query = ["skyscraper`#building`"] Key value pars: Any of the keys above (1-5) with an new nested sub-query array: $query = [ 'skyscraper' => [ 'height', 'floors' ], 'architect' => [ 'title', 'email' ], ] A named key and a closure function to process and return a query. The closure gets the parent object as argument: $query = [ 'architecs' => function($parent) { $architects = $parent->find('template=architect'); return $architects->arrayQuery(['name', 'email']); // or return $architects->explode('name, email'); } ] Real life example: $query = [ 'title', 'subtitle', // naming the key invitation 'template=Invitation, limit=1#invitation' => [ 'title', 'subtitle', 'body', ], // returns global speakers and local ones... 'speakers' => function($page){ $speakers = $page->speaker_relation; $speakers = $speakers->prepend(wire('pages')->find('template=Speaker, global=1, sort=-id')); // build a query of the speakers with return $speakers->arrayQuery([ 'title#name', // rename title field to name 'subtitle#ministry', // rename subtitle field to ministry 'links' => [ 'linklabel#label', // rename linklabel field to minlabelistry 'link' ], ]); }, 'Program' => [ // Child Pages with template=Program 'title', 'summary', 'start' => function($parent){ // calculate the startdate from timetables return $parent->children->first->date; }, 'end' => function($parent){ // calculate the endate from timetables return $parent->children->last->date; }, 'Timetable' => [ 'date', // date 'timetable#entry'=> [ 'time#start', // time 'time_until#end', // time 'subtitle#description', // entry title ], ], ], // ProcessWire selector, selecting children > name result "location" 'template=Location, limit=1#location' => [ 'title#city', // summary title field to city 'body', 'country', 'venue', 'summary#address', // rename summary field to address 'link#tickets', // rename ticket link 'map', // Mapmarker field, automatically transformed 'images', 'infos#categories' => [ // repeater matrix! > rename to categories 'title#name', // rename title field to name 'entries' => [ // nested repeater matrix! 'title', 'body' ] ], ], ]; if ($input->urlSegment1 === 'json') { header('Content-type: application/json'); echo $page->pageQueryJson($query); exit(); } Module default settings The modules settings are public. They can be directly modified, for example: $modules->get('PageQueryBoss')->debug = true; $modules->get('PageQueryBoss')->defaults = []; // reset all defaults Default queries for fields: Some field-types or templates come with default selectors, like Pageimages etc. These are the default queries: // Access and modify default queries: $modules->get('PageQueryBoss')->defaults['queries'] … public $defaults = [ 'queries' => [ 'Pageimages' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'focus', ], 'Pagefiles' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'filesize', 'filesizeStr', 'hash', ], 'MapMarker' => [ 'lat', 'lng', 'zoom', 'address', ], 'User' => [ 'name', 'email', ], ], ]; These defaults will only be used if there is no nested sub-query for the respective type. If you query a field with complex data and do not provide a sub-query, it will be transformed accordingly: $page->pageQueryArry(['images']); // returns something like this 'images' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'focus'=> [ 'top', 'left', 'zoom', 'default', 'str', ] ]; You can always provide your own sub-query, so the defaults will not be used: $page->pageQueryArry([ 'images' => [ 'filename', 'description' ], ]); Overriding default queries: You can also override the defaults, for example $modules->get('PageQueryBoss')->defaults['queries']['Pageimages'] = [ 'basename', 'url', 'description', ]; Index of nested elements The index for nested elements can be adjusted. This is also done with defaults. There are 3 possibilities: Nested by name (default) Nested by ID Nested by numerical index Named index (default): This is the default setting. If you have a field that contains sub-items, the name will be the key in the results: // example $pagesByName = [ 'page-1-name' => [ 'title' => "Page one title", 'name' => 'page-1-name', ], 'page-2-name' => [ 'title' => "Page two title", 'name' => 'page-2-name', ] ] ID based index: If an object is listed in $defaults['index-id'] the id will be the key in the results. Currently, no items are listed as defaults for id-based index: // Set pages to get ID based index: $modules->get('PageQueryBoss')->defaults['index-id']['Page']; // Example return array: $pagesById = [ 123 => [ 'title' => "Page one title", 'name' => 123, ], 124 => [ 'title' => "Page two title", 'name' => 124, ] ] Number based index By default, a couple of fields are transformed automatically to contain numbered indexes: // objects or template names that should use numerical indexes for children instead of names $defaults['index-n'] => [ 'Pageimage', 'Pagefile', 'RepeaterMatrixPage', ]; // example $images = [ 0 => [ 'filename' => "image1.jpg", ], 1 => [ 'filename' => "image2.jpg", ] ] Tipp: When you remove the key 'Pageimage' from $defaults['index-n'], the index will again be name-based. Help-fill closures & tipps: These are few helpfill closure functions you might want to use or could help as a starting point for your own (let me know if you have your own): Get an overview of languages: $query = ['languages' => function($page){ $ar = []; $l=0; foreach (wire('languages') as $language) { // build the json url with segment 1 $ar[$l]['url']= $page->localHttpUrl($language).wire('input')->urlSegment1; $ar[$l]['name'] = $language->name == 'default' ? 'en' : $language->name; $ar[$l]['title'] = $language->getLanguageValue($language, 'title'); $ar[$l]['active'] = $language->id == wire('user')->language->id; $l++; } return $ar; }]; Get county info from ContinentsAndCountries Module Using the [ContinentsAndCountries Module](https://modules.processwire.com/modules/continents-and-countries/) you can extract iso code and names for countries: $query = ['country' => function($page){ $c = wire('modules')->get('ContinentsAndCountries')->findBy('countries', array('name', 'iso', 'code'),['code' =>$page->country]); return count($c) ? (array) $c[count($c)-1] : null; }]; Custom strings from a RepeaterTable for interface Using a RepeaterMatrix you can create template string for your frontend. This is usefull for buttons, labels etc. The following code uses a repeater with the name `strings` has a `key` and a `body` field, the returned array contains the `key` field as, you guess, keys and the `body` field as values: // build custom translations $query = ['strings' => function($page){ return array_column($page->get('strings')->each(['key', 'body']), 'body', 'key'); }]; Multilanguage with default language fallback Using the following setup you can handle multilanguage and return your default language if the requested language does not exist. The url is composed like so: `page/path/{language}/{content-type}` for example: `api/icf/zurich/conference/2019/de/json` // get contenttype and language (or default language if not exists) $lang = wire('languages')->get($input->urlSegment1); if(!$lang instanceof Nullpage){ $user->language = $lang; } else { $lang = $user->language; } // contenttype segment 2 or 1 if language not present $contenttype = $input->urlSegment2 ? $input->urlSegment2 : $input->urlSegment1; if ($contenttype === 'json') { header('Content-type: application/json'); echo $page->pageQueryJson($query); exit(); } Debug The module respects wire('config')->debug. It integrates with TracyDebug. You can override it like so: // turns on debug output no mather what: $modules->get('PageQueryBoss')->debug = true; Todos Make defaults configurable via Backend. How could that be done in style with the default queries? Module in alpha Stage: Subject to change This module is in alpha stage … Query behaviour (especially selecting child-templates, renaming, naming etc) could change

Hi, I have installed 1 year old project backup into the new latest PW version. I used an earlier backup(taken in August 2020) and installed such as : 1. I downloaded the latest (ProcessWire 3.0.185 dev © 2021) then extracted into htdocs 2. copy pasted the site-profile from my backup. (this has the files/folders same as other site profiles, classic, beginner etc) 3. I chose my backup site profile and installed 4. I am able to login the admin panel 5. My fronted home page shows error as below Error: Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'page_path_history.language_id' in 'field list' (in wire/modules/PagePathHistory.module line 752) #0 wire/modules/PagePathHistory.module (752): PDOStatement->execute() #1 wire/core/PagesPathFinder.php (1014): PagePathHistory->getPathInfo('/', Array) #2 wire/core/PagesPathFinder.php (222): PagesPathFinder->getPathHistory('/') #3 wire/core/PagesRequest.php (255): PagesPathFinder->get('/', Array) #4 wire/core/Wire.php (414): PagesRequest->___getPage() #5 wire/core/WireHooks.php (951): Wire->_callMethod('___getPage', Array) #6 wire/core/Wire.php (485): WireHooks->runHooks(Object(PagesRequest), 'getPage', Array) #7 wire/modules/Process/ProcessPageView.module (10 This error message was shown because: you are logged in as a Superuser. Error has been logged. so I removed the line where its selecting language_id from the file wire/modules/PagePathHistory.module line 752 But this is an ugly fix, so is there any other proper fix for this issue ? Does any one experience same issue when trying to install from a backup site profile ? thanks

Hi! On a clients multilanguage site, upgrading to latest PW version, started to generate the following exceptions: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pages.has_parent' in 'where clause' (in /wire/core/WireDatabasePDO.php line 480) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pages.has_parent' in 'where clause' (in /wire/core/WireDatabasePDO.php line 480) And this message by the newly created page: Name already in use! A sibling page (id=15877) is already using name "my-page-name" for language: English Searching the forums the only thing i've found seems related to multi language:

We have many booking calendars made with ProcessWire (own databases) and I want to do a web app (SQL) which allows user to log in. First, the user chooses the right calendar and then (s)he have to log in. The user can be from any of those calendars and the app is not running on ProcessWire (it can if necessary). So if there any way to make sure that the user has rights to the calendar (s)he tries to log in and if the password is correct. Is there any better way to do this? I could also use PIN codes or something, but those need to be encrypted too. Multiple ProcessWires A lot of users per ProcessWire Everyone can log in to the web app (when using right calendar)

I've been working with different CMS's for quite a few years now but there was always one thing that bugged me, I never knew how the CMS takes a url and resolves a page ID from it. I knew it was to do with the "slug" but what i couldn't figure out is when it came to sub pages, as the slug only refers to the page itself not the parent pages in the url e.g /parent-page/sub-page. The main two CMS's i've worked with are Wordpress and ProcessWire, ProcessWire always has the upper hand when it comes to speed, even a large PW site is tens of milliseconds faster than a fresh Wordpress install. With the resolution of urls to pages being (probably) the most used query in a CMS i thought i'd investigate the two different approaches. Both ProcessWire and Wordpress split the urls by forward slash to extract the slugs /about/people/ => ['about', 'people'], however ProcessWire takes a completely different approach when it comes to resolving a page ID from these slugs. ProcessWire uses this query: SELECT pages.id,pages.parent_id,pages.templates_id FROM `pages` JOIN pages AS parent2 ON (pages.parent_id=parent2.id AND (parent2.name='about')) JOIN pages AS rootparent ON (parent2.parent_id=rootparent.id AND rootparent.id=1) WHERE pages.name='people' AND (pages.status<9999999) GROUP BY pages.id LIMIT 0,1; Resulting in a single item of the page in question including the page's template id. For urls with more parts it looks as though ProcessWire creates more JOINS to essentially walk back up the hierarchy and fully resolve the ID of the correct page. Wordpress on the other hand takes a different approach: SELECT ID, post_name, post_parent, post_type FROM wp_posts WHERE post_name IN ('about','people') AND post_type IN ('page','attachment'); More elegant looking however it returns a list of potential items. So requires a loop within PHP to walk the hierarchy tree and determine the correct page ID. Both queries once cached by MySQL took 19-21ms to return, ProcessWire looks as though it uses this to it's advantage by returning the correct page ID straight from the MySQL cache and doesn't require the extra looping step afterwards. Interesting to see the different approaches to the same problems.

Hi all I have big troubles with my DB backups and phpMyAdmin. I can't manage to import a backup. The phpMyAdmin export settings are left to the defaults. The error message I get: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `caches` (`name`, `data`, `expires`) VALUES ('ModulesUninstalled.in' at line 11 When I delete the complete 'cache' section there are almost for all tables the same errors. (part of the dump attached) I had this problem before and back then (couple months ago) I think I copied almost every table individually. I thought it was specific to that DB. But I can't do a dump/import of any of my PW installations. MySQL: 5.6.27 PHP version: 7.0.11 phpMyAdmin: 4.5.5.1 Anybody knows that problem? Or what to do? phpMyAdmin SQL Dump.sql

I've been asked to look into migrating an existing site into Magento & Stripe. I haven't mentioned to my client whether he would consider Processwire because I wasn't sure how to do the accounting side of things using PW. He currently has a bespoke site (PHP / MySQL) and 1 of his biggest problems is that there are some bugs with the accounting side of things and he has to reconcile his accounts manually. As much as I like PW, I don't know how easy it would be generate various sales and accounting report using PW. The only way I can currently think of is to store each sale as a page and I would have to write various get functions with filters and loop through them to get totals. I never thought I'd say this but I'm missing SQL right now Any thoughts, suggestions or opinions would be welcome here.

pw 3.0.36 I just installed on a new site and received this warning when I accessed the admin url. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ProcessLogger' for key 'class' Is that anything to worry about? The admin and site appear to be working fine. I just want to avoid issues down the road if there is a problem somewhere.

Hi guys I have a problem with a specific filter/selector I try to achieve. The following filter causes a SQL-Error. This error also appears when I want to sort subfields(options and page fields - multi and single select) in the lister or with the API. Some Infos Processwire Version: 2.7.3 ListerPro Version: 1.0.8 Media Manager: 0.0.6 PHP-Version: 7.0.8-0 UBUNTU 0.16.04.3 MySQL-Version: 5.7.13-0ubuntu0.16.04.2-log - (Ubuntu) I tried changing the sql_mode many times: DEFAULT IS: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NO_ENGINE_SUBSTITUTION I tried all above but with minimal effect. When I remove "ONLY_FULL_GROUP_BY" it doesn't cause a SQL-Error but it still can't find the page. Also sorting subfields start working. But everything works fine locally on a fresh blank install with the media manager and listerpro. My local MySQL Version is 5.6.25 and PHP version is 5.6. ----------------------------------------------------------- The Intention why I am asking (last post): ----------------------------------------------------------- Some other Processwire Topics related to sql_modes: I wanted to ask in the forum before I try it on github: https://github.com/processwire/processwire-issues ----------------------------------------------------------- Some related links to sql_modes: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html Greetings Orkun

Is there an easy way to get back the SQL query generated by a specific processwire selector? I have what appears to be a cartesian product and/or groupwise maximum bug in a selector, but I'm not sure how to confirm it. I'm querying for a set of pages with a specific template which are related to the current page. The returned pages include a URL field and a file field. The selector looks like this: $grades = $pages->find('template=grade,include=all,classification.title="'.$page->title.'",sort=series,sort=title'); "classification" is a page field. Series and title are text fields. I'm iterating through the results and outputting three text fields, a URL field, and a file (->url) field. On one of the nine pages using this query, it should only return one row, but it returns two. The text fields are identical for both rows. The URL and file fields are empty in the first row and populated in the second row. The other eight pages all work as expected with no duplication. Thanks, Jason

Creating separate thread as last one was a bit buried. Apologies. I think our VPS provider has been having issues today with their servers and when I tried going to one of our sites we were getting "Unable to complete this request errors". When I turned on debugging, I get the following error Fatal error: Exception: SQLSTATE[HY000]: General error: 1 Can't create/write to file '/tmp/#sql_daf_0.MYI' (Errcode: 30) Which sounds to me like it's a problem accessing/writing to the database (on our server)? Could anyone confirm if that sounds right? Cheers guys.

is it somehow possible to get PW running without SQL - maybe as a file based CMS, saving everything in a single(?) xml or multiple txt files? i know, there are a lot of downsides using files, but for some small projects it would be a great alternative!

Hi all, I'm tweaking a search engine that's built using PW selectors. I'm wondering if there is a way to set the selector to find word fragments, for example the user searches on 'grass seed' and I want to find fields that have the words 'grasses' and 'seeding' (or 'grassy' and 'seeds' for another example). I know I can use the %= selector, but my options there are body%=grass seed or body%=grass|seed, neither of which return just what I want. I could switch to using raw SQL (body LIKE 'grass%' AND body LIKE 'seed%') but I'd rather stick with PW selectors if I can. Thanks!

Hey, My company asked me how Processwire handles SQL Injections, i was quite sure this was done somewhere but after some scanning through the code and the documentation. I noticed I couldn't really find an answer to the question. Because security is a big issue I would like to ask where and how processwire handles SQL injections? Big thanks! Greetings, Harm.