在Eloquent Not Quite Working中加入查询按相关表排序

Question

I have a few tables in my database:

• Questions <-(Many to Many)-> Surveys
• Questions (Has Many)-> Answers
• Questions (Has Many)-> Orders *for sorting
• Survey (Has Many)-> Orders *for sorting questions within a survey

My goal is to load all the questions within a given survey and sort them in a specific order. However, I cannot seem to make this work. I've tried many different variations of the query below, and it always "almost" works, but a few questions will be out of order. I'm not great with SQL so sorry if I'm missing something obvious.

$questions = Survey::find($survey->id)->questions()
                    ->join('orders as o', 'o.question_id', '=', 'questions.id')
                    ->orderBy('o.order',  'desc')
                    ->groupBy('questions.id')
                    ->get();

I'm then adding them to an array and calling them in the view with:

($survey_questions[$survey->id] as $q)

UPDATE

Well... I don't think this is probably the best solution, (so I'm not marking it as an answer) but at least it works. To get things to order correctly, I had to initially select surveys and build joins from there, then create a separate array of question_id => answers to cycle through in my view... it works, but I'm quite sure it isn't ideal.

$survey_responses = array();
        $survey_questions = array();
        $question_answers = array();
        foreach($surveys as $survey) {
            $responses = $survey->responses()->where('survey_id', '=', $survey->id)->count();
            $survey_responses[$survey->id] = $responses;

            $questions = Order::where('survey_id', '=', $survey->id)                    
                        ->join('questions as q', 'q.id', '=', 'orders.question_id')
                        ->select('q.*') // add anything you need here
                        ->orderByRaw('orders.order asc')
                        ->groupBy('q.id')
                        ->get();

            $survey_questions[$survey->id] = $questions; 

            //make question_id => answers array because I can't figure out the damn select query
            foreach($survey->questions as $question) {
                foreach($question->answers as $answer) {
                    $question_answers[$question->id][$answer->id] = $answer->answer;
                }
            }
        }

Answer 1

When working with eloquent you need to specify the columns you want to select if using joins. Otherwise ids (and possibly other columns) will be overriden by the related table's values, and the result is wrong.

$questions = Survey::find($survey->id)->questions()
     ->join('orders as o', 'o.question_id', '=', 'questions.id')
     ->orderBy('o.order',  'desc')
     ->groupBy('questions.id')
     ->select('questions.*') // add anything you need here
     ->get();