query builder distinct() it doesn't not working

so i want convert query from sql to laravel builder

SELECT DISTINCT YEAR(publish), research_group_id FROM research_group_statuses WHERE research_group_id = 1 AND status = 'basic' ORDER BY publish ASC;

Is this correct? because distinct() doesn't work at all

 $filterOne = ResearchGroupStatus::query()
            ->whereIn('status', ['Basic'])->where('research_group_id', 1)
            ->orderBy('publish', 'asc')->distinct()->get(['publish', 'status', 'research_group_id']);

view blade

<form action="#" method="GET">
    <select class="select-option rounded" id="publish" name="publish" aria-label="filtering periode">
        <option selected>Filter Year</option>
        @foreach ($filterOne->where('status', 'Basic') as $year)
            <option value="{{ $year->getYear() }}">{{ $year->publish }}</option>
        @endforeach
    </select>
    <button type="submit" class="btn btn-success">Filter</button>
</form>
>

jlrdw

2 years ago

Level 75

What happens if you leave out DISTINCT? And maybe you need a GROUP BY. Note just suggestion to try as I don't know your database.

1 like

s4muel

2 years ago

Best Answer

Level 50

in your SQL example, you select only the year of the published date YEAR(publish), i suppose the publish column contains a full date and time, so the rows are disctinct (arent the same anymore), so all of them get selected.

try something like this:

 $filterOne = ResearchGroupStatus::query()
            ->whereIn('status', ['Basic'])->where('research_group_id', 1)
            ->orderBy('publish', 'asc')->distinct()->get([DB::raw('YEAR(publish) as year', 'status', 'research_group_id']);

or ResearchGroupStatus::select([DB::raw('YEAR(publish) as year'), 'status', 'research_group_id'])->distinct()...

Please or to participate in this conversation.