Order By Is Not Working Properly?

Yii 1.1 General Discussion
1

I have the code below and order by is not working,


$criteria = new CDbCriteria();

                $criteria->condition = 'status=1';

                $criteria->order = 'dob ASC';                

                $records = Priest::model()->findAll($criteria);

		

		return $records;

the output below is order by YEAR not by the date for the above code

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1 JoseVadakkel 21, Feb 1965

2 ArulNirmal 13, Feb 1975

3 ShaijuChacko 12, Feb 1977

4 AmalaSingh 11, Feb 1979

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

How to resolve this?

2

[size=2]Hi can you please try this [/size]


$criteria->order = 't.dob ASC';

are you insert the full date on dob field or just date?

I am not sure it’s work or not.

3

Hi

Please make a raw query directly in your database


select * from Priest_table_name WHERE status=1 ORDER BY dob ASC

what results?

4

Hi,

Just write this code in your model Priest

and remove this line from your code

$criteria->order = ‘dob ASC’;

/**

 *


 * @return  array contains scope for sorting


 */


public function defaultScope()


{


	return array(


		'order'=>'dob ASC',


	);


}

Thanks,

chandran nepolean

5

Thanks to all, tried all your suggestions… None works as per my wish. but when I execute this query,

select * from Priest_table_name WHERE status=1 ORDER BY dob ASC

the same result… it order by YEAR … not the date number?

6

So, the problem is in table of database.

what is the format of this field ? datetime, timestamp or string?

7

it is date field… check attachment

8

Hi try this




                $criteria = new CDbCriteria();

                $crietreia ="DATENAME(yyyy, dob) AS Year"

                $criteria->condition = 'status=1';

                $criteria->order = 'Year ASC';                

                $records = Priest::model()->findAll($criteria);

                

                return $records;

I hope it’s some help.

9

Field date are ordered according to the timeline, so year, month and date.

If you need an order by birth date you have to order by DATE_FORMAT(dob, ‘%m%d’)

10

if the type of your field is datetime you shouldn’t have any issue

but your results seems corrected!

1 JoseVadakkel 21, Feb 1965

2 ArulNirmal 13, Feb 1975

3 ShaijuChacko 12, Feb 1977

4 AmalaSingh 11, Feb 1979

11

Thanks, finally this worked for me,

DATE_FORMAT(dob, ‘%m%d’)