Laravel - Eloquent - Eager Loading - Constraints / Selection / Grouping

Question

I find Eloquent's joining logic far more complicated than a typical prepared statement (read I hate it).

Can someone please help me translate the following query into a sensible eloquent query that is wholly eagerly-loaded?

SELECT  C.id,
        C.seo_alias
FROM store_variants AS A 
LEFT JOIN variants  AS B ON B.id = A.id_variant
LEFT JOIN products  AS C ON C.id = B.id_product
LEFT JOIN stores    AS D ON D.id = A.id_store
WHERE D.id = 123
AND A.isActive > 0
GROUP BY C.id;

one product - many variants

one variant - many stores

one store_variant - one store

The models are storeVariantModel, variantModel, productModel, storeModel. There are millions of rows.

The way I see it, this query contains filtered selection, joins, grouping and constraints, so it covers the majority of operations I encounter daily, so if anyone can solve this you have my sincere gratitude.

Thank you

EDIT: Using Laravel 5.2

EDIT: Solution #2:

Thanks to @Balraj for his help, this is basically the final solution I have been forced to use:

storeVariantModel::
    join(   'variants', 'variants.id_variant',  '=', 'store_variants.id')
    ->join( 'products', 'products.id',          '=', 'variants.id_product')
    ->join( 'stores',   'stores.id',            '=', 'store_variants.id_store')
    ->with([
        'variantModel.storeVariantModel.storeModel',
        'variantModel.productModel'
    ])
    ->select('variants.*')
    ->where('stores.id','=',123)
    ->where('store_variants.isActive','>',0)
    ->get();

Modifications: I am no longer using the 'GROUP BY'

Pros: I get models with relationships back, constraints on intermediate tables filter data as opposed to only eager-loading whatever data matches the constraints

Cons: Syntax is balls, I'm basically calling join twice, I am no longer doing a select for a limited set of columns

Answer 1

Try this one,

storeVariantModel::join('variants.id_variant','=','store_variants.id')
          ->join('products','products.id','=','variants.id_product')
          ->join('stores','stores.id','=','store_variants.id_store')
          ->select(['products.id','products.seo_alias'])
          ->where('stores.id','=',123)
          ->where('store_variants.isActive','>',0)
          ->groupby('products.id')
          ->get();

Update Answer

Eloquent without groupby:

In productModel:
public function variants(){
    return $this->hasMany(variantModel::class,'id_product','id');
}

In variantModel:
public function store_variants(){
    return $this->hasMany(storeVariantModel::class,'id_variant','id);
}

In storeVariantModel:
public function stores(){
    return $this->hasOne(storeModel::class,'id','id_store');
}



productModel::with(['variants.store_variants.stores' => function($q){
    $q->where('id','=',123);
}])->whereHas('variants.store_variants', function($q){
    $q->where('isActive','>',0);
})->get();