Zend_Db order by field Value

Question

I'm outputting the contents of a select menu from a model using this:

$select = $this->select();
$select->order('name');
return $this->fetchAll($select);

However, what i want to do is order by a specific value, and then by the name column. The SQL would look like this:

SELECT * FROM `names` ORDER BY `name` = 'SomeValue' DESC,`name`

SAMPLE SQL CODE:

CREATE TABLE IF NOT EXISTS `names` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `names` (`id`, `name`) VALUES
(1, 'rob'),
(2, 'dave'),
(3, 'andy'),
(4, 'paul'),
(5, 'jason'),
(6, 'john');

SELECT *
FROM `names`
ORDER BY `name` = 'john' DESC , `name`

RETURNS:

6   john
3   andy
2   dave
5   jason
4   paul
1   rob

and in what order does that (very weird) query return the entries? — markus, Jan 29 '10 at 15:24
$select->order("name = 'john' DESC , name"); does not work ? — Rufinus, Jan 29 '10 at 15:26
@tharkun: john, andy, dave, jason, paul, rob find it kinda strange to.. — Rufinus, Jan 29 '10 at 15:26
if this returns john first and then everybody else, then you really can't call this order by entry. it's much rather a take john out of the order, place him in the beginning and then order the rest by name. — markus, Jan 29 '10 at 15:27
nope, Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name = 'john'' in 'order clause' — robjmills, Jan 29 '10 at 15:28
can't find this in the mysql documentation either. Maybe you should work with IF THEN or CASE. I guess either way you'll have to write your query out, I don't think the Zend_Db wrapper supports this. — markus, Jan 29 '10 at 15:32
$select->setIntegrityCheck(false) doesnt work, docs say thats only relevant to table joins anyway — robjmills, Jan 29 '10 at 15:39

score 6 · Accepted Answer · answered Jan 29 '10 at 17:57

6

I believe this is what you are looking for:

$name = 'John';
$order = new Zend_Db_Expr($this->getAdapter()->quoteInto("name = ?", $name) ." DESC, `name`");
$select = $this->select();
$select->order($order);
return $this->fetchAll($select);

answered Jan 29 '10 at 17:57

Mark

6,254
1
32
31

score 0 · Answer 2 · answered Jan 29 '10 at 15:09

0

depending on your SQL DB, this is not supported. (i dont know a database which can sort directly by a string.)

what would you want to accomplish ? have fields with someValue at the first positions

answered Jan 29 '10 at 15:09

Rufinus

29,200
6
68
84

This is possible with MySQL as i use that query in an older system – robjmills Jan 29 '10 at 15:12
possible by not throughing an error, but dont sort like i would expect. can you give us an example table with data and what sorting you want to achive ? – Rufinus Jan 29 '10 at 15:15

Zend_Db order by field Value

2 Answers2

Linked