Laravel Eloquent Check if Value [Table A] Does not Contain in [Table B]

I would like to retrieve value from Table A where it does not exists in Table B. Using whereIn could be terrible for performance in BigData. I am trying to achieve it with Eloquent

Table A: PropertyVault |
Table B: PropertyNetworkPost

So, the mechanism. I need to retrieve a value from PropertyVault where it does not exist in PropertyNetworkPost. Then should be matched based on network_id

Here the relationship

PropertyVault

public function networkPost(): HasMany{
    return $this->hasMany(PropertyNetworkPost::class);
}

PropertyNetworkPost

public function vault(): BelongsTo{
    return $this->belongsTo(PropertyVault::class);
}

Here is the code I was trying for

    $vault = PropertyVault::whereDoesntHave('networkPost', function ($query) use($network) {
        $query->where('network_id', $network->id);
    })->limit(1)->get();

I was trying to got unposted [listing] then matching the network_id

Here is the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column
‘property_vaults.network_post_id’ in ‘where clause’ SELECT * FROM
property_vaults WHERE NOT EXISTS (
SELECT
*
FROM
property_network_posts
WHERE
property_vaults.network_post_id = property_network_posts.id
AND network_id = 1 ) limit 1

Anyway, I got it from Laravel documentation
https://laravel.com/docs/10.x/eloquent-relationships#querying-relationship-absence

Table Schemas:

PropertyVault

There are total posted, so whenever it posts to other sites [increment +1]

    // Schema::connection('agc')->dropIfExists('property_vault');
    Schema::create('property_vaults',function (Blueprint $table){
        $table->id();
        $table->char('country',2);
        $table->unsignedBigInteger('source_id');
        $table->foreign('source_id')->references('id')->on('property_sources')->cascadeOnDelete()->cascadeOnUpdate();
        $table->string('ad_url')->unique();
        $table->string('profile_url')->nullable();
        $table->unsignedInteger('total_posted')->default(0);
        $table->json('ad_data')->nullable();
        $table->json('profile_data')->nullable();
        $table->json('ad_images')->nullable();
        $table->json('profile_images')->nullable();
        $table->boolean('rendered_ad')->default(0);
        $table->boolean('rendered_profile')->default(0);
        $table->enum('status',PropertyVaultStatusEnums::values());
        $table->timestamps();
    });

PropertyNetworkPost

Schema::create('property_network_posts',function(Blueprint $table){
    $table->id();
    $table->unsignedBigInteger('network_id');
    $table->foreign('network_id')->references('id')->on('property_networks')->cascadeOnDelete()->cascadeOnUpdate();
    $table->unsignedBigInteger('vault_id');
    $table->foreign('vault_id')->references('id')->on('property_vaults')->onUpdate('cascade')->onDelete('cascade');
    $table->string('posted_url');
    $table->timestamps();
});

>Solution :

Based on your schema could you try with the following:

public function networkPosts(): HasMany{
    return $this->hasMany(PropertyNetworkPost::class, 'vault_id', 'id');
}

Laravel tries to guess your foreign keys based on your Model names, however if you name your foreign keys using a different convention you need to tell Laravel what they are named.

More about it here: https://laravel.com/docs/10.x/eloquent-relationships#one-to-many.

I would also recommend you try to debug the one to many relationship first to make sure it’s working by simply doing:

dd(PropertyVault::first()->networkPosts)