Cakephp pagination with more than one order condition

Question

Currently I have a table called as 'Users'. With columns like

Id, name, image, is_active, etc, etc, created, modified

I am setting up a pagination, where it will display all Users one by one, on click of next button(in the view)

here not all Users will have images. So my pagination order will be: to show users who have images first. Followed by Users who don't have images.

So far I have this

    $this->paginate = array(
      'limit' => 1,
      'conditions' => array(
        'User.is_active' => 0
        ),
      'order' => array(
        'User.image' => 'DESC'
        ),
      'group' => array(
        'User.id'
        )    
      );

What I want is now, the view to FIRST display a random User, who has image, every time the page refreshes OR on the first load of the page. Followed by other Users who have images ,and then followed by Users who don't have images.

this query works fine in phpmyadmin sql panel. But I am not sure how do I write a cakephp paginate query for it

select * User order by image<>' ' desc,rand()

If you think I am missing any more data, please ask. I'll write it in comments

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

you can look at this answer SQL how to make null values come last when sorting ascending and try to change it with CakePHP conditions.

edited May 23 '17 at 12:05

Community

1
1

answered Nov 03 '14 at 08:07

Miheretab Alemu

956
2
20
43

score 0 · Answer 2 · answered Nov 03 '14 at 10:36

In your model you can set. I believe this works for the pagination order as well.

    public $order = array(
        'ISNULL(Users.image) ASC',
        'RAND() * User.id'
    );

But this makes all the users randomized. I don't know how you may do it to show only in the first record a random user with the order field. I would suggest using a custom function in which you may choose a random row and put it in the top of the array.

Cakephp pagination with more than one order condition

2 Answers2