Jump to content

RockFinder3 - Combine the power of ProcessWire selectors and SQL


bernhard

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 4 weeks later...

@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)); 
	});

 

Capture.PNG.56af64281060445570488823f3827950.PNG

Link to comment
Share on other sites

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();

 

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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();

 

  • Haha 1
Link to comment
Share on other sites

  • 1 year later...

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);
}

fFw5u9I.png

  • Thanks 1
Link to comment
Share on other sites

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();

 

Link to comment
Share on other sites

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;

 

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...