RockFinder3 - Combine the power of ProcessWire selectors and SQL

[adrian]

2 hours ago, bernhard said:

I've implemented your changes on the bardump branch

One thing you should probably change is the calls to db(), bdb(), etc so \TD::dumpBig, \TD::barDumpBig, etc. Those shortcut functions are optional in Tracy and some users may have disabled them. Otherwise, looks great!

I'll commit those required changes to Tracy shortly. BTW, I am making a proper barEcho() and be() shortcut method because I actually think it's quite useful in general to be able to echo / render HTML into the bar Dumps panel. 

[bernhard]

2 minutes ago, adrian said:

BTW, I am making a proper barEcho() and be() shortcut method because I actually think it's quite useful in general to be able to echo / render HTML into the bar Dumps panel. 

Absolutely! Just played around with RockPdf and that new tracy dumping features open up so many new possibilities (like rendering the generated pdf in an iframe). Really great stuff ?

3 minutes ago, adrian said:

One thing you should probably change is the calls to db(), bdb(), etc so \TD::dumpBig, \TD::barDumpBig, etc. Those shortcut functions are optional in Tracy and some users may have disabled them. Otherwise, looks great!

Thx for that reminder! I'm so used to them that I didn't think of them being optional ? I'll fix that instantly!

[adrian]

@bernhard - the new barEcho() version of Tracy has been committed.

[adrian]

Hey @bernhard - do you have support for getting fields from a page reference field?

Something like: addColumns(['title', 'pagefield.title'])

I feel like this should be fairly easy if the page field is a single item only. Not sure the best approach if it is set up to store references to multiple pages though.

I guess this is similar to your support for the Options fieldtype where you use: "OptionsValue:sex" in the docs example.

Any thoughts on whether this could be supported? I guess I could add it myself via the "columnTypes" folder, but I feel like this will be a common enough need that it should be included.

Nevermind - I really should RTFM ?

[elabx]

Question, how do you go about rendering the paginator with RockFinder3??

I am testing on a table with about a million pages, and it indeed loads each page (as in paginated page) super fast! BUT, I can't seem to figure out how to actually render the paginator. I'm going to take a wild guess that the counting is going to take a long time.

[bernhard]

There's at the moment no concept of pagination in RockFinder3, because it was initially built to build the data array for client side datatables that do the pagination and sorting on the client side. Could you please explain your use case a little more? Why is a regular $pages->find() no option if you get the data paginated anyhow?

[elabx]

3 hours ago, bernhard said:

There's at the moment no concept of pagination in RockFinder3, because it was initially built to build the data array for client side datatables that do the pagination and sorting on the client side. Could you please explain your use case a little more? Why is a regular $pages->find() no option if you get the data paginated anyhow?

Well its just a simple listing of content, I basically I was just wanted to give RockFinder  a shot to see if it performed faster and it does! Basically brings down the time to nothing. Took a dive into the code and notice how you strip down the SQL select, I am guessing that's why it peforms faster since the original db query includes SQL_COUNT_FOUND_ROW. So, I'm starting to think I have no way to solve this without counting? Unless I make a prev/next pagination only maybe? Of course, I can always cache all of this, but just wanted to see if I could squeeze out a bit more of performance from the raw request.

[adrian]

1 minute ago, elabx said:

the original db query includes SQL_COUNT_FOUND_ROW

Just an FYI for anyone who reads this - this is deprecated since MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

I have told Ryan here: https://github.com/processwire/processwire-issues/issues/1146#issuecomment-629428382

[elabx]

1 hour ago, adrian said:

Just an FYI for anyone who reads this - this is deprecated since MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

I'm going to die if any of the hosting providers I use suddenly flips the switch to mysql 8 lol

[adrian]

7 minutes ago, elabx said:

I'm going to die if any of the hosting providers I use suddenly flips the switch to mysql 8 lol

Deprecated, not yet removed, so no need to totally panic, although there are a few problematic bugs with PW and MySQL 8 that I have discovered and am notifying Ryan as I find them.

Keep in mind that v8 has been the stable release for 2 years now.

[elabx]

3 hours ago, adrian said:

few problematic bugs with PW and MySQL 8

Was thinking more of these, because I've seen all the github issues.

[bernhard]

