zend-frameworkmysqlsql-order-byclause

Zend framework SQL select query construction (ORDER BY)

I have a database with VARCHAR column url. I would like to fetch rows so that the ones that have any url value have priority over other rows, but are ordered by date row (descending), so ORDER BY 'url' DESC, 'date' DESC wouldn't work as it would order them alphabetically first. Basically, it would look something like this:

Table:

ID   |    Url    | Date
1    | http://...| 1001
2    |           | 1002
3    |           | 1003
4    | http://...| 1005
5    | http://...| 1004

Sorted:

ID   |    Url    | Date
4    | http://...| 1005
5    | http://...| 1004
1    | http://...| 1001
3    |           | 1003
2    |           | 1002

What would be the proper zend framework way (or at least SQL query) to do it?

Solution

  • With SQL, you could so something like...

    It gets kind of ugly if you allow null values on the url field as well.

    SELECT * , IF(LENGTH(url) = 0 OR url IS NULL, 1, 0) AS nourl 
FROM url ORDER BY nourl ASC

    This basically checks to see if the url length is greater than zero or is null. If they are, they are at the bottom of the sort.