In Laravel, how to combine where() and orderBy() when orderBy() is working on relationship?

Question

I have two Models, a Track model and an Artist model, where each Track model belongsTo() one Artist model. Track models have a number column and Artist models have a name column. I want to retrieve all Tracks where number = 1 and then display those sorted by their Artist's name. So far I have only figured out how to do one or the other, but not both.

I would expect to be able to do this with something like:

Track::with(['artist' => function($q) {
            $q->orderBy('name');
        }])->where('number', 1)->get();

This displays all tracks where number = 1, but the tracks are not sorted in any particular order. I get the same result if I remove the with() altogether. Conversely, if I remove the where() clause then I get all Tracks in the database, and they are sorted by the Artist's name. How can I combine these two functions? The results should look like:

[
  {
    "id": 17,
    "created_at": "2019-09-04 22:33:50",
    "updated_at": "2019-09-04 22:33:50",
    "number": 1,
    "title": "Iste omnis maxime inventore rerum nam et.",
    "artist_id": 2,
    "album_id": 4,
    "length": "06:08",
    "disc_number": 1,
    "artist": {
      "id": 2,
      "created_at": "2019-09-04 22:33:46",
      "updated_at": "2019-09-04 22:33:49",
      "name": "Ace",
      "image_id": "4"
    }
  },
  {
    "id": 35,
    "created_at": "2019-09-04 22:33:54",
    "updated_at": "2019-09-04 22:33:54",
    "number": 1,
    "title": "Doloremque quidem voluptatibus doloribus et.",
    "artist_id": 4,
    "album_id": 7,
    "length": "18:13",
    "disc_number": 3,
    "artist": {
      "id": 4,
      "created_at": "2019-09-04 22:33:46",
      "updated_at": "2019-09-04 22:33:53",
      "name": "Bar",
      "image_id": "10"
    }
  },
  {
    "id": 54,
    "created_at": "2019-09-04 23:00:08",
    "updated_at": "2019-09-04 23:00:08",
    "number": 1,
    "title": "Ut placeat assumenda aut.",
    "artist_id": 21,
    "album_id": 17,
    "length": "09:25",
    "disc_number": 4,
    "artist": {
      "id": 21,
      "created_at": "2019-09-04 23:00:08",
      "updated_at": "2019-09-04 23:00:08",
      "name": "Cat",
      "image_id": "22"
    }
  },
  {
    "id": 71,
    "created_at": "2019-09-04 23:00:11",
    "updated_at": "2019-09-04 23:00:11",
    "number": 1,
    "title": "Omnis et dolores odio a eius.",
    "artist_id": 22,
    "album_id": 20,
    "length": "16:48",
    "disc_number": 2,
    "artist": {
      "id": 22,
      "created_at": "2019-09-04 23:00:08",
      "updated_at": "2019-09-04 23:00:10",
      "name": "Dog",
      "image_id": "25"
    }
  },
]

score 1 · Accepted Answer · edited Sep 05 '19 at 19:45

1

Use something like this:

Track::with('artist')->join('artist', 'artist.id', '=', 'track.artist_id')->orderBy('artist.name', 'DESC')->get()

I hope you getting my point

edited Sep 05 '19 at 19:45

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Sep 05 '19 at 19:11

Vaibhav Joshi

101
4

This works for me because I am building the query programmatically based on user input so tagging a sortBy() at the end after get() is not ideal. Thank you! – clem Sep 05 '19 at 20:19

score 1 · Answer 2 · answered Sep 05 '19 at 19:11

You cannot sort by artist's name with your code because laravel, to eager load artists with tracks, executes two queries, you should use a join to have one query but I give two other options (I don't like joins):

1) You can use sortBy() or sortByDesc() (if you want to sort descending) on the resulting collection, e.g.:

$tracks = Track::with('artist')->where('number', 1)->get()->sortBy('artist.name');

2) You can start querying from Artist, but you have to change a bit your view, e.g.:

$artists = Artist::with(['tracks' => function($q) {
               $q->where('number', 1);
           }])->orderBy('name')->get();

3) You can use joins, see another answer please.

FYI if you want to debug a query just append ->toSql() in place of ->get() to get a string of your SQL.

This is good to know for in other situations, but in my situation I am building the query step by step based on user input so the join() works better for me. Thanks. — clem, Sep 05 '19 at 20:21
No probem, did I say I don't like joins? (with models) You should also consider, for the future, the second solution, if you change a bit your point of view is the same as starting with tracks. — dparoli, Sep 05 '19 at 20:28

In Laravel, how to combine where() and orderBy() when orderBy() is working on relationship?

2 Answers2