Any idea how to Convert this to Laravel Query Builder-CodePudding

DB::select("select encoded_datas.* from encoded_datas inner join
            (select data_user_firstname, data_user_lastname, data_user_barangay from encoded_datas t
            group by data_user_firstname, data_user_lastname, data_user_barangay
            having count(*)>1) t1
          on encoded_datas.data_user_firstname=t1.data_user_firstname and encoded_datas.data_user_lastname=t1.data_user_lastname and encoded_datas.data_user_barangay=t1.data_user_barangay");

It's a working piece of query code, but it is so slow to load compare to Eloquent/Proper Query Builder.

Can anyone here help me convert this to Laravel's Query Builder format..

CodePudding user response：

try this solution

DB::connection(your_connection)
->table('encoded_datas')
->select('encoded_datas.*')
->join(
    DB::raw("SELECT data_user_firstname, data_user_lastname, data_user_barangay FROM encoded_datas t GROUP BY data_user_firstname, data_user_lastname, data_user_barangay HAVING COUNT(*) > 1) t1"),
    function($join {
        $join->on('encoded_datas.data_user_firstname', '=', 't1.data_user_firstname')
             ->on('encoded_datas.data_user_lastname', '=', 't1.data_user_lastname')
             ->on('encoded_datas.data_user_barangay', '=', 't1.data_user_barangay');
    })
)

but I don't think it will speed up execution

CodePudding user response：

Sometimes sending multiple requests might be less expensive then sending one nested.

You can try getting the ids of the duplicate rows first

$ids = DB::table('datas')
->select('firstname','lastname', DB::raw('COUNT(*) as `count`'))
->groupBy('firstname', 'lastname')
->havingRaw('COUNT(*) > 1')
->pluck('id');

Then get datas

$datas = Datas::whereIn('id', $ids)->get();

In your example you are executing two queries and a join.

Here we are executing 2 queries only, 1 of which uses primary key index, which should be instant. Technically its should be faster, looks cleaner as well