How to request x random items

Support
1

Is there a simple filter function to get x random items from a filtered collection?

I try to find something like this (options may change):

$options = [
    'filter' => [
        'published' => true,
        'active' => true,
        'location' => 'Germany'
    ],
    'fields' => [
        'title' => true,
        'excerpt' => true,
        '_created' => true,
        'slug' => true,
        'image' => true,
    ],
    'lang' => 'de',
];
// all entries, but I need 3 random ones
$entries = cockpit('collections')->find('members', $options);

I found a few options for MongoDB on Stackoverflow, but I look for a solution, that works with SQLite/MongoLite, too.

Before I use workarounds like

  • adding hidden fields with random integer values to all entries or
  • fetching the whole collection and picking 3 random entries afterwards,

I would prefer a simple filter option, that I just missed.

edit:

I found a working solution (code is for a different test collection), but now I have to request the database multiple times.

$x          = 5; // number of random items --> 5 database requests
$collection = 'posts';
$entries    = [];
$lang       = 'de';

$filter = [
    'published' => true,
];

$count = cockpit('collections')->count($collection, $filter);

if ($count > $x) {

    $keys = [];

    $i = 0;             // get x random keys in range of entries count
    while ($i++ < $x) { // should be fast for small $x and large $count
        while (in_array($num = mt_rand(0, $c), $keys));
        $keys[] = $num;
    }

    $options = [
        'filter' => $filter,
        'fields' => [
            'title' => true,
        ],
        'lang'  => $lang,
        'limit' => 1,
    ];

    foreach ($keys as $k) { // x db calls
        $options['skip'] = $k -1;
        $entries[] = cockpit('collections')->find($collection, $options)[0];
    }

} else {
    // return all entries
}

I would love to have something like ORDER BY RAND() LIMIT 3 (MySQL).

2

If you use mongodb you can utilize $sample - https://docs.mongodb.com/manual/reference/operator/aggregation/sample/

3

how can i use $sample in api request?