Category: Models & ORM

Q: In CakePHP 3, find 10 random blog posts from mysql fast

based on this article: MySQL select 10 random rows from 600K rows fast

I am thinking about finding random blog posts in CakePHP. How to use 

RAND()

in query builders?

In addition, can you speed up/optimize this query?

sql
2 Answers

Version: 3.3

User Order by RAND()

//find 10 random blog posts
$query = $posts->find()
      ->select($fields)
      ->where($conditions)
      ->order(['RAND()'])
      ->limit(10);

$ten_posts = $query->toArray();
Ksmacky
Ksmacky

30

According to MySQL select 10 random rows from 600K rows fast, try:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

Created: 8 Oct '16

Last Reply: 9 Oct '16

Replies: 2

Views: 857

Votes: 0

Welcome to Aero Coding!

Aero Coding is a CakePHP-focused Q&A community for professional and enthusiast cake bakers. It's built and run by you as part of the community.


Join Now Tour

Download Cakephp

Start baking your own CakePHP application!


Cakephp All Versions