Collection of MySQL Stuffs and Bits (As They Come In)

Cats: laravel, mysql

By on

For my own reference (and so whoever might find them useful). Sometimes just MySQL and sometimes in Laravel-esican.

Nullable integer column: Order numerically, except with NULL last

Source: http://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last

MySQL

SELECT * FROM table ORDER BY ISNULL(field), field ASC;

Laravel

SomeModel::orderByRaw('ISNULL(field)')
	->orderBy('field','ASC')
	->get();

Regular integer column: Order numerically, except with ZEROs last

MySQL

SELECT * FROM table ORDER BY field!=0 DESC, field asc

Laravel

SomeModel::orderByRaw('field!=0 DESC')
	->orderBy('field','ASC')
	->get();

Order by, ignoring “the”

Laravel

$orderBy = DB::raw('CASE WHEN LEFT(`name`,4) ="The "
	THEN REPLACE(`name`, "The ", "")
	ELSE `name`
	END');
SomeModel::orderBy($orderBy, 'ASC');