Trouble with Eloquent and DISTINCT

if not mistaken, you have to put the distinct column name in the get()

Example

$query->get(['user_id']);

Edit: If the column name is not defined in the get() method, the query will be something like this

SELECT DISTINCT column1, column2,.... FROM `table`;

@frankielee Thanks for the reply. Gave it a shot, no such luck. I also am passing the column I am trying to be distinct, which is a table id into the distinct call. $query->distinct('table.id'). It's so odd that it counts and I get correct results with the query it spits out, but get() seems to throw a monkey wrench into the gears. I don't get why.

@fylzero add a DB::listen(...) & log sql + bindings before running $query->get().

Why are you not using groupBy?

I wouldn't worry about sharing your eloquent code & query, if anything it will give readers better idea of what you are dealing with.

@fylzero This the eloquent I have done testing, and it works on my site

$logs = Log::distinct()->get(['email']);

@spikerok I'm not super familiar with using DB::listen and the docs didn't really get me there. I did however install Telescope just to see the final query and it outputs correctly... if I copy/paste the query directly to TablePlus, I get 4 results. Makes zero sense to me why the actual $query->get() is outputting 5 results where one is a duplicate of the id I am asking to be distinct.

Here's the whole sql method...

    /**
     * Converting ticket SQL to Eloquent/QueryBuilder magic
     * Hopefully this works  =T
     *
     * @param  search:term  - Search term for deep searching issues and comments
     * @param  billable:bool  - Filters billable tickets
     * @param  assigned:id  - Filters tickets by ticket assignee id
     * @param  closed:bool  - Filters closed tickets
     * @param  all:bool  - Removes filters for opened, closed, on-hold, etc.
     * @param  department:id  - Filters tickets by location department id
     * @param  customer:id  - Filters tickets by location customer id
     * @param  location:id  - Filters tickets by location id
     * @param  opener:id  - Filters tickets by ticket created_by id
     * @param  closer:id  - Filters tickets by ticket closed_by id
     * @param  action:open|closed  - Filters tickets closed/not-closed betweem dates
     * @param  from:datestamp
     * @param  to:datestamp
     * @param  sort:assignee|status|date
     */
    public function sql(Request $request)
    {
        $tickets = Ticket::query();
        $tickets = $tickets->select(
            'tickets.id',
            'tickets.location_id',
            'tickets.status_id',
            'tickets.assignee_id',
            'tickets.issue',
            'tickets.weekend',
            'tickets.after_hours',
            'tickets.billable',
            'tickets.billed',
            'tickets.created_by',
            'tickets.created_at',
            'tickets.closed_by',
            'tickets.closed_at',
            'locations.customer_id',
            'locations.label',
            'ticket_statuses.id as status_id',
            'customers.name'
        );
        $tickets = $tickets->leftJoin('locations', 'locations.id', '=', 'tickets.location_id');
        $tickets = $tickets->leftJoin('customers', 'customers.id', '=', 'locations.customer_id');
        $tickets = $tickets->leftJoin('ticket_statuses', 'ticket_statuses.id', '=', 'tickets.status_id');

        // Filter: Search Term - Ticket Issue & All Comments
        if ($request->is('ticket*') && $request->filled('search')) {
            $tickets = $tickets->addSelect('comments.commentable_id', 'comments.commentable_type', 'comments.comment');
            $tickets = $tickets->leftJoin('comments', function ($join) {
                $join->on('tickets.id', '=', 'comments.commentable_id')
                    ->where('comments.commentable_type', '=', 'App\Models\Ticket');
            });

            $searchedWords = collect(explode(' ', $request->search));

            $searchedWords->each(function ($searchedWord) use ($tickets) {
                $tickets = $tickets->where(function ($query) use ($searchedWord) {
                    $query->where('tickets.issue', 'like', '%'.$searchedWord.'%');
                    $query->orWhere('comments.comment', 'like', '%'.$searchedWord.'%');
                });
            });
        }

        // Filter: Billable
        if ($request->is('ticket*') && $request->filled('billable')) {
            $tickets = $tickets->billable();
        }

        // Filter: Assigned to user
        if ($request->is('ticket*') && $request->filled('assigned')) {
            $tickets = $tickets->where('tickets.assignee_id', $request->assigned);
        }

        // Filter: Closed
        if ($request->is('ticket*') && $request->scope == 'closed') {
            $tickets = $tickets->closed();
        }

        // Filter: All or Not-Closed (Default)
        if ((!$request->scope == 'all') || ($request->scope == 'open')) {
            $tickets = $tickets->notClosed();
        }

        // Filter: Department
        if ($request->is('ticket*') && $request->filled('department')) {
            $tickets = $tickets->whereHas('location', function (Builder $query) use ($request) {
                $query->where('locations.department_id', $request->department);
            });
        }

        // Filter: Customer
        if ($request->is('ticket*') && $request->filled('customer')) {
            $tickets = $tickets->whereHas('location', function (Builder $query) use ($request) {
                $query->where('locations.customer_id', $request->customer);
            });
        }

        // Filter: Location
        if ($request->is('ticket*') && $request->filled('location')) {
            $tickets = $tickets->where('tickets.location_id', $request->location);
        }

        // Filter: Opened By & Closed By
        if ($request->is('ticket*') && $request->filled('opener')) {
            $tickets = $tickets->where('tickets.created_by', $request->opener);
        }
        if ($request->is('ticket*') && $request->filled('closer')) {
            $tickets = $tickets->where('tickets.closed_by', $request->closer);
        }

        /**
         * Filter: Date Range
         * @param  scope  - all, opened, closed
         * @param  from  - 2020-01-01
         * @param  to  - 2022-03-31
         */
        if ($request->is('ticket*') && ($request->filled('from') || $request->filled('to'))) {
            // Default to waaay in the past
            $from = now()->subYears(500)->toDateTimeString();
            if ($request->from) {
                $from = Carbon::parse($request->from, auth()->user()->settings->timezone)
                    ->startOfDay()->timezone('UTC')->toDateTimeString();
            }
            // Default to waaay in the future
            // Theory being, nothing can happen before "now" anyways
            $to = now()->addYears(500)->toDateTimeString();
            if ($request->to) {
                $to = Carbon::parse($request->to, auth()->user()->settings->timezone)
                    ->endOfDay()->timezone('UTC')->toDateTimeString();
            }
            if ((in_array($request->scope, ['all', 'open'])) && (!$request->has('closer'))) {
                $tickets = $tickets->whereBetween('tickets.created_at', [$from, $to]);
            }
            if (($request->scope == 'closed') || $request->has('closer')) {
                $tickets = $tickets->whereBetween('tickets.closed_at', [$from, $to]);
            }
        }

        // Ignore hidden locations
        $tickets = $tickets->whereHas('location', function (Builder $query) {
            $query->where('locations.hidden', false);
        });

        // Clear multiple results if searching because of comment left join
        if ($request->is('ticket*') && $request->filled('search')) {
            $tickets = $tickets->distinct('tickets.id');
        }

        /**
         * Sort
         * @param  sort  - assignee, date, status
         * @param  scope  - closed
         */
        if ($request->is('ticket*') && $request->scope == 'closed') {
            $tickets = $tickets->orderByDesc('tickets.closed_at');
        }
        $sort = $request->sort ?: auth()->user()->settings->tix_sort;
        if ($request->is('ticket*') && $sort == 'assignee') {
            $tickets = $tickets->orderByDesc('tickets.assignee_id')
                ->orderBy('ticket_statuses.priority')
                ->orderByDesc('tickets.created_at');
        }
        if ($request->is('ticket*') && $sort == 'status') {
            $tickets = $tickets->orderBy('ticket_statuses.priority')
                ->orderByDesc('tickets.created_at');
        }
        if ($request->is('ticket*') && $sort == 'date') {
            $tickets = $tickets->orderByDesc('tickets.created_at');
        }
        $tickets = $tickets->orderBy('customers.name');

        // dd($tickets->toSql());

        return $tickets;
    }

