Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

dev 3.0.0: QB order_by() method may be extended for supporting random order with repetitve sequences #1987

Closed
ivantcholakov opened this Issue · 11 comments

3 participants

Ivan Tcholakov Andrey Andreev keatliang2005
Ivan Tcholakov

Example 1 (it works without need of escaping in "order_by" method):

$result = $this->db
    ->select('id, name, image')
    ->from('products')
    ->where('promoprice >', 0)
    ->order_by('RAND()')
    ->limit(3)
    ->get_compiled_select()
;
var_dump($result);
// SELECT `id`, `name`, `image` FROM `products` WHERE `promoprice` >0 ORDER BY RAND() LIMIT 3

Example 2 (it produces identical SQL to Example 1):

$result = $this->db
    ->select('id, name, image')
    ->from('products')
    ->where('promoprice >', 0)
    ->order_by('', 'random')
    ->limit(3)
    ->get_compiled_select()
;
var_dump($result);
// SELECT `id`, `name`, `image` FROM `products` WHERE `promoprice` >0 ORDER BY RAND() LIMIT 3

Now we want to introduce a seeder on order to have repetitive sequences, Example 3:

$seeder = 384;  // Within the real application I generate a random integer seeder and
                // I store it as a session variable.
$result = $this->db
    ->select('id, name, image')
    ->from('products')
    ->where('promoprice >', 0)
    ->order_by('RAND('.$this->db->escape_str($seeder).')')
    ->limit(3)
    ->get_compiled_select()
;
var_dump($result);
// SELECT `id`, `name`, `image` FROM `products` WHERE `promoprice` >0 ORDER BY RAND(384) LIMIT 3

Ok, the Example 3 works, at least on MySQL.

Is it possible order_by() to be reworked slightly, so it to be able to accept and to escape internally the seeder value? I mean something like this:

    ->order_by('', "random($seeder)")

or maybe this:

    ->order_by('', 'random', null, $seeder)
Andrey Andreev
Owner

Not a bad idea, but I'm not a fan of introducing new parameters. Especially in this case where one of the existing ones if effectively ignored.

I'd rather put the seed value in the second argument, but the documentation currently says that that is where you have to put 'random'.

Ivan Tcholakov

This might be a good choice.

->order_by($seeder, 'random')    // For this particular case, we might use the first patameter.
Ivan Tcholakov

Or maybe:

->order_random($seeder)     // Default value NULL.
Andrey Andreev
Owner

No, no new methods either ... that's unnecessary to say the least.

Passing 'random' as the second parameter is semantically wrong, as the second parameter is for ordering direction. RAND() is not a direction and so we should use the first parameter for it.

I'm thinking we can accept both formats - even currently both field and direction parameters are being checked for a match with the $_random_keyword property and if they match - anything else is ignored. But I'd also like to standartize it to the behavior explained above.

keatliang2005

order by random it's just too taxing if the data rows is huge

Ivan Tcholakov

I know that, thank you.

Andrey Andreev
Owner

Another thought - why would you need to add seed to the random function? What's the practical use case for this?

It's ordering after all, you're not generating tokens or using it for encryption.

Ivan Tcholakov

I am reworking a site for selling gifts (vanilla style code), so it to use CodeIgniter.

Example 1: On a sidebar (a panel) I want to show 3 promotional products, picked randomly. But I also want when these products to be the same, when the user reloads the page. This is why I store the seeder within a session variable.

Example 2 (more generic): Within the "Product Details" page at the bottom I've got a list "More Products at the Same Category" which shows in random order. And this list is to have pagination. On each shown page, pagination technically is to be done by using "offset" and "limit" on a same random list. Here I also need a seeder stored within the session.

Random Order + Pagination = Need for Repetitive Random Sequences.
Pagination on a random list without repetitive sequence does not make sense... :-)

Andrey Andreev
Owner

Sold! See the above commit.

Andrey Andreev narfbg closed this
Ivan Tcholakov

Thank you very much!

Ivan Tcholakov

Yes, it works:

$seed = 384;

$result = $this->db
    ->select('id, name, image')
    ->from('products')
    ->where('promoprice >', 0)
    ->order_by($seed, 'random')
    ->limit(3)
    ->get_compiled_select()
;

var_dump($result);
// SELECT `id`, `name`, `image` FROM `products` WHERE `promoprice` >0 ORDER BY RAND(384) LIMIT 3
Kyra Zimmer nonchip referenced this issue from a commit in nonchip/CodeIgniter
Andrey Andreev narfbg Add seed values support for Query Builder order_by
(feature request #1987)
c747d7a
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.