Question 193992 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How can I join Two Tables with and intermediate Table and gather data?

I am working on a project and I needed to retrieve data from an Table A to Table C with a Table B as an intermediate using the HasManyThrough from Laravel Doc Has Many Through.

I have three Tables Categorie SousCategorie and Produit, This is the realtionship:
Categorie –> Hasmany Sous_Categorie –>Hasmany Produits.

And this is my tables 
Categorie:
   - id
   - name
Sous_categorie:
   - id
   - categorie_id
   - name
Produits:
   - id
   - sous_categorie_id
   - name

And this are my Models

Categorie.php

class Categorie extends Model
{
public function souscategories()
    {
        return $this->hasMany(Sous_categorie::class, 'sous_categorie');
    }

    public function produits()
    {
        return $this->hasManyThrough(Produit::class, Sous_categorie::class);
    }
}

Sous_Categorie.php

class Sous_categorie extends Model
{ 
public function categories(){
        return $this->belongsTo(Categorie::class,'categorie_id','id');
    }

    public function produits(){
        return $this->hasMany(Produit::class,'produit');
    }
}

Produit.php

class Produit extends Model
{
public function souscategories()
    {
        return $this->belongsTo(Sous_categorie::class, 'sous_categorie_id', 'id');
    }
}

I also added the:

protected $fillable = [
        'filenames',
        'title',
        'nom',
        'sous_categorie_id',
        'categorie_id',
        'id',
        'id'

    ];

What Am trying to do is to get all the product from a specified Category based on and ID for example:

I have categorie Called PC PARTS and a Sous_categorie belongs to PC PARTS has many Produits that I want to retrive.

I hope My Problem is Clear, Thanks in Advance.

I tried to get all produits with a join between souscategories.categories relationship with categorie_id like this

$s_produits = Produit::with('souscategories.categories')->where('categorie_id','like','%'.$id.'%')->get();

I get This Error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categorie_id' in 'where clause' (SQL: select * from `produits` where `categorie_id` like %1%)

If I try this line of code:

$s_produits = Produit::with('souscategories.categories')->get();

it retrieve all product available

enter image description here
which I don’t want I want all products from a certain category

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I figure it out how to join table A with table B with a table C and an intermediate table. so I have table categories hasMany sous_categories hasMany produits i was trying to retrieve all products based on a categories name with this code:

    public function show(Sous_categorie $sous_categorie, $id)
{
    //
    $s_produits = DB::table('produits')
    ->select('produits.*', 'sous_categories.categorie_id as laravel_through_key')
    ->join('sous_categories','sous_categories.id','=','produits.sous_categorie_id')
    ->whereIn('sous_categories.categorie_id',[$id])
    ->get();
}

    it's works fine with a 156ms query duration if i can make it any simpler please tell me.


  2. 0

    I see that you wnat to filter the Produit model based on categorie_id in souscategories table ? You can add a constraints inside with() :

    use IlluminateContractsDatabaseEloquentBuilder;

Produit::with(['souscategories' => function (Builder $query) use ($id) {
    $query->where("categorie_id","LIKE", %{$id}%);
}])->get();

    Constraining Eager Loads here the reference.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search