RockFinder3 - Combine the power of ProcessWire selectors and SQL

[Spiria]

2 minutes ago, bernhard said:

What happens if you put that selector in a regular $pages->find() ?

I get what I want.

[bernhard]

Did you install the Page Paths module?

[Spiria]

No.

[bernhard]

addPath() needs the PagePath module to be installed! Does it work if you install it?

Why do you need the path? How many pages do you have?

[Spiria]

Just now, bernhard said:

addPath() needs the PagePath module to be installed! Does it work if you install it?

Why do you need the path? How many pages do you have?

 

[Spiria]

This is a bilingual site. I want the right URL for the user language.

I was testing your solution to see the performance difference it makes as a search feature with htmx. Removing the addPath command does not solve the problem. The problem is in the query statement, I think... Why this particular selector does not work?

There are 1350 pages actually.

[bernhard]

1 minute ago, Spiria said:

Why this particular selector does not work?

I don't know. You can dump the created sql query and inspect it to find the problem: https://github.com/baumrock/RockFinder3#dumping-the-sql-of-the-finder

[Spiria]

6 minutes ago, bernhard said:

I don't know. You can dump the created sql query and inspect it to find the problem: https://github.com/baumrock/RockFinder3#dumping-the-sql-of-the-finder

 

[Spiria]

1 minute ago, Spiria said:

 

The addColumn() doesn't like to have selectors in it, I think.

[bernhard]

That is strange. The selector is just used for a $pages->findIDs internally. Then the columns are merged, but I don't know how the selector value should end up there? Is that the whole query? Can you come up with a reproducable example?

[Spiria]

16 minutes ago, bernhard said:

That is strange. The selector is just used for a $pages->findIDs internally. Then the columns are merged, but I don't know how the selector value should end up there? Is that the whole query? Can you come up with a reproducable example?

Please note that the "=" equal sign doesn't create the problem, but "*=" does. Perhaps * is read as a regex expression for the field name and should be escaped? I am not sure I have time this weekend to create a standalone site with some stuff in it to explore the problem. I will let you know.

[flydev]

@bernhard How you would have started to delete more than 4 millions pages ?

 

$end = strtotime( "2021-02-27 23:59:59");
$result = $rockfinder
    ->find("template=receipt, numChildren=0, created<$end, limit=25000")
	->each(function($row, $finder) { 
	    $finder->pages->delete($finder->pages->get($row->id)); 
	});

 

[bernhard]

RockFinder does not bring you any benefit here as it relies on the native findIDs. Have a look here:

 

[flydev]

Thanks, it's running on batch of 5000 records and I will see how much time it take. I guess I still have the benefit of not crashing the server while getting all the candidate pages.

$end = strtotime( "2021-02-27 23:59:59");

// loop
$database->beginTransaction();
$result = $rockfinder
	->find("template=receipt, numChildren=0, created<$end, limit=5000")
	->each(function($row, $finder) use ($database) { 
		  
	    $finder->pages->delete($finder->pages->get($row->id)); 
	});
$database->commit();

 

[flydev]

Result, it crashed two times, but managed to delete 2 millions of pages in about ~15 hours ??

RockFinder really helped here - it reduce the code that should be written and the server overhead of getting the millions of record through a loop.

[bernhard]

Nice, thx for sharing the results ? 

Though I'm still not sure why RockFinder helped here? I think it would be the same to do this:

<?php
$end = strtotime( "2021-02-27 23:59:59");

// loop
$database->beginTransaction();
$ids = $pages->findIDs("template=receipt, numChildren=0, created<$end, limit=5000")
foreach($ids as $id) $pages->delete($pages->get($id));
$database->commit();

 

[flydev]

Correct - let find an argument then, it was more fun to write, I like chaining things ?

[bernhard]

Haha yeah I'm a fan of chaining ?  eg https://github.com/baumrock/rockseo

Thx for clarifying. Just wanted to make sure I'm not missing anything ? 

[eydun]

Thank you for building this awesome module.

Is to possible to do custom sql-queries with RockFinder like this?

SELECT id, class, created FROM modules

 

[bernhard]

Hi @eydun you don't need RockFinder for that, you can just use the core:

$database = $wire->database;
$query = $database->prepare("SELECT id, class, created FROM modules");
$query->execute();
while($row = $query->fetch(\PDO::FETCH_ASSOC)) {
    db($row);
}
$query->closeCursor();

You could also use the DatabaseQuerySelect abstraction:

$q = new DatabaseQuerySelect();
$result = $q->select('id, class, created')
  ->from('modules')
  ->execute();
while($row = $result->fetch(\PDO::FETCH_ASSOC)) {
    db($row);
}

[eydun]

 Would it be possible to dump this result to Tabulator (like $rf->dump();)?

[bernhard]

Sure. Tabulator can display any data you feed it.

 

[eydun]

Thanks. Can I pass this data to a rockfinder-instance, and use rockfinder dump-method?

 

<?php namespace ProcessWire;

/** @var $rockfinder RockFinder3 */

$rf = $rockfinder->find("template=dummy");

$q = new DatabaseQuerySelect();
$result = $q->select('id, class, created')
    ->from('modules')
    ->execute();

//dump($result->fetchAll());

// how do pass $result->fetchAll() to $rf-variable?
$rf->dump();

 

[bernhard]

Why and where do you want to dump your data? Just for debugging?

[eydun]

I use RockFinder together with runtimemarkup-module https://processwire.com/modules/fieldtype-runtime-markup/ to display various page-data in the backend for the content-superusers.

There are also some tables in the database that are non-processwire-tables, which I would like to display with RockFinder (if it is possible?).

There is my code in the markup-field (it is working):

$modules = $this->wire()->modules;
$rockfinder = $modules->get("RockFinder3Master");

$rf = $rockfinder->find("template=categories")->addColumns(['id', 'title', 'created']);
ob_start();
$rf->dump();
$result = ob_get_clean();
return (string)$result;