Multiple ordering

[ionesculiviucristian]

Hi. Is there a way to order by 2 joins? Example:

Specialization belongs to College that belongs to University.
When listing specializations, I want them ordered by University, College.
I did that with raw queries but don't know if I can with the package.

Thanks.

[fico7489]

yes of course you call orderByJoin 2 times, something like this :

$specializations = Specializations:orderByJoin('college.title', 'asc')
    ->orderByJoin('college.university.title', 'asc')
    ->get();

[Sharptsa]

Edit : I'm using MySQL 5.7

I think there is an issue with this because of all sorts having the same alias.

Example:

$query = Document::query()
            $query->orderByJoin('documentType.order IS NOT NULL', 'DESC');
            $query->orderByJoin('documentType.order = 0', 'ASC');
            $query->orderByJoin('documentType.slug', 'asc');

I have a special sort here that basically says : if NULL put last. if order = 0, put after everything else (that is NOT NULL), then order by slug.

It gives me this query:

select document.*,
       MAX(document_type.order IS NOT NULL) as sort,
       MAX(document_type.order = 0)         as sort,
       MAX(document_type.slug)              as sort
from `document`
       inner join `document_type`
                  on `document_type`.`id` = `document`.`document_type_id`
group by `document`.`id`
order by sort DESC, sort ASC, sort asc;

Result:

id	document_type_id	sort	sort	sort
12	5	1	1	Type 1
41	5	1	1	Type 1
207	4	1	0	Type 2
37	3	1	0	Type 2

As you see, documents with Type 2 should be first (Type 2 order is 1, Type 1 order is 0) but it's not the case.

If I change the query to add different aliases to the multiple sorts, it works as expected.

select document.*,
       MAX(document_type.order IS NOT NULL) as sort0,
       MAX(document_type.order = 0)         as sort1,
       MAX(document_type.slug)              as sort2
from `document`
       inner join `document_type`
                  on `document_type`.`id` = `document`.`document_type_id`
group by `document`.`id`
order by sort0 DESC, sort1 ASC, sort2 asc;

Result:

id	document_type_id	sort0	sort1	sort2
207	4	1	0	Type 2
37	4	1	0	Type 2
12	5	1	1	Type 1
41	5	1	1	Type 1

[fico7489]

this is fixed here : 220aa02