MySQL Database to Processwire Pages. Opinions?

[adrian]

@theo - here's another approach that you might like to add to your arsenal. Note that this was just an experiment on my part and there will be an upper limit on using the results of findIDs in an SQL query like this, but if you follow the rest of the discussion on that thread you'll get an idea of the pros and cons.

https://processwire.com/talk/topic/15524-preview-rockdatatables/?do=findComment&comment=158104

[theo]

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.

[adrian]

1 minute ago, theo said:

Otoh, all the necessary SELECT...JOINS are probably a nightmare.

That's why approach I linked is so easy to work with - let PW take care of getting all the matched IDs and then getting the fields you need from that match via SQL. At least I think it helps your situation, although honestly I didn't read through this thread very thoroughly, so maybe I am not getting all the issues.

[LostKobrakai]

$pages->find($selector, ['load_options' => ['joinFields' => ['title']]]);

This should allow you to join fields on demand.

[theo]

55 minutes ago, adrian said:

That's why approach I linked is so easy to work with - let PW take care of getting all the matched IDs and then getting the fields you need from that match via SQL. At least I think it helps your situation, although honestly I didn't read through this thread very thoroughly, so maybe I am not getting all the issues.

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.

[theo]

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!

[adrian]

Glad you figured it out. Could you also reduce this down to one loop by making use of has_parent in the selector? Not sure your structure, but might be worth investigating.

[theo]

3 minutes ago, adrian said:

Glad you figured it out. Could you also reduce this down to one loop by making use of has_parent in the selector? Not sure your structure, but might be worth investigating.

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.

[adrian]

1 hour ago, theo said:

Can you make an example? What is has_parent?

Check out the PW cheatsheet - it's on there.

Thanks for noting the error if no matches are returned. Not sure if @bernhard fixed that further along in that thread - I know he tweaked things quite a bit.

[bernhard]

2 hours ago, adrian said:

Thanks for noting the error if no matches are returned. Not sure if @bernhard fixed that further along in that thread - I know he tweaked things quite a bit.

Nope, but added a comment in the related thread. I'll start working on the datatables module very soon and implement that check  thx @theo

 

3 hours ago, theo said:

And it is a lot faster. Thank you!

what is "a lot"? just curious

[theo]

17 hours ago, bernhard said:

what is "a lot"? just curious

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

[ak1001]

I think i can add to CONTRA PW inability to design some simple database features like unique fields
and datagrid like data editing in admin from the box
So after many years of development PW still not mass used , big projects are still complicated.
I observe how  opencart now widespread  and even how people trying magento 2  -very complicated system -  but no one want to program on  PW.


 

[cstevensjr]

8 minutes ago, ak1001 said:

I think i can add to CONTRA PW inability to design some simple database features like unique fields
and datagrid like data editing in admin from the box
So after many years of development PW still not mass used , big projects are still complicated.

Can you please elaborate a little more and better explain what you are talking about?  If you could provide some examples about PW's inabilities, that would be very helpful.

[theo]

36 minutes ago, ak1001 said:

I think i can add to CONTRA PW inability to design some simple database features like unique fields
and datagrid like data editing in admin from the box
So after many years of development PW still not mass used , big projects are still complicated.
I observe how  opencart now widespread  and even how people trying magento 2  -very complicated system -  but no one want to program on  PW.
 

Not sure if I understand what you are talking about.
PW will never be as "mass used" as Wordpress, but this doesn't make Wordpress a better tool.

Then you are comparing PW with e-commerce tools. 
I don't need e-commerce tools most of the time.
But I once had a job to code a complete website with Magento. 
It is more or less usable for the e-commerce part, as long as you don't need anything "special", but for developing the rest of the website, it is a nightmare imo.

[ak1001]

I am very like processwire and look it closely  for over  3 years i think, surely it well suited for tasks like simple site or blog.
But i need more e-shop options : products , categories, clients. So SKU must be unique, user login/emails must be unique, orders must be unique with custom prefix-suffix autoincrement and so on. And you must have ability to manage all this in a grid like view from admin width search/filter capability.
So after 3 years of development since i look on pw i don't see it matured in this direction.

This don't make pw bad, it's still cool cms