OrderBy multiple columns

Thanks for the quick on point response!

And I should have given a more in-depth example. if there would also be a (7, 5, 'D') (8, 5, 'C'), having a parent that has a parent, they do not follow.

http://www.sqlfiddle.com/#!9/5520a8/1

Edit: If I do ​ orderByRaw('coalesce(id, parent_id), parent_id is not null, title')->paginate($items); ​ They are listed just beneath the id, just not in alphabetical order.

Hi,

I'm using mySQL.

Sadly can't say I know raw SQL, but seeing @michaloravec 's link, hope this is sufficient

CREATE TABLE models
	(`id` int, `parent_id` int, `title` varchar(1))
;
	
INSERT INTO models
	(`id`, `parent_id`, `title`)
VALUES
	(1, NULL, 'A'),
	(2, NULL, 'C'),
	(3, NULL, 'B'),
	(4, 3, 'F'),
	(5, 1, 'E'),
	(6, 3, 'D'),
	(7, 5, 'D'),
	(8, 5, 'C'),
	(9, NULL, 'C'),
	(10, 8, 'C'),
	(11, 9, 'B')
;

The result I'm looking for

(1, NULL, 'A')
- (5, 1, 'E')
- - (8, 5, 'C')
- - - (10, 8, 'C')
- - (7, 5, 'D')
(3, NULL, 'B')
- (6, 3, 'D')
- (4, 3, 'F')
(2, NULL, 'C')
(9, NULL, 'C')
- (11, 9, 'B')

Added the "-" to have it lined up to be have my example be clearer.

Having row's id be followed by those that have it as parent_id. Having those follow a general alphabetical order on title, like (2, NULL, 'C') being under (3, NULL, 'B') and those following it through parent_id in above example).

EDIT: Added padding to the breadcrumb

If using MySQL 8, this should do the trick:

<?php

// ./routes/web.php

use Illuminate\Support\Facades\Route;

class Model extends \Illuminate\Database\Eloquent\Model {
    // protected $table = 'models'; // Table name gussed by Laravel
}

Route::get('/', function () {
    $sql = <<<SQL
WITH RECURSIVE `pad` (`length`) AS (
    SELECT CHAR_LENGTH(CONCAT(MAX(`id`), '')) AS `length`
    FROM `models`
),
`input` (`id`, `parent_id`, `title`, `rowid`) AS (
    SELECT
        `models`.`id`,
        `models`.`parent_id`,
        `models`.`title`,
        LPAD(CONCAT(ROW_NUMBER() OVER (ORDER BY `title`), ''), `pad`.`length`, '0') as `rowid`
    FROM `models`, `pad`
),
`source` (`id`, `parent_id`, `title`, `breadcrumb`) AS (
    SELECT
        `id`,
        `parent_id`,
        `title`,
        `rowid` AS `breadcrumb`
    FROM `input`
    WHERE `parent_id` IS NULL
    UNION ALL
    SELECT
        `input`.`id`,
        `input`.`parent_id`,
        `input`.`title`,
        CONCAT_WS('.', `source`.`breadcrumb`, `input`.`rowid`)
    FROM
        `input`
        INNER JOIN `source`
            ON `input`.`parent_id` = `source`.`id`
)
SELECT `id`, `parent_id`, `title`
FROM `source`
ORDER BY `breadcrumb`
SQL;

    return Model::query()->fromQuery($sql);
});

Here I used a less known Eloquent feature to hydrate a Collection of models from a raw SQL query. The ->fromQuery() method also accepts an array of bindings values in case you need them.

As I am assuming MySQL 8, I used a new feature called Common Table Expressions (CTE), more specifically Recursive CTEs

Reference: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

The first CTE (pad) is not actually recursive , but MySQL requires the RECURSIVE keyword close to the WITH keyword when one of the CTEs are recursive.

The idea is similar to using sub-queries. But that you build them in steps and can reuse them one or more times later.

The first CTE (pad) calculates the length of the largest ID so we can later build sortable breadcrumbs.

The second CTE (input) uses a window function (ROW_NUMBER) to create an index ordered on each records title.

The third CTE (source), the recursive one, uses the index created in the previous step to build a breadcrumb, using the a record's parent index. As the breadcrumb is delimited it does what you want for sorting.

