Select all records that don't exist in another table Laravel
<p>So, i'm trying to select a number of records if they don't already exist in another table.
I have three models, <strong>Lead</strong>, <strong>SalesLog</strong> and <strong>Cron</strong> and the idea is to get all leads that are not already in the SalesLog and where the <code>created_at</code> field is greater than or equal to than the <strong>Crons</strong> <code>duplicated_days</code> field.</p>

<p>So far have something that looks like this, but it's not working at all</p>

<pre><code>    $leads = Lead::whereIn('status', [$minimumRequiredStatus])-&gt;whereNotIn('id', function($query) use ($cron) {

        $query-&gt;table('sales_log')-&gt;select(['id'])-&gt;where('campaign', $cron-&gt;campaign);

    })-&gt;get();
</code></pre>

<p>I can use filters for this but i might eventually be working with thousands and possibly millions of records on both the <code>leads</code> and <code>sales_log</code> tables.. This makes me apprehensive about doing it offline.</p>

<p>How could i run a query that would simply get all <code>leads</code> that are of the required status, not already inside of the <code>sales_log</code> and whose <code>created_at</code> field is equal to or greater than, say 30 days for instance.</p>

<p>This version with the Filters actually works the way it's expected to, but i fear the performance impact of such an operation with several records on both tables.</p>

<pre><code>    $leads = $cron-&gt;campaign-&gt;validLeads-&gt;filter(function($lead) use ($cron) {

        if($cron-&gt;salesLogs-&gt;contains(SalesLog::LEAD_ID, $lead-&gt;{Lead::ID}))
            return false;

        return true;

    });
</code></pre>

<p>As far as Models are concerned i have 4 models that should be used here, a <strong>Lead</strong> model that refers to a <code>leads</code> table that contains leads for <em>every campaign</em> a <strong>Campaign</strong> Model, a <strong>Cron</strong> model that contains all Crons that <strong>Belong to</strong> a Campaign.
Finally there's a <strong>SalesLog</strong> model, all leads, once sent via CURL get added to this Table, this is how i can tell which leads were sent and to which <strong>Campaign</strong></p>

<p>So, to recap</p>

<p><strong>Campaign</strong> is the top-level Model it has access to the <strong>Lead</strong> model and the <strong>Cron</strong> model via a <em>hasMany</em> relationship.</p>

<p><strong>Lead</strong> is the model for the table where <strong>ALL</strong> leads are stored. Regardless of Campaigns. It <em>belongs to</em> a <strong>Campaign</strong> and <em>has many</em> <strong>SalesLog</strong></p>

<p>The <strong>Cron</strong> also <em>belongs to</em> a <strong>Campaign</strong> and <em>has many</em> <strong>Sales Log</strong> through another Model.</p>

<p>Finally, the <strong>SalesLog</strong> <em>belongs to</em>  a <strong>Campaign</strong> and also <em>belongs to</em> a <strong>Lead</strong></p>

<p>So the idea is through the <strong>Cron</strong> i can access all <strong>SalesLogs</strong> for that Cron, and i need to get all <strong>Leads</strong> from a <strong>Campaign</strong> where the Lead's <strong>ID</strong> is not in the <strong>SalesLogs</strong> for that <strong>Campaign</strong></p>