Duplicate Records in result

Replace the ->get()->toArray() with ->toSql() and show the generated sql statement.

The problem is that an user has many subjects.

perhaps use RAW query will be easier...

what's the point of the join? you don't use any of the joined data

A list of students arranged by the updated date of the subject? Does not seem to make sense?

@snapey I wanna get the list of students orderBy Subjects.updated_at timestamp. that's why i'm doing join here. but when I use the join it is giving. the duplicates in the result.

@tray2 here is the general statement

select "users"."id" as "user_id", "first_name", "last_name", "email", "active" from "users" inner join "subjects" on "subjects"."user_id" = "users"."id" where "active" = ? order by "subjects"."updated_at" asc

Have you checked your db? Perhaps you really got duplicates stored in.

@ep!sode yeah it is giving more duplicates than inner join

If you are still lost, you can try this:

$students = User::select([
            'users.id AS user_id',
            'first_name',
            'last_name',
            'email',
            'active', 
           'subjects.*'
        ])
        ->join('subjects', 'subjects.user_id', '=', 'users.id')
        ->where('active', 'yes')
        ->orderByRaw('subjects.updated_at ASC')
        ->get()
        ->toArray();

@snapey @tray2 @ep!sode I did some refactoring now it is not giving any duplicate values. but it is not doing sortBy results. I mean ->sortByDesc('subjects.updated_at') is not working at all

$students = User::with(['subjects' => function ($q){
            $q->orderBy('updated_at', 'DESC');
        }])
        ->select([
            'id',
            'first_name',
            'last_name',
            'email',
            'active'
        ])
        ->where('active', 'yes')
        ->withCount('subjects')
        ->get()
        ->sortByDesc('subjects.updated_at');

I wanna get the list of students orderBy Subjects.updated_at timestamp. that's why i'm doing join here. but when I use the join it is giving. the duplicates in the result.

but how can you do that when you have multiple subjects per student. The order is non-deterministic because different subjects will have different dates for the same student.

@snapey Thanks for your response. ah, that makes sense. what is the best practice in this case?

I don't understand why would you want to sort the users in this way? Perhaps if you can explain the requirements then we can find the right solution.

@snapey client wants to see a report that who did latest changes to subjects object by each student. so for this, I was using the updated_at column.

I don't know your use-case thus I can't give you a reasonable practice in doing the proper way.

So, you said, you want a list of subjects. Change your perspective, get the subjects with students. Rather than the other way around.

In this article look for Ordering by has-many relationships

https://reinink.ca/articles/ordering-database-queries-by-relationship-columns-in-laravel

Hi, what about the distinct() method? Im also trying to avoid duplicate records. This is what im trying to do via routes/web.php

'comments' => App\Models\CustomComment::distinct()->get()

Yet that does not work, I was actually trying to find out how to use that method and I saw this post. Cheers.

https://laravel.com/docs/8.x/queries#select-statements

EDIT:

Simple SQL returns non-duplicated records:

select distinct commentable_id
from comments

Hope this helps. I had a @foreach where I was retrieving comments, by using: unique('field.name')

@foreach ($comments->unique('commentable_id') as $comment)
                        <a href="/posts/{{ $comment->commentable->slug }}"> 
                            <p class="date"> 
                                {{ $comment->commentable->title }}
                            </p>
                        </a>
                    @endforeach

https://stackoverflow.com/questions/47189705/unique-distinct-in-laravel-foreach

We wanted to return a single record for each user, but instead we get multiple records for some users in the results. Looking across the result set, we can see that the "duplicate" results are not actually duplicates - the same user is being returned with multiple categories, making each row unique...

coomeet azar shagle