A recursive CTE is a query that references itself. You have a seed query (the part before the UNION ALL) and then you the recursive query referencing how you named your CTE.

If you run that query in MySQL, adding the breadcrumb to its SELECT, you would get something like this:

+--+---------+-----+-----------+
|id|parent_id|title|breadcrumb |
+--+---------+-----+-----------+
|1 |NULL     |A    |01         |
|5 |1        |E    |01.10      |
|8 |5        |C    |01.10.05   |
|10|8        |C    |01.10.05.07|
|7 |5        |D    |01.10.09   |
|3 |NULL     |B    |02         |
|6 |3        |D    |02.08      |
|4 |3        |F    |02.11      |
|2 |NULL     |C    |04         |
|9 |NULL     |C    |06         |
|11|9        |B    |06.03      |
+--+---------+-----+-----------+

EDIT I updated to use your last example data

I know it can be a bit overwhelming when one first sees this, but I hope it makes a bit of sense.

One note: if you are using MySQL 5, there is a workaround that might work, using variables. Not sure it will work, but we might give a shot, if needed.

Hope it helps.

@tikay , in case you were testing my last response's code already, I updated due to sorting numeric strings problems.

Now it should cover your needs.

Amazing, thanks!

And yep, somewhat overwhelming, will have to up my knowledge on that.

Picked up Laravel a month ago, after 2 years of Wordpress usage, so yep, still a lot to learn ^^

You too!

Yeah my bad, didn't think about a parent_id having a parent_id and how they get sorted when I wrote my initial example.

Sadly query and relationship still blow my mind.

I'd gladly replace it with less complicated code, if it's not too much trouble I'd be greatly appreciated.

@tikay , indeed @michaloravec suggestion on using relationships seems to be a more Laravel-y solution.

Maybe I was excited on revisiting CTE queries that I jumped straight to them. I've used them a lot prior in my career when the company I worked for only allowed us to code on Microsoft SQL Server or Excel (not an IT company, I was a BI analyst there, some companies here restrict which software you can install in a company's computer).

Using relationships would still have the need to present the data hierarchically where each nested set of children nodes needs to be presented close to its parent.

Also Laravel would make as many requests as the deepest level you need to fetch (probably more than the amount of levels as levels go deeper).

<?php

// ./routes/web.php

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Route;

class Model extends \Illuminate\Database\Eloquent\Model
{
    // protected $table = 'models'; // Table name guessed by Laravel

    public function children()
    {
        return $this->hasMany(Model::class, 'parent_id');
    }

    public function childrenWithChildren()
    {
        return $this->hasMany(Model::class, 'parent_id')
            ->with(['childrenWithChildren']) // recursive relation
            ->orderBy('title');
    }

    public function scopeRootNodes(Builder $builder)
    {
        $builder->whereNull('parent_id');
    }
}

Route::get('/related', function () {
    return Model::query()
        ->with(['childrenWithChildren'])
        ->rootNodes()
        ->orderBy('title')
        ->get();
});

Note that here, as in my previous code sample, I am defining the model directly in the ./routes/web.php file for simplicity. In your app move the model code to its own class in the ./app/Models/ folder and consider using a controller for your route.

The result will be:

[
  {
    "id": 1,
    "parent_id": null,
    "title": "A",
    "children_with_children": [
      {
        "id": 5,
        "parent_id": 1,
        "title": "E",
        "children_with_children": [
          {
            "id": 8,
            "parent_id": 5,
            "title": "C",
            "children_with_children": [
              {
                "id": 10,
                "parent_id": 8,
                "title": "C",
                "children_with_children": []
              }
            ]
          },
          {
            "id": 7,
            "parent_id": 5,
            "title": "D",
            "children_with_children": []
          }
        ]
      }
    ]
  },
  {
    "id": 3,
    "parent_id": null,
    "title": "B",
    "children_with_children": [
      {
        "id": 6,
        "parent_id": 3,
        "title": "D",
        "children_with_children": []
      },
      {
        "id": 4,
        "parent_id": 3,
        "title": "F",
        "children_with_children": []
      }
    ]
  },
  {
    "id": 2,
    "parent_id": null,
    "title": "C",
    "children_with_children": []
  },
  {
    "id": 9,
    "parent_id": null,
    "title": "C",
    "children_with_children": [
      {
        "id": 11,
        "parent_id": 9,
        "title": "B",
        "children_with_children": []
      }
    ]
  }
]

