CODEWITHSUNDEEP
mysqllaravel-5query-builderlaravel-query-builder
Soul Coder
Soul Coder

Reputation: 804

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

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.

Upvotes: 0

Views: 165

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31832

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.

Upvotes: 2

giorgiosaud
giorgiosaud

Reputation: 536

just remove the groupBy that is mergin the query

Upvotes: 0

Related Questions