Laravel Nova - Problems with distinct()

Question

I am using Laravel Nova to generate metrics, in this case specifically a trend. Here is my code:

$builder = order::query()->select('cook_id')->distinct();
return $this->countByDays($request, $builder)->showLatestValue();

However this is returning 6 - but it should be 4.

Yes there are 6 rows, but four of them have the same 'cook_id' and thus the distinct call should be eliminating 2 of those.

To reiterate, this is in Laravel Nova's "trend" metric.

Thanks

Have you found solution for this? – Jigar Mar 25 '22 at 11:12 — Jigar, Mar 25 '22 at 11:12

score 1 · Answer 1 · answered May 27 '21 at 15:30

1

I know this an old post, but for anyone having the same problem, you can use a raw expression to allow for the distinct query.

public function calculate(NovaRequest $request)
{

    return $this->aggregate($request,
        order::class,
        self::BY_DAYS,
        'count',
        DB::raw('distinct cook_id'),
    );
}

By using DB::raw, nova will accept the column as is and not attempt to wrap it.

answered May 27 '21 at 15:30

Jack Stowey

11
2

is there any way we completely overwrite and show result based on custom query ? – Jigar Mar 25 '22 at 11:12

score 0 · Answer 2 · answered Oct 23 '18 at 06:46

0

Try to use groupBy

$builder = order::distinct()->select('cook_id')->groupBy('cook_id')->get();

also in database.php set mysql strict to false

'mysql' => [
    ...
    'strict' => false
]

answered Oct 23 '18 at 06:46

alexandru.gaidei

71
8

Thanks, may I ask why I should set mySql to false? What does this do? – SongBox Oct 23 '18 at 16:26
Thanks again, I have now tried your solution and it produces the same results that I have been seeing with every other variation of the query. It is counting 2 rows with the same cook ID as 2, and not 1. – SongBox Oct 23 '18 at 16:30
You can read about strict mode here https://mattstauffer.com/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2/ – alexandru.gaidei Oct 24 '18 at 07:21
Also try with distinct('cook_id') and groupBy('cook_id') – alexandru.gaidei Oct 24 '18 at 07:23
Nope - I appreciate your help here but it seems like this "trends" mfunction just completely ignores distinct. It's maddening. I've written the query every which way imaginable.... and there are lots of ways to write it. None work. – SongBox Oct 25 '18 at 20:41

Laravel Nova - Problems with distinct()

2 Answers2