Laravel Nova Filter using joined table

Question

I am trying to build a Laravel Nova Filter for my Model "Question", based on data from two different tables. I have figured out the exact SQL query I would need to use, but cannot figure out how to use it in the apply() method of the Nova filter.

Table "questions" columns: id, ...

Table "themes" columns: id, question_id, theme_id

The SQL Query I am trying to use:

SELECT questions.*
From questions
Inner JOIN themes on questions.id=themes.question_id
WHERE themes.theme_id = 3 (value taken from the Nova Filter options)

My Filter in Laravel Nova QuestionsThemeFilter.php:

class QuestionsThemeFilter extends BooleanFilter
{
    public $name = 'filter by theme';

    public function apply(Request $request, $query, $value) {
        // This is what I'm missing...
    }

    public function options(Request $request) {
        $themes = Themes::all();
        $themesArray = array();
        foreach($themes as $item) {
           $themesArray[$item['title']] = strval($item['id']);
        }
        return $themesArray;
    }
}

So, how exactly should I do this?

Other question: Since I am a beginner in Laravel, how exactly should I start debugging this? Using dd() doesn't work (the filter just breaks) and I don't know how I can debug this. Is there a way to dump some values from the apply method (for example the $value)?

Thanks in advance for any help, highly appreciated :-D

user_4273867 · Answer

Check the comments in the code

use Illuminate\Support\Arr;
use Illuminate\Support\Facades\Log;

class QuestionsThemeFilter extends BooleanFilter
{
    public function apply(Request $request, $query, $value) {
        // Since you are using BooleanFilter $value will be an associate array with true/false value
        $selectedThemes = Arr::where($value, function ($v, $k) {
            return $v === true;
        });
        $selectedThemeIds = array_keys($selectedThemes)

        // For debugging print some logs and check
        Log::info($value)
        Log::info($selectedThemeIds);
        
        return $query->join('themes', 'questions.id', '=', 'themes.question_id')
              ->whereIn('themes.theme_id', $selectedThemeIds);

        // If you are using normal filter then below code will be enough
        /*            
        return $query->join('themes', 'questions.id', '=', 'themes.question_id')
              ->where('themes.theme_id', $value);
        */
    }

    public function options(Request $request) {
        // You can use Laravel collection method pluck
        return Themes::pluck('id', 'title')->all();
    }
}

References:

Pluck

Boolean Filter

Arr::where

Laravel Nova Filter using joined table

1 Answers