theo

@bernhard: Or like this: $this->addHook("Pages::findArray", function($event) { ... foreach ($fields as $f) { $field_extra = explode(':', $f); $f = $field_extra[0]; $field_extra = array_slice($field_extra, 1); $field = $this->fields->get($f); ... case $fieldtype instanceof FieldtypeImage: $sql .= ",\n (SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f"; if (in_array('description', $field_extra)) $sql .= ",\n (SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS " . $f . "_description"; break; case $fieldtype instanceof FieldtypeOptions: $sql .= ",\n (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f"; if (in_array('title', $field_extra)) $sql .= ",\n (SELECT title FROM fieldtype_options WHERE fields_id=(SELECT id FROM fields WHERE name = '$f') AND option_id=$f) AS " . $f . "_title"; break; Then request extra data like 'images:description': foreach ($pages->findArray("id=1, sort=title, limit=1000", ['title', 'images:description'], []) as $p) {

Yes, speed must be the first priority here. Load only things you need. In case of Images, only desc is "advanced use". "data" is already the filename and url is not in the database. URL is (pseudo) assets/files/$pageid/$data But I think we should offer a more convenient way for "description" than writing the select statement yourself. "description" exists for FieldtypeFile too.

Great! Although it would be a great addition from the community, I am not sure if ryan would like to add it to core. You lose all the comfort of Processwire. For example I've written some code last evening to access image-urls and descriptions (snippet, experimental): case $fieldtype instanceof FieldtypeImage: $sql .= ",\n (SELECT GROUP_CONCAT($data SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS $f"; $sql .= ",\n (SELECT GROUP_CONCAT(description SEPARATOR 0x1D) FROM field_$f WHERE pages_id = p.id) AS " . $f . "_desc"; break; foreach ($pages->findArray("id=1, sort=title, limit=1000", array('title', 'images'), array()) as $p) { $images = explode(chr(29), $p['images']); $images_desc = explode(chr(29), $p['images_desc']); $imgs = array_combine($images, $images_desc); $langid = $this->user->language->id; if ($langid == $languages->getDefault()->id) $langid = 0; foreach ($imgs as $url => $descr) { $ajson = json_decode($descr, true); if (isset($ajson[$langid])) $desc=$ajson[$langid]; else $desc=""; echo "URL=" . $p['id'] . '/' . $url . ", DESCR=" . $desc; } } You see, it works if you know how, but it feels a bit like writing assembler. But yes, it is fast!

Playing more... If you need a fast multi level nav, you can try the code below (using findArray). Set "has_parent" to the id of the parent page. The difference to looping through pages -> children is remarkable. Objects : 794 / Execution time : 0.021355867385864 seconds function buildTree($flat, $pidKey, $idKey = null) { $grouped = array(); foreach ($flat as $sub) { $grouped[$sub[$pidKey]][] = $sub; } $fnBuilder = function($siblings) use (&$fnBuilder, $grouped, $idKey) { foreach ($siblings as $k => $sibling) { $id = $sibling[$idKey]; if (isset($grouped[$id])) { $sibling['children'] = $fnBuilder($grouped[$id]); } $siblings[$k] = $sibling; } return $siblings; }; $tree = $fnBuilder($grouped[$flat[0][$pidKey]]); //!! return $tree; } $arr = $pages->findArray("has_parent=1029, sort=parent_id, include=all, limit=5000", array('title'), array('id', 'parent_id', 'name')); $tree = buildTree($arr, 'parent_id', 'id'); foreach ($tree as $p1) { echo '<h2>' . $p1['title'] . '</h2>'; if (isset($p1['children'])) foreach ($p1['children'] as $p2) { echo '<h3>' . $p2['title'] . '</h3>'; if (isset($p2['children'])) foreach ($p2['children'] as $p3) { $count++; echo '<a href="../myparent/' . $p1['name'] . '/' . $p2['name'] . '/' . $p3['name'] . '/">'; echo '<h6>' . $p3['title'] . '</h6></a>'; } } } Function buidTree is from here: https://stackoverflow.com/questions/4196157/create-array-tree-from-array-list @bernhard Am I hijacking your thread?

You are lucky. Here it happens all the time. The warning shows even in the Skyscraper Demo: http://demo.processwire.com/admin/page/?login=1

Playing around with this, I have tested repeaters and there is some code to get titles in addition to the index from FieldtypeOptions (Not sure if you like this). Just sharing what I have. $this->addHook("Pages::findObject", function($event) { $event->return = $this->pages->findArray($event->arguments(0), $event->arguments(1), $event->arguments(2), true); }); $this->addHook("Pages::findArray", function($event) { $selector = $event->arguments(0); $fields = $event->arguments(1); $fields_pages = $event->arguments(2) ?: []; $type = $event->arguments(3) ? \PDO::FETCH_OBJ : \PDO::FETCH_ASSOC; // build sql string $sql = "SELECT\n p."; // add fields of pages table $fields_pages[] = 'id'; // make sure we return the page id $fields_pages = array_unique($fields_pages); $sql .= implode(",\n p.", $fields_pages); foreach ($fields as $f) { $field = $this->fields->get($f); if (!$field) continue; $fieldtype = $field->type; $f = strtolower($f); // fielddata is always stored in the "data" column of the field's table // multilang fields have several data columns identified by the language id // we use a variable to query the current user's language, eg data1234 $data = "data"; switch (true) { // if it is a multilang field we append the language id to query the correct column case $fieldtype instanceof FieldtypeTextLanguage: case $fieldtype instanceof FieldtypeTextareaLanguage: if ($this->user->language->name != 'default') $data .= $this->user->language->id; // no break here intended! // build sql query case $fieldtype instanceof FieldtypeText: case $fieldtype instanceof FieldtypeCheckbox: $sql .= ",\n (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f"; break; case $fieldtype instanceof FieldtypePage: case $fieldtype instanceof FieldtypeRepeater: $sql .= ",\n (SELECT GROUP_CONCAT($data SEPARATOR ',') FROM field_$f WHERE pages_id = p.id) AS $f"; break; case $fieldtype instanceof FieldtypeFile: $sql .= ",\n (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f"; //$sql .= ",\n (SELECT description FROM field_$f WHERE pages_id = p.id) AS ".$f."_desc"; break; case $fieldtype instanceof FieldtypeOptions: $sql .= ",\n (SELECT $data FROM field_$f WHERE pages_id = p.id) AS $f"; $sql .= ",\n (SELECT title FROM fieldtype_options WHERE fields_id=(SELECT id FROM fields WHERE name = '$f') AND option_id=$f) AS " . $f . "_title"; break; default: $sql .= ",\n '$fieldtype not supported' AS $f"; } } $pages = $this->pages->findIDs($selector); if (count($pages) == 0) { $event->return = []; } else { $sql .= "\nFROM\n pages AS p"; $sql .= "\nWHERE\n p.id IN (" . implode(",", $pages) . ")"; //echo '<pre>' . $sql . '</pre>'; $results = $this->database->query($sql); $event->return = $results->fetchAll($type); } }); Testing with: foreach ($pages->findArray("template=families, sort=title, limit=5000", array('title'), array()) as $p) { foreach ($pages->findArray("parent=" . $p['id'] . ",sort=title, limit=5000", array('title'), array()) as $m) { foreach ($pages->findArray("parent=" . $m['id'] . ",sort=title, limit=5000", array('title', 'sku12', 'Sound_Sample'), array()) as $s) { echo ($p['title'] . ' ' . $m['title'] . ' ' . $s['title'] . '<br>'); $repeater = $s['sound_sample']; if ($repeater) { foreach ($pages->findArray('id=' . str_replace(',', '|', $repeater) . ",limit=5000", array('name', 'Sound_Type_ss', 'Files_ss'), array()) as $r) { echo '<h5>' . $r['files_ss'] . '</h5>'; echo '<h5>' . $r['sound_type_ss_title'] . '</h5>'; } } } } }

@bernhard: Another thing to keep in mind. Field Names can contain uppercase characters whereas the database fields are all lowercase, it seems. So after getting the type with the name possibly containing uppercase chars, the field name has to be converted to lowercase for the DB table "field_xyz" foreach ($fields as $f) { $field = $this->fields->get($f); $f=strtolower($f);

I just wanted to ask if I missed something. It is not urgent for me, I am still evaluating what's possible with PW to be able to decide if it's the right tool for a certain job. Are you going to publish your code in the next weeks or so? Thank you.

Without a basic idea about PHP you probably won't get far with processwire imo. About your question"How to eliminate..?" It is all in the PHP files in "/site/templates/". Probably _main.php in your case.

@bernhard: This is great! Is there a more complete version of this function somewhere? I mean more supported Fieldtypes "and stuff"? Thank you.

I think it depends on what you do exactly. The example above: Objects : 794 / Execution time : 0.69733490943909 seconds Compared with this (titles autojoined): foreach ($pages->find('template=families') as $fam) { foreach ($fam->children() as $model) { foreach ($model->children() as $size) { echo ($fam->title . ' ' . $model->title . ' ' . $size->title . '<br>'); } } } Objects : 794 / Execution time : 1.6172688007355

Can you make an example? What is has_parent? Btw. I've found a problem in your "findArray" function: $items = $this->pages->findIDs($selector); if there are no matching items, there will be an SQL error, because WHERE id IN (" . implode(',', $items) . ") is empty.

Never mind, I think I understand. foreach ($pages->findArray("template=families, sort=title, limit=5000", array('title'), 'array') as $p) { foreach ($pages->findArray("parent=" . $p['id'] . ",sort=title, limit=5000", array('title'), 'array') as $m) { foreach ($pages->findArray("parent=" . $m['id'] . ",sort=title, limit=5000", array('title'), 'array') as $s) { echo ($p['title'] . ' ' . $m['title'] . ' ' . $s['title'] . '<br>'); $count++; } } } And it is a lot faster. Thank you!

Can you help me? How would you code this using findArray? foreach ($pages->find('template=tpl_families') as $fam) { foreach ($fam->children() as $model) { foreach ($model->children() as $size) { } } } Thank you.

Thank you. Good idea. I'm going to have a closer look later. My problem is, that I need to get data from a hierarchical tree, like foreach ($pages->find('template=tpl_families') as $fam) { foreach ($fam->children() as $model) { foreach ($model->children() as $size) { } } } Or: $item->parent->parent->title . $item->parent->title . $item->title And from repeaters like foreach ($item->Sound_Sample as $sound) { echo $sound->Sound_Type_ss->title; if ($sound->Files_ss) echo $sound->Files_ss->url; } This is no problem with PW Pages for single items, but if I need to export the entire data, it is getting quite slow. Otoh, all the necessary SELECT...JOINS are probably a nightmare.

Answering my own question: Yes, Autojoin makes a measurable difference in speed and no, you don't need "$f->save()" to speed up the subsequent find() operation. So this is enough: foreach($fields->find("name=sku|inactive|new|discontinued") as $f) { $f->addFlag(Field::flagAutojoin); } The settings in the field dialog will not be changed this way.

@alxndre: Ah. Thank you. Is there an elegant way to define autojoin fields on-the-fly before using $pages->findMany() or in the selector? I know I could probably do it like this example: foreach($fields->find("name=myfield1|myfield2|myfield3") as $f) { $f->addFlag(Field::flagAutojoin); $f->save(); } Another question: What happens without $f->save() ? Will it auto-join in the subsequent find() call or not? Or do I have to remember-state -> save -> restore-state?

What is autoloading? Can you explain?

@LostKobrakai: I am not comparing these codes technically. I'm totally aware that they do different things "behind the scenes". But I don't think it is bad to be aware of the fact, that there might be a faster way to access PW data in certain cases. For example for data export (csv) or the like.

Hmm, I think for certain queries, it is indeed better to use the database directly. Speed difference is remarkable (~64x) These two snippets produce the same output: $result = $this->db->query("SELECT id, data FROM pages LEFT JOIN field_title ON pages_id = pages.id WHERE templates_id=54 ORDER BY data limit 5000"); while($row = $result->fetch_array()) { echo '<p>' . $row['data']. '</p>'; $count++; } //Objects : 4067 / Execution time : 0.042616128921509 seconds foreach ($pages->findMany('template=my_tpl, sort=title, limit=5000') as $item) { echo '<p>' . $item->title . '</p>'; $count++; } //Objects : 4067 / Execution time : 2.7485032081604 seconds

Yes, debug mode is on. It's not a problem since I know about "findMany", it is just a liitle scary. ProcessWire 3.0.93 / PHP 7.0.7

No, nothing. It's as if the server would hang up. The browser's load indicator stops and the old page remains. Thanks.

@kongondo: Thanks, just in time. I just wanted to ask why this works.. foreach ($pages->find('template=my_tmpl, limit=2500') as $endo) { echo '<p>' . $endo->title . '</p>'; } ...and this returns nothing (higher limit): foreach ($pages->find('template=my_tmpl, limit=3000') as $endo) { echo '<p>' . $endo->title . '</p>'; } Using findMany it works.

@Macrura Thank you, but I need to select using a tree. Is this possible with ASM Select? "Page List Select Multiple" does exactly what I need in this regard.

This is a little bit faster, but just a little. $time_start = microtime(true); $count = 0; foreach ($pages->find('template=tpl_sizes') as $size) { echo '<h4>' . $size->parent->parent->title . '</h4><h5>' . $size->parent->title . '</h5><h6>' . $size->title . '<h6>'; $count++; } $time_end = microtime(true); $time = $time_end - $time_start; echo 'Objects : ' . $count; echo ' / Execution time : ' . $time . ' seconds';