First thought: much easier! And indeed it is much easier.

There are some few concepts to learn about for a new comer, such as query scopes and relationships that will be more useful to add to your Laravel tool belt than learning SQL Recursive CTEs. Maybe the childrenWithChildren relation is a bit weird as it references itself on its definition, but much less complicated than the odd SQL syntax.

Also results are the desired order, great!

Problems arise when you want to present these results. You might have noted the child records are nested within their parent records.

I don't know how to it without recursion. @michaloravec if you know how to traverse this tree-like result without recursion I would like to learn. Asking genuinely, please don't think I am being picky about it, you are one of the forums members which responses and contributions I really appreciate and learn from them.

As I just said, the way I know to flatten this nested set is by using recursion. Let's see a way to do it:

<?php

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Route;

class Model extends \Illuminate\Database\Eloquent\Model
{
    // protected $table = 'models'; // Table name guessed by Laravel

    public function children()
    {
        return $this->hasMany(Model::class, 'parent_id');
    }

    public function childrenWithChildren()
    {
        return $this->hasMany(Model::class, 'parent_id')
            ->with(['childrenWithChildren']) // recursive relation
            ->orderBy('title');
    }

    public function scopeRootNodes(Builder $builder)
    {
        $builder->whereNull('parent_id');
    }
}

Route::get('/related', function () {
    return Model::query()
        ->with(['childrenWithChildren'])
        ->rootNodes()
        ->orderBy('title')
        ->get()
        ->flatMap($flatten = function (Model $model) use (&$flatten) {
            $children = $model->childrenWithChildren;
            $model->unsetRelation('childrenWithChildren');

            return [$model, ...$children->flatMap($flatten)];
        });
});

And the results:

[
  {
    "id": 1,
    "parent_id": null,
    "title": "A"
  },
  {
    "id": 5,
    "parent_id": 1,
    "title": "E"
  },
  {
    "id": 8,
    "parent_id": 5,
    "title": "C"
  },
  {
    "id": 10,
    "parent_id": 8,
    "title": "C"
  },
  {
    "id": 7,
    "parent_id": 5,
    "title": "D"
  },
  {
    "id": 3,
    "parent_id": null,
    "title": "B"
  },
  {
    "id": 6,
    "parent_id": 3,
    "title": "D"
  },
  {
    "id": 4,
    "parent_id": 3,
    "title": "F"
  },
  {
    "id": 2,
    "parent_id": null,
    "title": "C"
  },
  {
    "id": 9,
    "parent_id": null,
    "title": "C"
  },
  {
    "id": 11,
    "parent_id": 9,
    "title": "B"
  }
]

Great! In the order we wanted! And around 5-6 lines of code shorter then the raw SQL version.

although that is debatable as we could write the SQL in a much terser way, for example by listing all the SELECT columns in one line.

The trick was calling the ->flatMap(...), but there is one catch: As we needed the callback closure to be recursive we had to:

• Assign the callback closure to a variable ($flatten = function...)
• Add this variable as a closure variable to the callback, but passing it by reference: use (&$flatten) (note the & before the variable name).

Passing it by reference is needed here because we need to reference the callback closure inside itself to call it recursively, but it is still not yet assigned as the closure object is built after the assigment.

Bottom line:

Overall the "complicated" part was just moved from SQL to PHP. But I will concede the Laravel solution brings you much more value, as it is more likely you would reuse those concepts (query scopes, relationships, recursive callbacks) later in future projects.

But I still think the CTE solution has some advantages over the Laravel approach. mainly when your dataset has deeply nested sets. As I said before Laravel would issue lots of queries to get all the child records one level at a time.

CTEs also come handy on other scenarios than traversing hierarchical datasets, I highly recommend you, or anyone, to learn more about them.

Nevertheless, I think learning both techniques will make you a better developer in the long run.

Sorry if the first response was too overwhelming. Hope this one is easier to grasp.

Much appreciated, and thanks for the thorough explanation!

And will learn about both, but a step at a time :) I tend to learn easier with using examples, "pull them apart" and play around with them, is atleast how I've done it so far.

To be honest, most things I don't understand feel overwhelming, until I understand it more.

Again, thanks!

You’re welcome! Have a nice day =)