General developer forum

Thinking a bit more about the need for correctness and pagination, maybe we're making this way harder than it needs to be.

What I mean to say is: we need temp tables for this. If you do the magic, and select into a temptable, then you can tell your caller - hey, just JOIN against table $foo and we are sorted.

Back in the big rewrite mess, I hit this wall hard too. I am sure there's a thread on this forum about patches to make our DB layer support temptables, for this exact reason.

The short of it: it is a veritable a mess to try to support temptables across the DBs we have with consistent semantics. Some have automatic 'garbage collection', some have private namespaces, some have special names for them that need to be escaped or specialcased everywhere.

It's not impossible, but at the time, it was hard enough to fall in the 'for later' basket.

If we can get temptables working... woohoo! Second option: if we can get an established "results cache" table then what you are tring to do becomes possible. The 'results cache' table is going to become a bottleneck on large sites though so it's better to avoid it.