CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
daugaard47
daugaard47

Reputation: 1868

Compare same column in different tables Laravel

I have 2 tables where I'm trying to match records and output the records that do not match.

The main goal is to get the missing rows from Table B and Add them to Table A.

I would also like to get the following information:

  1. Number of matching upc from table A & Table B
  2. Number of Not matching upc from table A & Table B
  3. Output the unmatched name & upc

How would I achieve this?

For #1 I tried:

use \App\Models\A;

$matchingUpc = A::join('b', 'b.upc', '=', 'a.upc')->count();

dd($matchingUpc); // = 990 ???

This outputs the count of 990 which does not make sense because Table A only has 986 rows.

I know I'm probably making this much harder than it is. Any help or a point in the right direction would be appreciated.

Upvotes: 1

Views: 1448

Answers (1)

OMR
OMR

Reputation: 12188

you can use Where Exists Clauses

The whereExists method allows you to write "where exists" SQL clauses. The whereExists method accepts a closure which will receive a query builder instance, allowing you to define the query that should be placed inside of the "exists" clause:

 $upcMatching  = DB::table('production')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('imported')
                    ->whereColumn('production.upc', 'imported.upc');
            })->count();
      

        $upcNotMatching = DB::table('production')
            ->whereNotExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('imported')
                    ->whereColumn('production.upc', 'imported.upc');
            })->get();


 $numberOfUnMatchedCount=DB::table('production')->count('upc')-$upcMatching;

//or simply:

$numberOfUnMatchedCount= $upcNotMatching->count();

Upvotes: 2

Related Questions