CODEWITHSUNDEEP
mysqllaravellaravel-5eloquent
João Serra
João Serra

Reputation: 253

Select all records that don't exist in another table Laravel

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

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

    $leads = Lead::whereIn('status', [$minimumRequiredStatus])->whereNotIn('id', function($query) use ($cron) {

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

    })->get();

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

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

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.

    $leads = $cron->campaign->validLeads->filter(function($lead) use ($cron) {

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

        return true;

    });

As far as Models are concerned i have 4 models that should be used here, a Lead model that refers to a leads table that contains leads for every campaign a Campaign Model, a Cron model that contains all Crons that Belong to a Campaign. Finally there's a SalesLog 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 Campaign

So, to recap

Campaign is the top-level Model it has access to the Lead model and the Cron model via a hasMany relationship.

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

The Cron also belongs to a Campaign and has many Sales Log through another Model.

Finally, the SalesLog belongs to a Campaign and also belongs to a Lead

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

Upvotes: 5

Views: 3970

Answers (2)

Shahrukh Anwar
Shahrukh Anwar

Reputation: 2632

You can use pluck for getting the id's which will not present in another table, follow below instructions,

$unapproved = $this->approval->where('user_id', $user_id)->pluck('approved_by')->all(); $unapproved = $this->company->whereNotIn('id', $unapproved)->with('countries')->get();

Upvotes: 0

Tomas Buteler
Tomas Buteler

Reputation: 4117

Wouldn't a left join suffice?

$leads = Lead::whereIn('status', [$minimumRequiredStatus])
             ->leftJoin('sales_log', function ($join) use ($cron) {
                 $join->on('leads.id', '=', 'sales_log.lead_id')
                      ->where('sales_log.campaign_id', '=', $cron->campaign->campaign_id);
             })
             ->whereNull('sales_log.id')
             ->where('created_at', '>=', Carbon::now()->subDays(30))
             ->get();

Upvotes: 4

Related Questions