laravel eloquent eager loading nested condition

Question

I have 2 tables that is using eager loading and then using nested condition in that eager loading:

//migration for lead table
public function up()
{
    Schema::create('leads', function(Blueprint $table)
    {
        $table->engine = 'InnoDB';
        $table->increments('id');
        $table->string('first_name',255);
        $table->string('surname',255);
    });

    Schema::table('leads', function($table)
    {
        $table->foreign('create_by')->references('id')->on('employees')->onDelete('cascade');
    });
}

//lead for lead detail emails
public function up()
{
    Schema::create('lead_detail_emails', function(Blueprint $table)
    {
        $table->engine = 'InnoDB';
        $table->increments('id');
        $table->integer('lead_id')->unsigned();
        $table->string('email',255);
    });

    Schema::table('lead_detail_emails',function($table)
    {
        $table->foreign('lead_id')->references('id')->on('leads')->onDelete('cascade');
    });
}

//leads model
class LeadsModel extends Eloquent
{
    protected $table = 'leads';

    public function emails()
    {
        return $this->hasMany('LeadDetailEmailsModel','lead_id','id');
    }
}

//lead detail emails
class LeadDetailEmail extends Eloquent
{
    protected $table = 'lead_detail_email';

    public function lead()
    {
        return $this->belongsTo('LeadsModel');
    }
}

When I am trying to add nested condition to eager loading, lets say

$qry = LeadsModel::with(
                            array
                            (
                            'emails' => function($qr)
                            {
                                $qr->orWhere('email','like','%testname%');
                            }
                       ));

$res = $qry->get();

dd($res);

It returns all the records in the lead, I have tried joining emails and $qry by using

->join('lead_detail_emails','lead_detail_emails.lead_id','=','leads.id');

but it does not work as well. may I know what is the problem in the code?

update question

how can i get the leads by doing nested condition on the emails?

Yes, I know, but do you? :) This is what you need: http://laravel.com/docs/eloquent#querying-relations, `whereHas` in this very case. `joins` would do the job just the same, but then you need `select` and so forth. — Jarek Tkaczyk, Oct 27 '14 at 12:11
but I need to eager load the email records also, something like this then? $qry = LeadsModel::with( array ( 'emails' => function($qr) { $qr->whereHas('email','like','%testname%'); } )); $res = $qry->get(); — rfpdl, Oct 27 '14 at 12:12

Jarek Tkaczyk · Accepted Answer · 2014-10-27T13:06:19.953

12

$qry = LeadsModel::with(array('emails' => function ($q) use ($input) {
            $q->where('email','like',"%{$input}%");
        }))->whereHas('emails', function ($q) use ($input) {
            $q->where('email','like',"%{$input}%");
        });

$res = $qry->get();

edited Oct 27 '14 at 13:06

answered Oct 27 '14 at 12:20

Jarek Tkaczyk

78,987
25
159
157

jarek? how do you add the condition to the wherehas? I mean lets say you have $input variable before the code you have shown, how can I insert it into the whereHas('emails', function ($q) {$q->where('email','like','%'.$input.'%'); – rfpdl Oct 27 '14 at 12:55
You need to pass it to the closure with `use`, check the answer again. – Jarek Tkaczyk Oct 27 '14 at 13:06
may I ask if there will be several emails used in the condition? lets say look for input1 and input 2 email with OR condition. – rfpdl Oct 29 '14 at 07:59
I suppose another question of yours is what you mean, so you have the answer there. – Jarek Tkaczyk Oct 29 '14 at 11:15

laravel eloquent eager loading nested condition

1 Answers1