This is the method that calls it...

    /**
     * This is the server-driven-parital that actually shows the ticket list
     */
    public function display(Request $request)
    {
        $tickets = $this->sql($request);

        // Pagination Limit
        if (!$request->page || ($request->page == '1')) {
            $tickets = $tickets->offset(0)->limit(auth()->user()->settings->pagination);
        } else {
            $page = $request->page - 1;
            $page = $page * auth()->user()->settings->pagination;
            $tickets = $tickets->offset($page)->limit(auth()->user()->settings->pagination);
        }

        $tickets = $tickets->get();

        return view('pages.resources.ticket.ticket-display.main', compact('tickets'));
    }

Here is the SQL output...

select
  distinct `tickets`.`id`,
  `tickets`.`location_id`,
  `tickets`.`status_id`,
  `tickets`.`assignee_id`,
  `tickets`.`issue`,
  `tickets`.`weekend`,
  `tickets`.`after_hours`,
  `tickets`.`billable`,
  `tickets`.`billed`,
  `tickets`.`created_by`,
  `tickets`.`created_at`,
  `tickets`.`closed_by`,
  `tickets`.`closed_at`,
  `locations`.`customer_id`,
  `locations`.`label`,
  `ticket_statuses`.`id` as `status_id`,
  `customers`.`name`,
  `comments`.`commentable_id`,
  `comments`.`commentable_type`,
  `comments`.`comment`
