CODEWITHSUNDEEP
laraveleloquent
lyl0o0o
lyl0o0o

Reputation: 420

laravel search many to many Relashionship

I am testing eloquent for the first time and I want to see if it suit my application.

I have Product table:

id, name

and model:

class Produit extends Eloquent {

    public function eavs()
    {
        return $this->belongsToMany('Eav')
                ->withPivot('value_int', 'value_varchar', 'value_date');
    }
}

and eav table:

id, name, code, field_type

and pivot table:

product_id, eav_id, value_int, value_varchar, value_date

class Eav extends Eloquent {

public function produitTypes()
{
    return $this->belongsToMany(
            'ProduitType'
            ->withPivot('cs_attributs_produits_types_required');
}

All this is working. But I want to search in that relashionship: e.g: all product that have eav_id=3 and value_int=3

I have tested this:

$produits = Produit::with( array('eavs' => function($query)
    {
        $query->where('id', '3')->where('value_int', '3');
    }))->get();

But I get all the product, and eav data only for these who have id=3 and value_int=3.

I want to get only the product that match this search... Thank you

Upvotes: 1

Views: 6196

Answers (3)

Raghavendra N
Raghavendra N

Reputation: 3527

I know the question is very old. But added the answer that works in the latest versions of Laravel.

In Laravel 6.x+ versions you can use whereHas method.

So your query will look like this:

Produit::whereHas('eavs', function (Builder $query) {
    // Query the pivot table
    $query->where('eav_id', 3);
})->get()

Upvotes: 4

user1669496
user1669496

Reputation: 33058

My suggestion and something I like to follow is to start with what you know. In this case, we know the eav_id, so let's go from there.

$produits = Eav::find(3)->produits()->where('value_int', '3')->get();

Eager loading in this case isn't going to save you any performance because we are cutting down the 1+n query problem as described in the documentation because we are starting off with using find(). It's also going to be a lot easier to read and understand.

Using query builder for checking multiple eavs

$produits = DB::table('produits')
    ->join('eav_produit', 'eav_produit.produit_id', '=', 'produits.id')
    ->join('eavs', 'eavs.id', '=', 'eav_produit.eav_id')
    ->where(function($query)
    {
        $query->where('eav_produit.value_int','=','3');
        $query->where('eavs.id', '=', '3');
    })
    ->orWhere(function($query)
    {
        $query->where('eav_produit.value_int','=','1');
        $query->where('eavs.id', '=', '1');         
    })
    ->select('produits.*')
    ->get();

Making it work with what you already have...

$produits = Produit::with( array('eavs' => function($query)
{
    $query->where('id', '3')->where('value_int', '3');
    $query->orWhere('id', '1')->where('value_int', '1');
}))->get();

foreach($produits as $produit)
{
    if(!produit->eavs)
        continue;

    // Do stuff 
}

Upvotes: 0

Dave
Dave

Reputation: 3658

From http://four.laravel.com/docs/eloquent:

When accessing the records for a model, you may wish to limit your results based on the existence of a relationship. For example, you wish to pull all blog posts that have at least one comment. To do so, you may use the has method

$posts = Post::has('comments')->get();

Using the "has()" method should give you an array with only products that have EAV that match your criteria.

$produits = Produit::with( array('eavs' => function($query)
    {
        $query->where('id', '3')->where('value_int', '3');
    }))->has('eavs')->get();

Upvotes: 0

Related Questions