How to convert mysql query to query builder for laravel 5?

Question

I am trying to get all duplicates rows from Customers Table I want to convert the following mysql query into query builder to use in laravel 5. And I search in documentation but not luck.

SELECT 
   CustomerName, 
   ContactName, 
   Customers.City 
FROM 
   Customers
INNER JOIN(
            SELECT 
               City 
            FROM Customers 
            GROUP BY City 
            HAVING COUNT(CustomerName) >1 ) temp 
ON Customers.City = temp.City;

and here I search in google and tried like this , but this query builder is just showing only one row if duplicate.I just want every rows which are duplicates.

> $duplicates = DB::table('customers')
->select('CustomerName','City', DB::raw('COUNT(*) as `count`'))
->groupBy('CustomerName', 'City')
->having('count', '>', 1)
->get();

Any help you have would be greatly appreciated.Thanks.

Paul Spiegel · Accepted Answer · 2017-07-23 16:49:00Z

2

Laravel supports subqueries in whereIn.

$subQuery = DB::table('Customers')
    ->select('City')
    ->groupBy('City')
    ->havingRaw('count(*) > 1');

$duplicates = DB::table('Customers')
    ->select('CustomerName','City')
    ->whereIn('City', $subquery)
    ->get();

This will not create the same query. But will return the same result.

answered Jul 23, 2017 at 16:49

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

giorgiosaud · Accepted Answer · 2017-07-23 16:45:53Z

0

just remove the groupBy that is mergin the query

answered Jul 23, 2017 at 16:45

giorgiosaud

5363 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to convert mysql query to query builder for laravel 5?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related