How to perform this sort of JOIN query in Laravel?

I have a laravel 5.2 site that has guitar lessons which consist of exercises. Exercises have difficulty rating and for a lesson its difficulty is set to maximum difficulty of the contained exercises. I want to be able to sort lessons by difficulty.

For lessons, I have the following in a searchresults composer:

public function createModel($request, $searchParameters)
    {
        $url = $this->clearPagination($request->fullurl());
        $model = [
            'sort' => static::createSort($url),
            'free' => static::createFree($url),
            'difficulty' => static::createDifficultyList($url, $request['difficulty']),
            'category' => static::createCategoryList($url, $request['category']),
        ];

            $key = array_key_exists('category', $searchParameters);
            if (($key) && count($searchParameters['category'] == 1)) {
                $id = $searchParameters['category'];
                $subcats = \DB::select("SELECT id FROM categories WHERE parent_id = {$searchParameters['category']}");
                $scs = [];
                if (!empty($subcats)) {
                    foreach ($subcats as $sc) {
                        $scs[] = $sc->id;
                    }
                    unset($searchParameters['category']);
                    $searchParameters['category'] = array_merge([$id], $scs);
                }
            }

        $selectedMode = $request['mode'] ?? 'lessons';
        if ($selectedMode == 'lessons')
        {
            $result = $this->searchService->searchLessons($searchParameters);

            $model['lessons'] = $result['lessons'];
            $model['mode'] = static::createMode($url, 'lessons');
        }
        elseif ($selectedMode == 'exercises')
        {
            $result = $this->searchService->searchExercises($searchParameters);

            $model['exercises'] = $result['exercises'];
            $model['mode'] = static::createMode($url, 'exercises');
        }
        else throw new Exception('Unknown search mode.');

        $model['count'] = $result['count'];
        $model['pagination'] = static::createPagination($result['count'], $result['page']['skip'], $result['page']['take'], $url);

        return $model;
    }

And in the search service the SortLessons function (called above):

private function sortLessons($query)
    {
        if (isset($this->parameters['sort']))
        {
            switch ($this->parameters['sort'])
            {
                case 'popularity': $query->orderByRaw('(hits / (1 + datediff(now(), created_at))) desc'); break;
                case 'difficulty': ;
                case 'date_desc': $query->orderBy('created_at', 'desc'); break;
                case 'date_asc': $query->orderBy('created_at', 'asc'); break;
                default: throw new Exception('Unknown sort value.');
            }
        }
        else
        {
            $query->orderBy('created_at', 'desc');
        }
        return $query;

I am trying to figure out efficient way to perform a query that sorts lessons by difficulty given the way lesson difficulty is determined as described above. I am a guitarist taking over some dev work on my site from the developers, so my laravel skills are not all that great. But I have decent working knowledge and am comfortable with PHP and MySQL. Any tips are greatly appreciated.

If no one were to suggest something, I would end up querying a list of lessons, then looping through each lesson to determine its difficulty, then reordering lessons based on those difficulties. But I know there is a better way :-)

Thanks!

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2tL0FrR
via IFTTT