Raw DB query but with Eloquent

At the moment I have a model called contacts and I'm displaying a list of contacts like this:

$contacts = Contacts::orderBy('first_name')->orderBy('last_name')->paginate(200);

return view('contacts/index', ['contacts' => $contacts]);

Now I want to list all contacts which have duplicates, but utilising the same view. So ideally I want to do it via Eloquent rather than Query Builder.

My SQL is:

$sql = 'select * from contacts where id in (select id from conatcts group by concat(first_name,last_name) having count(concat(first_name,last_name)) > 1)'

Is it possible to somehow pass this query to Eloquent and get back an eloquent collection? Note that I also need to use the paginate() method.

I know about the hydrate() method but I am not sure it's possible for me to utilise this inconjunction with the paginate() method.

Thanks!

Tray2

4 years ago

Level 73

You can use

DB::select(<your query>)

1 like

Garet

4 years ago

Level 3

I tried that but it didn't work and threw a query exception, because it seemed like the select() method is used for the select portion of the query only

jlrdw

4 years ago

Level 75

With the group by, you can use a length aware paginator.

https://laracasts.com/discuss/channels/guides/manual-pagination-episode-3

Edit: A more advanced example using db facade:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

You can always use getPdo().

Also an eloquent query is in this form just example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();  // or paginate

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

https://laravel.com/docs/8.x/queries

1 like

Garet

4 years ago

Best Answer

Level 3

Okay, I figured it out:

Contacts::whereRaw(<sql>);

In my case:

Contacts::whereRaw('CONCAT(first_name, last_name) IN(SELECT CONCAT(first_name, last_name) FROM contacts GROUP BY CONCAT(first_name, last_name) HAVING COUNT(CONCAT(first_name, last_name)) > 1)')
    ->orderBy('first_name')
    ->orderBy('last_name')
    ->paginate(20);

Please or to participate in this conversation.