jeudi 7 mai 2020

How to get only the second duplicated record in laravel 5.5?

Let's say i have a user table like this :

+----+-----------+----------------------+------+
| ID | Name      | Email                | Age  |
+----+-----------+----------------------+------+
| 1  | John      | john.doe1@mail.com   | 24   |
| 2  | Josh      | josh99@mail.com      | 29   |
| 3  | Joseph    | joseph410@mail.com   | 21   |
| 4  | George    | gge.48@mail.com      | 28   |
| 5  | Joseph    | jh.city89@mail.com   | 24   |
| 6  | Kim       | kimsd@mail.com       | 32   |
| 7  | Bob       | bob.s@mail.com       | 38   |
| 8  | Joseph    | psa.jos@mail.com     | 34   |
| 9  | Joseph    | joseph.la@mail.com   | 28   |
| 10 | Jonathan  | jonhan@mail.com      | 22   |
+----+-----------+---------+------------+------+

In the actual, it have more data and some of them is duplicated with more than two record, but the point is i want to get only the first and the second row in duplicated rows that have name "Joseph", but how to do it ? My code this far...

User::withTrashed()->groupBy('name')->havingRaw('count("name") >= 1')->get();

With that code the result will retrieve :

+----+-----------+----------------------+------+
| ID | Name      | Email                | Age  |
+----+-----------+----------------------+------+
| 1  | John      | john.doe1@mail.com   | 24   |
| 2  | Josh      | josh99@mail.com      | 29   |
| 3  | Joseph    | joseph410@mail.com   | 21   |
| 4  | George    | gge.48@mail.com      | 28   |
| 6  | Kim       | kimsd@mail.com       | 32   |
| 7  | Bob       | bob.s@mail.com       | 38   |
| 10 | Jonathan  | jonhan@mail.com      | 22   |
+----+-----------+---------+------------+------+

And i use this code to try to get the second duplicate row :

User::withTrashed()->groupBy('name')->havingRaw('count("name") >= 2')->get();

The result still same as the mentioned above :

+----+-----------+----------------------+------+
| ID | Name      | Email                | Age  |
+----+-----------+----------------------+------+
| 1  | John      | john.doe1@mail.com   | 24   |
| 2  | Josh      | josh99@mail.com      | 29   |
| 3  | Joseph    | joseph410@mail.com   | 21   |
| 4  | George    | gge.48@mail.com      | 28   |
| 6  | Kim       | kimsd@mail.com       | 32   |
| 7  | Bob       | bob.s@mail.com       | 38   |
| 10 | Jonathan  | jonhan@mail.com      | 22   |
+----+-----------+---------+------------+------+

I want the result is to get record that have the id "5" with name "Joseph" like this :

    +----+-----------+----------------------+------+
    | ID | Name      | Email                | Age  |
    +----+-----------+----------------------+------+
    | 1  | John      | john.doe1@mail.com   | 24   |
    | 2  | Josh      | josh99@mail.com      | 29   |
    | 4  | George    | gge.48@mail.com      | 28   |
    | 5  | Joseph    | jh.city89@mail.com   | 24   |
    | 6  | Kim       | kimsd@mail.com       | 32   |
    | 7  | Bob       | bob.s@mail.com       | 38   |
    | 10 | Jonathan  | jonhan@mail.com      | 22   |
    +----+-----------+---------+------------+------+

But it seems only the first duplicate row is retrieved and i can't get the second duplicated row, can anybody give me suggestion ?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire