Laravel Eloquent : one to many relationship with distinct

Question

Need to get distinct row.

Is there any way in laravel eloquent to avoid the duplication of data. Need to get unique users after joining with image table.

Now, I'm getting the same users, need to avoid the repetition of the same user.

Model Image

public function user()
{
    return $this->belongsTo('App\User');
}

User Table

id | name
---------
| 1| Joe
| 2| Ben
| 3| Don

Image Table

id  |   name | user_id
---------------------
| 1 |  1.png |    3
| 2 |  2.png |    1
| 3 |  3.png |    1
| 4 |  4.png |    2

join using eloquent :

Image::with('user')->orderBy('id', 'desc')->paginate(10);

Required Output

"data": [
    {
        "id": 4,
        "name": "4.png",
        "user_id": 2,
        "user": {
            "id": 2,
            "name": "Ben",
        }
    },
    {
        "id": 3,
        "name": "3.png",
        "user_id": 1,
        "user": {
            "id": 1,
            "name": "Joe",
        }
    },
    {
        "id": 1,
        "name": "1.png",
        "user_id": 3,
        "user": {
            "id": 3,
            "name": "Don",
        }
    },
],

@Aless55 I tried different methods using distinct, doesn't work. — Shelly, Aug 27 '20 at 13:04

dipenparmar12 · Answer 1 · 2020-08-27T14:52:40.700

0

To get unique results from Eloquent call unique() method at end of the method chain.

Image::with('user')
      ->orderBy('id', 'desc') 
      ->paginate(10)
      ->unique() // Like this

If above method is not working try below.

Image::with('user')
      ->orderBy('id', 'desc') 
      ->paginate(10)
      ->unique()
      ->values()

edited Aug 27 '20 at 14:52

answered Aug 27 '20 at 13:09

dipenparmar12

3,042
1
29
39

I have added one more thing in answer, Now try again. – dipenparmar12 Aug 27 '20 at 14:53

score 0 · Answer 2 · answered Aug 27 '20 at 14:09

0

It sounds like you want all the users and only one of the images per user. You could do something like the following to add an image relationship to user model:

    public function image()
    {
        return $this->hasOne('App\Image')->orderBy('id', 'desc');
    }

Then you would access this data like User::has('image')->with('image');

answered Aug 27 '20 at 14:09

OneSimpleGeek

138
11

I need to make a distinct on "user_id", is there any way in laravel eloquent to achieve it? – Shelly Aug 28 '20 at 05:03
It almost work, but I have to get result based on the latest image. By doing it in your way the result will be based on the users. Actually in the desired output it must be in descending, I have corrected it – Shelly Aug 28 '20 at 05:15
This should get you the latest image per user, I assumed you only wanted one image per user. Is it working for you now or do you still need help? Feel free to add your current code if you need help still. – OneSimpleGeek Aug 28 '20 at 12:30

score 0 · Answer 3 · answered Aug 28 '20 at 20:23

To get latest image for each user you can use a self join like

Schema

CREATE TABLE users
    (`id` int, `name` varchar(3))
;
    
INSERT INTO users
    (`id`, `name`)
VALUES
    (1, 'Joe'),
    (2, 'Ben'),
    (3, 'Don')
;

CREATE TABLE images
    (`id` int, `name` varchar(5), `user_id` int)
;
    
INSERT INTO images
    (`id`, `name`, `user_id`)
VALUES
    (1, '1.png', 3),
    (2, '2.png', 1),
    (3, '3.png', 1),
    (4, '4.png', 2)
;

Query

select a.*, u.*
from images a
left join images b on a.user_id = b.user_id
  and a.id < b.id
join users u on u.id = a.user_id
where b.user_id is null
order by a.id desc

Query Builder

DB::table('images as a')
  ->select('a.*, u.*')
  ->leftJoin('images as b', function ($join) {
        $join->on('a.user_id', '=', 'b.user_id')
             ->whereRaw(DB::raw('a.id < b.id'));
   })
  ->join('users as u', 'u.id', '=', 'a.user_id' )
  ->whereNull('b.user_id')
  ->orderBy('a.id', 'desc')
  ->get();

DEMO

Laravel Eloquent : one to many relationship with distinct

3 Answers3