CODEWITHSUNDEEP
phpmysqllaraveleloquentforeign-keys
J. Robinson
J. Robinson

Reputation: 929

Laravel Eloquent orderBy() parent table field in a One to Many Relationship

So I have the table questions and the table steps. The steps can have many questions. The question belongs to a "step".

I have a step_id field in my questions table, which is a foreign key of the id field in my steps table. I also have a number field in my steps table that does NOT have anything to do with the id field. It's just a number (1-12).

Questions Table

---------------------------
| id | step_id | question |
---------------------------

Steps Table

-----------------------------
| id | number | description |
-----------------------------

I have the relationship working fine as I can create, update, and delete questions from the questions table. However, I'm working on the index page, and I want to grab all the questions and sort them by the number field in the steps table.

Ive done a little bit of research and I've found this little bit of code from Laracasts, but it's not working. Not much information was given on the site. Do I need a dependency to get this functionality, or is there a native way to do this in laravel.

$questions = Question::join('steps', 'questions.step_id', '=', 'questions.id')
->orderBy('questions.number', 'asc')
->get(['questions.*']);

I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'questions.number' in 'order clause' (SQL: select questions.* from questions inner join steps on questions.step_id = questions.id order by questions.number asc)

Maybe I just don't understand relationships well enough to do this. But i'm at a loss. Any help would be appreciated.

Upvotes: 1

Views: 2777

Answers (1)

DevK
DevK

Reputation: 9942

Let's break down what's wrong with this query (also note that you're doing a normal SQL join here, not using the Eloquent relationship - which is correct for this case):

// You are comparing the `step_id` on `questions` to the `id` on `questions`
// Which doesn't make sense, you should compare it to the `id` on `steps` table
$questions = Question::join('steps', 'questions.step_id', '=', 'questions.id')
    // You're trying to order by a column on `steps` table, but
    // you're explicitily looking for it on `questions` table
    ->orderBy('questions.number', 'asc') // <-- you get the SQL error because of this
    ->get(['questions.*']);

Now let's fix those issues:

$questions = Question::join('steps', 'questions.step_id', '=', 'steps.id')
    ->orderBy('steps.number', 'asc') 
    ->get(['questions.*']);

Upvotes: 6

Related Questions