Very good news for the weekend: RockFinder3 does now work again with the latest dev version of PW (thx to @adrian for making Ryan aware of the issue that also affected tracy!) ?

v1.0.6 is up and running smoothly and ready for a long future ? 

$owners = $rockfinder3
  ->find("template=person")
  ->addColumns(['title', 'age'])
  ->setName('owner'); // set name of target column
db($rockfinder3
  ->find("template=cat")
  ->addColumns(['title', 'owner'])
  ->join($owners)
  ->getSQL());

SELECT
  `pages`.`id` AS `id`,
  `_field_title_5ed16c868e555`.`data` AS `title`,
  GROUP_CONCAT(DISTINCT `_field_owner_5ed16c868e66d`.`data` ORDER BY `_field_owner_5ed16c868e66d`.`sort` SEPARATOR ',') AS `owner`,
  GROUP_CONCAT(DISTINCT `join_owner_5ed16c868ea01`.`title`) AS `owner:title`,
  GROUP_CONCAT(DISTINCT `join_owner_5ed16c868ea01`.`age`) AS `owner:age`  
FROM `pages`  
LEFT JOIN `field_title` AS `_field_title_5ed16c868e555` ON `_field_title_5ed16c868e555`.`pages_id` = `pages`.`id` 
LEFT JOIN `field_owner` AS `_field_owner_5ed16c868e66d` ON `_field_owner_5ed16c868e66d`.`pages_id` = `pages`.`id` 
LEFT JOIN (
  SELECT
    `pages`.`id` AS `id`,
    `_field_title_5ed16c868de1f`.`data` AS `title`,
    `_field_age_5ed16c868dea8`.`data` AS `age`  
  FROM `pages`  
  LEFT JOIN `field_title` AS `_field_title_5ed16c868de1f` ON `_field_title_5ed16c868de1f`.`pages_id` = `pages`.`id` 
  LEFT JOIN `field_age` AS `_field_age_5ed16c868dea8` ON `_field_age_5ed16c868dea8`.`pages_id` = `pages`.`id` 
  WHERE (pages.templates_id=48) 
  AND (pages.status<1024)
  GROUP BY pages.id 
) AS `join_owner_5ed16c868ea01` ON `join_owner_5ed16c868ea01`.`id` = `_field_owner_5ed16c868e66d`.`data` 
WHERE (pages.templates_id=44) 
AND (pages.status<1024)
GROUP BY pages.id

PS: I decided to change the case of the API variable from $RockFinder3 to $rockfinder3 (all lowercase)!

[adrian]

1 minute ago, bernhard said:

PS: I decided to change the case of the API variable from $RockFinder3 to $rockfinder3 (all lowercase)!

What about ditching the "3" ? IIRC there wasn't an API variable for the first version anyway, was there? Wouldn't this be cleaner going forward?

[bernhard]

Hm, that's interesting - I didn't think about that ? I think it's not too late for that change and will make using RockFinder3 easier, so I just committed that change, thx!

[zoeck]

@bernhard: I have been using RockFinder since today and I am thrilled, thanks for this cool module. I can create reports twice as fast as before

Now i have a question:

I have 2 separate templates, 1x order and 1x invoice

There is a field arnr in the order
In the invoice there is a field ordernr and rgnr

arnr and ordernr containing the same number

I would like to list all invoices (field: rgnr) for one order (in one line), but somehow I do not know how to combine the two fields mentioned above

Can you tell me if that's even possible?

[bernhard]

Hi @zoeck,

could you please provide an easy to grasp example (like cats and dogs) with example data and the output you want?

// template cat

 id | title
----|-------
 1  | foo
 2  | bar

 

[zoeck]

15 hours ago, bernhard said:

Hi @zoeck,

could you please provide an easy to grasp example (like cats and dogs) with example data and the output you want?

// template cat

 id | title
----|-------
 1  | foo
 2  | bar

 

Sure ? 

I need something like this:

// template order
arnr | title
-----|---------
123  | Order 1
234  | Order 2
345  | Order 3

// template invoice
rgnr | ordernr
-----|---------
100  | 123
101  | 123
102  | 234
103  | 234
104  | 345

// output should be
arnr | title   | rgnr
-----|---------|----------
123  | Order 1 | 100, 101
234  | Order 2 | 102, 103
345  | Order 3 | 104

 

[bernhard]

