How to fetch records with order by Rand and an another field in order cake php 3?

Question

I am fetching video ids from database with random order in limit of 30 and but want to show videos with date of publish in desc in cake php 3. Please assist

please respond to the above query – Er Gagandeep Sethi Mar 04 '17 at 09:25 — Er Gagandeep Sethi, Mar 04 '17 at 09:25

score 0 · Answer 1 · answered Mar 04 '17 at 10:45

In fact, your question is very vague and you don't provide any code, that's why you are down-voted. Next time, try to show you tried something, because even if you did, this is a two-lines-long question.

I understood that :

You want to get 30 random videos from a certain database;
You want to sort them by publish date (desc).

So, try :

// Retrieve all ids
$ids = $this->Videos->find('list')->toArray();
$ids = array_keys($ids);

// Select 30 random ids from the ids list
$total = count($ids);
$count = 30 <= $total ? 30 : $total;
$selectedIds = [];
for($i = 0; $i < $count; $i++) {
    $newId = -1;

    do {
        $newId = rand(0, $total - 1);
    } while(!in_array($newId, selectedIds));

    $selectedIds[] = $newId;
}

// Now you got your ids in an array
$videos = $this->Videos->find('all')
    ->where(['id' => $selectedIds])
    ->order(['publish_date' => 'DESC'])
    ->limit(30);

It should be noted that you'll run into problems once you have a larger number of records... imagine the table has 1.000.000+ rows, things would probably already break when retrieving the list, even with lower numbers like 100.000 records, and hydration disabled, things would already slow down very notably. Efficient random order selects can be quite tricky, check for example: **http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast** — ndm, Mar 04 '17 at 14:03

How to fetch records with order by Rand and an another field in order cake php 3?

1 Answers1