CODEWITHSUNDEEP
phpmysql
Ben
Ben

Reputation: 62356

Sort priority in SQL results

Below is part of a library that generates lists of models.... the ->getBy() portion merely takes the SQL and puts it after a WHERE clause in SQL. So it'd execute as: WHERE site_id = ? ORDER BY name ASC

/**
 * Get areas
 */
public function getAreas() {
    return $this->modelFactory('area')->getBy('site_id = ? ORDER BY name ASC', array($this->siteId));
}

/**
 * Get nested areas
 */
public function getNestedAreas() {
    $nestedAreas = array();
    $areas = $this->getAreas();
    if (is_array($areas)) {
        foreach ($areas as $area) {
            //parent areas
            if ($area->getParentId() == NULL) {
                $nestedAreas[$area->getId()]['area'] = $area;
            } else {
                $nestedAreas[$area->getParentId()]['subareas'][] = $area;
            }
        }
        $areas = $nestedAreas;
    } else {
        $areas = array();
    }
    return $areas;
}

The end result is an array like:

array( array( 'area' => new areaModel() //areaModel for Kentucky 'subareas' => array( new areaModel(), //areaModel for Cincinatti new areaModel(), //areaModel for Louisville new areaModel() //areaModel for Lexington ) ) )

The problem with this setup is that the original ORDER BY will put Ohio at the top of my nested areas because it's sub area Akron / Canton shows up first by the ORDER BY. Like this:

            <select name='area_id'>
                <optgroup label='Ohio'>
                     <option value='905'>Akron / Canton</option>
                     <option value='1154'>Cincinnati</option>
                     <option value='1155'>Cleveland Eastside</option>
                     <option value='908'>Cleveland Westside</option>
                     <option value='910'>Dayton</option>
                     <option value='1394'>Toledo</option>
                </optgroup>
                <optgroup label='Alabama'>
                     <option value='988'>Birmingham, AL</option>
                     <option value='1224'>Huntsville</option>
                     <option value='712'>Mobile / Baldwin County</option>
                ....

Is it possible to simply adjust my query to properly order the results by those with parent_id IS NULL before those that have an integer value for parent_id

Upvotes: 0

Views: 102

Answers (1)

Jim H.
Jim H.

Reputation: 5579

Like MSSQL's isnull, MySQL has ifnull.

ORDER BY IFNULL(parent_id, 0)

will place null parent_id's before those with an actual value.

Upvotes: 1

Related Questions