CODEWITHSUNDEEP
phpmysqllaravel
DarkFenix
DarkFenix

Reputation: 746

How to validate the equality of two columns in a where of two tables in Laravel

I have two tables with the following structure

Table 1                                  Table2

table_id|table_name               table_id2| table_name2
  1     | sale                         2   | sale  
  2     | sale                         1   | note
  1     | note 
  3     | sale
  2     | note

I want to get all the records from the first table where table_id and table_name are not in table2 (id, table)

Expected result

 1 | sale   
 3 | sale   
 2 | note

Something like this occurred to me but I don't get the expected results

$table1->where(function ($query)  {
        $query->whereNotIn('table_id',$table2->pluck('table_id2'));
   });

Upvotes: 1

Views: 45

Answers (1)

Ersoy
Ersoy

Reputation: 9586

Assuming your tables are like these;

CREATE TABLE `first`
(
    `table_id`   int(11),
    `table_name` varchar(64)
);

CREATE TABLE `second`
(
    `table_id2`   int(11),
    `table_name2` varchar(64)
);

The query will be;

select first.*
from first
         left join second on table_id2 = table_id and table_name2 = table_name
where table_name2 is null;

Query builder will be;

return DB::table('first')
        ->leftJoin('second', function ($join) {
            $join->on('table_id2', '=', 'table_id');
            $join->on('table_name2', '=', 'table_name');
        })
        ->whereNull('table_name2')
        ->get([
            'first.*'
        ]);

Response json will be;

[
  {
    "table_id": 1,
    "table_name": "sale"
  },
  {
    "table_id": 3,
    "table_name": "sale"
  },
  {
    "table_id": 2,
    "table_name": "note"
  }
]

Upvotes: 1

Related Questions