Apply SQL Query to in-memory PHP Object or Array

Question

Use Case:

I'm building a site where users can search records - with SQL. BUT - they should also be able to save their search and be notified when a new submitted record meets the criteria.

It's not a car buying site, but for example: The user searches for a 1967 Ford Mustang with a 289 V8 engine, within the 90291 ZIP code. Can't find the right one, but they want to be notified if a matching car is submitted 2 weeks later.

So of course, every time a new car is added to the DB, I can retrieve all the user search queries, and run all of them over all the cars in the DB. But that is not scalable.

Rather than search the entire "car" table with every "search" query every time a new car is submitted, I would like to just check that single "car" object/array in memory, with the existing user queries.

I'm doing this in PHP with Laravel and Eloquent, but I am implementation agnostic and welcome any theoretical approaches.

Thanks,

Chris

Answer 1

I would rather run the saved searches in batches at scheduled intervals and not run them avery time a record is appended to the tables.

Answer 2

It comes down to how you structure your in memory cache.

Whatever cache it is it usually relies on key, value pairs. It will be the same for the cache you are using: http://laravel.com/docs/4.2/cache

So in the end it is all about using the right key. If you want to update the cached objects based on a car, then you would need to make the key in a way so that you can retrieve all objects from the cache using the car as (part of) the key. Usually you would concat multiple things for key like userId+carId+xyz and then make a MD5 checksum of that.

So that would be the answer to your question. However generally I would not recommend this approach. It sounds like your search results are more like persisted long term available results. So you would probably want to store them somewhere more permanent like a simple table. Then you can use standard SQL tools to join the table and find out what is needed.

Answer 3

My approach would be to use a MySQL stored procedure and use https://dev.mysql.com/doc/refman/5.1/en/event-scheduler.html to review the configs for possible changes and then flag them storing some kind of dirty indicator which is then checked by a php script which would be executed on demand or from cron etc periodically. You could use the trigger to simply flag that the event scheduler has work to do. However you approach there are a number of state variables which starts to get ugly however this use case doesn't seem to map neatly into a queuing architecture as far as I can see.

Answer 4

A possible approach would be to use a trigger in SQL to send a notification. Here is something related with it: 1s link or 2nd link.