Join tables using query builder

I am having three tales in my database presented as follow.

providers

id
ref_id
name

offers

id
ref_id
provider_id
name

features

id
ref_id
offer_id
name

What I am trying to do is to retrieve a provider, for that provider retrieve all the offers and for each offer list the all the features. So I try using the following code but I am not getting the results expected:

DB::table('providers')
            ->where('providers.ref_id', '=', $providerID)
            ->leftJoin('offers', 'providers.ref_id', '=', 'offers.provider_id')
            ->leftJoin('features', 'offers.ref_id', '=', 'features.offer_id')
            ->select('providers.*', 'offers.*', 'features.*')
            ->get();

kindly help me solve the problem.

Sinnbeck

9 years ago

Best Answer

Level 102

Any reason you are not using eloquent? If you did you could just do the following.

$provider->where('ref_id', $providerID)->with('offers.features')->get();

This will give you a collection within a collection within a collection, which means it is easy to iterate over.

This of course means that you need to setup models for each and create relationships, but it is well worth it :)

1 like

princelionelnzi

9 years ago

Level 1

Thanks @Resin01 your answer was what I needed. But is there any way for me to get ell the features at once? by doing something like this $provider->offers->features ?

I have 3 models: Provider, Offer and Feature And the relationships has been implemented as follow

Provider

class Provider extends Model
{
    protected $table = 'providers';
    protected $primaryKey = "ref_id";
    public $incrementing = false;
    protected $fillable = ['ref_id', 'name', 'description', 'status'];

    public function offers() {
        return $this->hasMany('App\Models\Offer');
    }
}

Offer

class Offer extends Model
{
    protected $primaryKey = "ref_id";
    public $incrementing = false;
    protected $fillable = [
        'ref_id',
        'name',
        'description',
        'base_id',
        'sector_id',
        'provider_id',
        'category_id',
        'status'
    ];

    public function features() {
        return $this->hasMany('App\Models\Feature');
    }
}

Feature

class Feature extends Model
{
    protected $fillable = [
        'ref_id',
        "name",
        "dtype",
        "value",
        "description",
        "offer_id"
    ];

    public function offer() {
        return $this->belongsTo('App\Models\Offer');
    }
}

Please or to participate in this conversation.