jeudi 31 janvier 2019

Laravel Eloquent Nested Relationship group by

I have three models Spot, DealItem and Daypart each has its own relationships

class Spot extends Model
{
   protected $table = 'avt_spot';
   public $timestamps = false;

   public function dealItem()
   {
       return $this->belongsTo('App\SkyDealitem', 'deal_core_service_id', 'core_service_id');
   }
}

class SkyDealItem extends Model
{
protected $table = 'sky_deal_item';
public $timestamps = false;

public function daypart()
{
    return $this->belongsTo('App\Daypart','daypart_code_id','id');
}
}

class Daypart extends Model
{
protected $table = 'avt_US_daypart';
public $timestamps = false;

public function deals()
{
    return $this->hasMany('App\DealItem','daypart_code_id','id');
}
}

Every spot will have a column called pre_eval_tvr, it belongs to a DealItem and also to a campaign also

Every DealItem belongs to daypart.

FYI - A campaign will have multiple spots

Now the query which i am looking is daypart wise pre_eval_tvr using relationships.

Using just normal join query i can able to get it but with nested relationships i can't able to get it.

FYI - Join Query

select sum(avt_spot.pre_eval_tvr) as pre_eval_tvr, avt_US_daypart.* from   avt_spot inner join sky_deal_item on avt_spot.deal_core_service_id =  sky_deal_item.core_service_id inner join avt_US_daypart on  avt_US_daypart.id = sky_deal_item.daypart_code_id where avt_spot.allocated_to_campaign = 4442

group by avt_US_daypart.id;



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire