Hello guys, I have a query like this:
$query = Product::query() ->andWhere('some kind of logic') ->orderBy('RAND()') ->limit(10);
But it works so slowly, because it is not optimized and I want to get the following in the context of the Phalcon:
SELECT product.* FROM ( SELECT id FROM product ORDER BY RAND() LIMIT 10 )
AS ids JOIN post ON product.id = ids.id WHERE 'some kind of logic'
Does anyone have any idea how to implement this?
Ordering by RAND() is not good performance wise on SQL level. Phalcon has nothing to do about it.
There are many alternatives on the web: https://www.google.bg/search?q=mysql+order+by+rand+alternative
I made it:
$lightweight = Product::query() ->column('id') ->join(...) ->andWhere(...) ->orderBy('RAND()') ->limit(...); $query = Product::query() ->inWhere('id', array_column($lightweight->execute()->toArray(), 'id'))
Thanks, Nikolay