What kind of fields do you have? Page reference? Repeater? That's all necessary informations... What finders did you try already?

[zoeck]

32 minutes ago, bernhard said:

What kind of fields do you have? Page reference? Repeater? That's all necessary informations... What finders did you try already?

Only Text Fields ? The data comes from an Excel file which is updated via cron. The data is only visualized via Processwire.

For the query, I currently use 2 Rockfinder.

$allorders = $rockfinder->find("parent=/projekte/auftraege/, template=order,ardatum>={$dateYearStart},ardatum<={$dateYearEnd}")->addColumns(['ardatum', 'arnr', 'title']);

foreach($allorders->getRows() as $order) {
  // Output Order Informations
  $allrg = $rockfinder->find("parent=/projekte/rechnungen/, template=invoice, arnr={$order->arnr}")->addColumns(['rgnr']);
  foreach($allrg->getRows() as $rg) {
  	// Output Invoices
  }
}

 

[bernhard]

Ok, now I get it. If you used a page reference field on your order template that holds all related invoices you'd get exactly the output you want just by adding this field as a column in the finder.

Another option is using relations: https://github.com/baumrock/rockfinder3#relations

3rd Option is building your own column type: https://github.com/baumrock/rockfinder3#custom-column-types

[zoeck]

1 hour ago, bernhard said:

Ok, now I get it. If you used a page reference field on your order template that holds all related invoices you'd get exactly the output you want just by adding this field as a column in the finder.

Thanks bernhard ?

i added a pagereference field to my order template and linked all invoices, but now, only the ids of the invoices are displayed - not the field rgnr.
If I understand it correctly, I still need the "relations"

Just added it like this:

$arlink = $rockfinder
->find("parent=/projekte/rechnungen/, template=invoice")
->setName("arlink")
->addColumns(['rgnr']);

$allorders = $rockfinder->find("parent=/projekte/auftraege/, template=order,ardatum>={$dateYearStart},ardatum<={$dateYearEnd}")->addColumns(['ardatum', 'arnr', 'title', 'arlink'])->addRelation($arlink);

But there are no relations found?

[bernhard]

15 minutes ago, zoeck said:

i added a pagereference field to my order template and linked all invoices, but now, only the ids of the invoices are displayed - not the field rgnr.

Sorry, of course you get the ids! ? But you can already be enough depending on your situation. Do you dump lots of data? Or is in-memory an option?

16 minutes ago, zoeck said:

But there are no relations found?

What does that mean? How do you know? How does a tracy dump look? Did you follow the docs? https://github.com/baumrock/rockfinder3#relations

Did you already read about callbacks? https://github.com/baumrock/rockfinder3#callbacks

[zoeck]

36 minutes ago, bernhard said:

Sorry, of course you get the ids! ? But you can already be enough depending on your situation. Do you dump lots of data? Or is in-memory an option?

What does that mean? How do you know? How does a tracy dump look? Did you follow the docs? https://github.com/baumrock/rockfinder3#relations

Did you already read about callbacks? https://github.com/baumrock/rockfinder3#callbacks

I need the whole Data for a datatable...

Just added a "$allorders->relations->first()", then the following error occurs:

Quote

SQLSTATE[42000]: Syntax error or access violation: 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 ',,,,,,,,6279,,,,,,,,6219,,,,,,,,,,6177,6178,,,,,,,,,,,,,,,,,) 
GROUP BY pages.id' at line 7

Here's the dump of "bd($allorders->relations);"

And just a dump of the rockfinder

[psy]

Love the way @bernhard has integrated PW selectors/way-of-doing-things in RF3. The doco is really good too. Got the basics no problem.

I'm sure I'll figure it out eventually but any tips welcome for this scenario:

Client hires out equipment and needs to know 'last and next booking date' for an item and ensure that new bookings don't clash. Simplified template structure is:

Booking with:
- Event date(s)
- Booking items (repeater field) that calls on a list (page reference field) of bookable items

Report needs to show (filterable):

• Item (multiple defined template names)
• Last booking date
• Next booking date

= availability for a given proposed booking event date

Any pointers on how I can achieve this RF3?

[kongondo]

@psy,

Is this thread related to a module you are developing? If so, I'll move it to the module development sub-forum. Otherwise I'll have to merge it to RockFinder 3 thread ?.