CODEWITHSUNDEEP
yii2
Anthonius
Anthonius

Reputation: 340

Order by twice in Yii2

How can I order by the same column twice in yii2?

select * from (
   select * from your_table order by id desc limit 20) 
tmp order by tmp.id asc

Why I need to sort twice? I have Events table. I want to show events which its eventDate is larger than today then sort it using asc. 

$events = Events::find()->where(['>=', 'eventDate', $today])->andWhere(['status' => 1])->orderBy(['eventDate' => SORT_ASC])->limit(5)->all();

Because I want to show at least 5 events, if the result of the query above is less than 5, I need to findAll event which eventDate is smaller than today, and sort it by descending, but I want to show it using ascending.

if(count($events)<4){
        $need = 4 - count($events);
        $pastEvents = Events::find()->where(['<', 'eventDate', $today])->andWhere(['status' => 1])->orderBy(['eventDate' => SORT_DESC])->limit($need)->all();

    }

example : today is 6th June. There are events on 1st, 2nd, 3rd, 4th, 5th, 7th and 8th June. First, the result I get is 7th and 8th. Because the event is still 2, I want to get 3 more events which has the nearest date with today. So with eventDate smaller than today, I get 1st, 2nd, 3rd, 4th and 5th. Then I need to sort it using descending and limit by 3 to get 3rd, 4th and 5th. Because it is using descending, I get 5th, 4th and 3rd. But I want the final result become 3rd, 4th, 5th, 7th and 8th.

Upvotes: 0

Views: 106

Answers (2)

P.Shatalov
P.Shatalov

Reputation: 92

You are using subquery, so you can use everything you use for a simple query.

$subQuery = (new Query())->select('id')->from('user')->where('status=1')->orderBy('id');

$query->from(['u' => $subQuery])->orderBy('u.id');

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133400

A simple way is use of findBySql 

$sql = "select * from (
   select * from your_table order by id desc limit 20) 
   tmp 
   order by tmp.id asc"

YourTableOrderMOdel::findBySql()->count( $sql)->all();

Upvotes: 1

Related Questions