from
  `tickets`
  left join `locations` on `locations`.`id` = `tickets`.`location_id`
  left join `customers` on `customers`.`id` = `locations`.`customer_id`
  left join `ticket_statuses` on `ticket_statuses`.`id` = `tickets`.`status_id`
  left join `comments` on `tickets`.`id` = `comments`.`commentable_id`
  and `comments`.`commentable_type` = 'App\Models\Ticket'
where
  (
    `tickets`.`issue` like '%eod%'
    or `comments`.`comment` like '%eod%'
  )
  and `status_id` != 5
  and `tickets`.`created_at` between '2020-09-01 05:00:00'
  and '2020-09-13 04:59:59'
  and exists (
    select
      *
    from
      `locations`
    where
      `tickets`.`location_id` = `locations`.`id`
      and `locations`.`hidden` = 0
  )
order by
  `tickets`.`created_at` desc,
  `customers`.`name` asc
limit
  20 offset 0

@frankielee That solution isn't solving the issue for me unfortunately.

@spikerok I was just mentioning that I'm not using groupBy as I've heard/read that has issues when using distinct. Just didn't want anyone spinning their wheels asking if that was part of the equation.

@fylzero, I just looked at your SQL output. I think the issue is due to the columns defined.

The SQL will only check if there any rows having the same value on those columns defined in select.

Example : If another row having the same email at the email column, ignored it.

SELECT distinct email from `users`;

If another row having the same id, email, and name, ignore it.

SELECT distinct id,email,name from `users`

@frankielee I understand but look closely at what I am doing. I am left joining a comments table and searching comments which will match and create additional entries which will cause duplicate tickets.id rows. Thus, I am selecting distinct on the id column to remove those duplicates made by the left-joined search-matched comments. Again, this SQL output works perfectly fine in TablePlus. ...I'm completely lost on why this doesn't work as expected when it hits the $tickets = $tickets->get(); line.

If I dd($tickets->count()) before that line, it results in 4.

If I dd($tickets->count()) after that line, it results in 5.

That's pretty messed up.

My best suggestion would be to use xdebug to dive into the eloquent code step by step to see how it handles the query output. It might take a little time, but it might be worth it

@sinnbeck I've never touched xdebug, I wouldn't know where to start with that.

@fylzero you get 4 when doing count is because count query doesn't fetch selected columns, and I suspect that this is why you are having an issue. I think you should try deleting everything from select() other than the distinct value tickets.id.

Your query, btw, is not too scary and can be refactored relatively quickly :) I tend to do a mapping array to define which method should be called for which scope or action and then just loop through the mapper. Similar to below:

const MAPPER = ['my_scope' => 'actionMyScopeMethod'];

//...

foreach(static::MAPPER as $condition => $method) {
//...
}

@fylzero i've just read your code and I think you can get away without using joins, just use eloquent's with() to get other relationships.Then, I suspect, you will have no need to use distinct in the first place since id is unique

@spikerok I like the refactor suggestions. For sure this can be simplified. Just doing things long-form for troubleshooting before refactoring but I will definitely employ that mapping suggestion in some fashion.

I'm not sure how to get the fuzzy search on issues and comments working if relationships are added in Eloquent but I'll try to think through that a bit.

I ultimately may just scrap some of this and move towards an Algolia implementation if this gives me too much more of a headache.

@spikerok Your suggestion lead to my answer! Thanks! I removed the select, joins and distinct and just walked everything down using with() relationships.

Still a WIP but here's the top section I changed for anyone interested...

        $tickets = Ticket::with(['location', 'customer', 'status']);

        // Filter: Search Term - Ticket Issue & All Comments
        if ($request->is('ticket*') && $request->filled('search')) {
            $searchedWords = collect(explode(' ', $request->search));

            $searchedWords->each(function ($searchedWord) use ($tickets) {
                $tickets = $tickets->where('issue', 'like', '%'.$searchedWord.'%');

                $tickets = $tickets->orWhereHas('comments', function (Builder $query) use ($searchedWord) {
                    $query->where('comment', 'like', '%'.$searchedWord.'%');
                });
            });
        }

I was for sure over-engineering this problem. This was really old code I was refactoring, I was just hesitant to fully bring it over to the Eloquent way as I'm still learning more of the intermediate features of it.