How to write a where not in query in laravel?

Question

I want to write a Where not in query in laravel. I wrote in sql it's working fine please help me convert the query to laravel.

This is the query...

SELECT * 
FROM `apiaccessauth` 
WHERE `site_name` NOT IN (
                SELECT `site_name` 
                FROM `API_site_access_log` 
                WHERE `created_at` LIKE '%2021-10-15%'
                );

Answer 1

If you want to use Eloquent, it'll be like below

Eloquent model

<?php

namespace App\Modules\Vehicle\Models;

use Illuminate\Database\Eloquent\Model;

class ApiAccessAuth extends Model
{
    protected $table = 'apiaccessauth';
}

Your query

ApiAccessAuth::whereNotIn('site_name', function ($query) {
    $query->from('API_site_access_log')
        ->select('site_name')
        ->whereDate('created_at', '2021-10-15');
})->get();

If you want to use query builder, it'll be like below

use Illuminate\Support\Facades\DB;

DB::table('apiaccessauth')
    ->whereNotIn('site_name', function ($query) {
        $query->from('API_site_access_log')
            ->select('site_name')
            ->whereDate('created_at', '2021-10-15');
    })->get();

Answer 2

Perhaps you would like to refactor your query to

    SELECT a.* 
    FROM `apiaccessauth` a, 
    `API_site_access_log` b 
    WHERE
    a.`site_name` = b.`site_name` AND
    b.`site_name` NOT LIKE '%2021-10-15%'

so with query builder

    $result = DB::table('apiaccessauth')
    ->join('API_site_access_log', 'API_site_access_log.site_name', '=', 'apiaccessauth.site_name')
    ->where('API_site_access_log.site_name', 'not like', "%2021-10-15%")
    ->get();

Pehaps you need a left or outer Join, depending what you want