psy Posted October 8, 2020 Share Posted October 8, 2020 I have a $pages selector that works great in giving me the requested info. However it's incredibly slow. Added to this, the request may be called up to 15 times on a particular page for 15 different listed items. <?php // in my custom module... // retrieves qty of items already hired on a particular day to determine availability for new bookings // booking_items is a repeater field and booking_item are repeater pages // I already have the $item and the $eventDate // Query is searching through 5K+ bookings and at least 3x that many booking_item repeater pages $limit = $item->qty; $selector = "template=booking, booking_items.booking_item.id=$item, booking_date=$eventDate, booking_status!=pending|cancelled, limit=$limit"; $pp = $this->wire()->pages; $otherBookings = $pp->find($selector); Each item query can take between 2 to 8 seconds ? What can I do to speed up this query? TIA psy Link to comment Share on other sites More sharing options...
MoritzLost Posted October 8, 2020 Share Posted October 8, 2020 If you really need some serious speed increase you can of course go directly to the database instead of using $pages. Though that is a lot more work, because you'll have to manually add status flags to your query, manage multilanguage support etc. I've done this for my module Automatically link page titles, the list of linkable titles and corresponding page IDs is retrieved directly from the field_title table. See the source code for reference. If you can't optimize the query, caching might be a solution, this way only the initial access will run slowly, but subsequent requests and queries will be fast. Your query varies by $item, $eventDate and $limit, so you could create a cache key based on that and store the results through the $cache API. Something like this: $limit = $item->qty; $cacheKey = implode('-', ['bookings', $item, $eventDate, $limit]); $cache = $this->wire('cache'); $selector = "template=booking, booking_items.booking_item.id=$item, booking_date=$eventDate, booking_status!=pending|cancelled, limit=$limit"; $pp = $this->wire()->pages; $otherBookings = $cache->get( $cacheKey, WireCache::expireSave, function () use ($pp, $selector) { return $pp->find($selector); } ) Quick and untested, might need some adjustments. You can use WireCache::expireSave to invalidate the cached results whenever any page is saved, though that's not very efficient if the bookings are edited very frequently. Or use a max age of a day or so, at the risk of sometimes showing outdated results. 4 Link to comment Share on other sites More sharing options...
BillH Posted October 8, 2020 Share Posted October 8, 2020 Might RockFinder speed things up sufficiently? Latest version at https://github.com/baumrock/rockfinder3. I'd expect it to reduce your query times greatly - probably well under a second. And you can keep using PW selectors. 1 Link to comment Share on other sites More sharing options...
dragan Posted October 8, 2020 Share Posted October 8, 2020 8 hours ago, psy said: retrieves qty of items Can you elaborate on this? Is the quantity derived from a page / selector count? And that's all you really need with all those queries? There are faster ways to get counts than to always return the full page arrays. (I probably miss the "big picture", sorry if it's a dumb question) Link to comment Share on other sites More sharing options...
psy Posted October 8, 2020 Author Share Posted October 8, 2020 @BillH Thanks, 7 hours ago, BillH said: Might RockFinder speed things up sufficiently? I tried RockFinder3 a while ago. It's certainly a great module however I couldn't figure out how to get it working with Repeater fields. It's perfect with direct relation fields, eg PageReference but Repeaters are different beasts. Not sure if RF3 handles '.' separated fields in the selector, eg "booking_items.booking_item.id" either? Link to comment Share on other sites More sharing options...
psy Posted October 8, 2020 Author Share Posted October 8, 2020 @dragan No question you ask is ever dumb! I should have made it clearer. Each item is a page, a product page. The qty is an integer field and refers to how many units of that product are owned by the client. The $limit is set to that number. The query searches through other bookings for that item on the nominated day, and on each match, subtracts the booking_qty from the item qty, to return how many are left for hire on that date. When all are booked out, the front-end customer cannot add it to their cart to prevent over-booking. A count wont do it as other bookings may have booked 2 or more units of the same item on that day. Link to comment Share on other sites More sharing options...
psy Posted October 9, 2020 Author Share Posted October 9, 2020 Grateful for all your suggestions and you gave me an idea. Didn't get a chance to test today but will try tomorrow. It's a conglomeration of your suggestions. in _init.php prepended to all pages (or maybe refined to only the pages on which it's needed ?), collect all bookings for the day, regardless of individual item ids and cache it. Historical bookings won't change and without the repeater stuff, will be quick; instead of going back to the db for all calls, run the individual item queries on the cached page array - at most 20 items Will report back ? Link to comment Share on other sites More sharing options...
kongondo Posted October 9, 2020 Share Posted October 9, 2020 Here's another... I am not sure if in your case the slowness is due to the query itself or the page objects that have to be created after the find. If the latter, you can tell ProcessWire not to return page objects. You can then work with arrays as it seems your main interest are not the items themselves but their IDs and the quantities. Just a quick thought.. 1 Link to comment Share on other sites More sharing options...
psy Posted October 10, 2020 Author Share Posted October 10, 2020 Just tested and it works a treat ? When a frontend user enters their event date, in _init.php: <?php if (!empty($input->post->availability)) { $availability = $sanitizer->date($input->post->availability, 'Y-m-d'); $sameDayBookings = $pages->find("template=booking, booking_date=$availability, booking_status!=pending|cancelled",['loadOptions'=>['autojoin' => true, 'joinFields' => ['booking_items']]]); $cache->save('sameDayBookings', $sameDayBookings); } This query takes no time at all. Next, in my custom module: <?php /** * @param Page $item - product page * @return int|void * @throws WireException */ public function itemAvailability(Page $item) { if ($item instanceof NullPage) return; $itemBookingCount = 0; $otherBookings = $this->wire('cache')->get('sameDayBookings'); if ($otherBookings->count > 0) { // there are other booking on the same event date foreach ($otherBookings as $otherBooking) { foreach ($otherBooking->booking_items as $otherBookingItem) { if ($otherBookingItem->booking_item->id == $item->id) { $itemBookingCount = (int)$itemBookingCount + (int)$otherBookingItem->booking_qty; } } } } $itemsAvailable = (int)$item->qty - (int)$itemBookingCount; return $itemsAvailable; } Query times for this function went from an average of 3.5sec to under 0.01 seconds per item and under 1sec to render the page listing multiple items with their availability. Happy dance time! Thank you all! 3 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now