Searching multiple tables gives incorrect results

[sunel]

Can you give some more details.

Check this file for possible ways to query

https://github.com/sunel/eav/blob/master/tests/Feature/WhereQueryTest.php

[joaopmmartins]

I am searching multiple tables in the database using the models relationship. For example, I need to get all the products belonging to the category with slug='garden-tools', which must be tagged with ('table' OR 'carbon-steel') OR have the attributes ('weight'='13 grams' OR length='10 cm').

The finals results should contain 7 products ids: 23903, 24061, 27891, 36514, 41948, 41949, 44861

But when I run the following query but it only returns the product id 27891.

Could this be a bug in whereAttribute, orWhereAttribute functions?

This is my code

$tags = array('table', 'carbon-steel');

$products = $category->products()->whereHas('tags', function($query) use($tags) {
                        $query->whereIn('slug', $tags);
                    })->orWhereAttribute(function ( $q) {
                        $q->whereAttribute('weight', '=', '13 grams');
                        $q->orWhereAttribute('length', '=', '10 cm');
                    })->select(['*'])->get();

The sql output is:

SELECT [joao_db].[dbo].[product].*
FROM   [joao_db].[dbo].[product]
       INNER JOIN [joao_db].[dbo].[category_product]
               ON [product].[id] = [category_product].[product_id]
       INNER JOIN [joao_db].[dbo].[product_string] AS [length_attr]
               ON [product].[id] = [length_attr].[entity_id]
                  AND [length_attr].[attribute_id] = 72
       INNER JOIN [joao_db].[dbo].[product_string] AS [weight_attr]
               ON [product].[id] = [weight_attr].[entity_id]
                  AND [weight_attr].[attribute_id] = 92
WHERE  [category_product].[category_id] = 20
       AND EXISTS (SELECT *
                   FROM   [joao_db].[dbo].[tags]
                          INNER JOIN [joao_db].[dbo].[taggables]
                                  ON [tags].[id] = [taggables].[tag_id]
                   WHERE  [product].[id] = [taggables].[taggable_id]
                          AND [taggables].[taggable_type] = 'App\Product'
                          AND [slug] IN ( 'carbon-steel', 'table' ))
        OR ( [weight_attr].[value] = '13 grams' OR [length_attr].[value] = '10 cm' ) 

I replaced the inner joins and it works. See the query below:

SELECT [joao_db].[dbo].[product].*
FROM   [joao_db].[dbo].[product]
       LEFT JOIN [joao_db].[dbo].[category_product]
               ON [product].[id] = [category_product].[product_id]
       LEFT JOIN [joao_db].[dbo].[product_string] AS [length_attr]
               ON [product].[id] = [length_attr].[entity_id]
                  AND [length_attr].[attribute_id] = 72
       LEFT JOIN [joao_db].[dbo].[product_string] AS [weight_attr]
               ON [product].[id] = [weight_attr].[entity_id]
                  AND [weight_attr].[attribute_id] = 92
WHERE  [category_product].[category_id] = 20
       AND EXISTS (SELECT *
                   FROM   [joao_db].[dbo].[tags]
                          LEFT JOIN [joao_db].[dbo].[taggables]
                                  ON [tags].[id] = [taggables].[tag_id]
                   WHERE  [product].[id] = [taggables].[taggable_id]
                          AND [taggables].[taggable_type] = 'App\Product'
                          AND [slug] IN ( 'carbon-steel', 'table' ))
        OR ( [weight_attr].[value] = '13 grams' OR [length_attr].[value] = '10 cm' ) 

I have the following tables:

product :

attributes :

product_string :

tags :

taggable :

category :

category_product :

The models are bellow:

Category model:

public function products() {
return $this->belongsToMany('App\Product', 'category_product', 'category_id', 'product_id')->select( ['attr.*']);
}

Product model

public function attributeSet() {
return $this->hasMany('Eav\AttributeSet', 'entity_id');
}

public function attributes() {
return $this->hasMany('Eav\Attribute', 'entity_id');
}

public function categories() {
return $this->belongsToMany('App\Category', 'category_product', 'product_id', 'category_id');
}

public function tags() {
return $this->morphToMany('App\Tag', 'taggable');
}

Tag model :

public function products()
{
return $this->morphedByMany('App\Product', 'taggable');
}

[joaopmmartins]

The following query gives me 3 product ids: 27891, 24061 and 36514

$products = Product::with('categories')->whereHas('categories', function ($query) {
                        $query->where('slug', request()->category);
                    })->whereAttribute(function ( $query) {
                        $query->whereAttribute(function ($q) {
                            $q->orWhereAttribute('length', '10 cm');
                        });
                    })->get();

The following query gives ONE product id: 27891

$products = Product::with('categories')->whereHas('categories', function ($query) {
                        $query->where('slug', request()->category);
                    })->whereAttribute(function ( $query) {
                        $query->whereAttribute(function ($q) {
                            $q->whereAttribute('weight', '13 grams');
                        });
                    })->get();

The following query should give THREE product id: 27891, 24061 and 36514 but gives me ONE product id: 27891

$products = Product::with('categories')->whereHas('categories', function ($query) {
                        $query->where('slug', request()->category);
                    })->whereAttribute(function ( $query) {
                        $query->whereAttribute(function ($q) {
                            $q->whereAttribute('weight', '13 grams');
                            $q->orWhereAttribute('length', '10 cm');
                        });
                    })->get();

Can you help me on this?

[sunel]

$products = Product::with('categories')->whereHas('categories', function ($query) {
       $query->where('slug', request()->category);
    })
    ->whereAttribute('weight', '13 grams')
    ->orWhereAttribute('length', '10 cm');

Or this

$products = Product::with('categories')->whereHas('categories', function ($query) {
       $query->where('slug', request()->category);
   })->whereAttribute(function ( $query) {
      $query->whereAttribute('weight', '13 grams')
     ->orWhereAttribute('length', '10 cm');
   })->get();

[joaopmmartins]

Sunel, I am trying to filter category products with tags and attributes. Basically, I need to create a query with AND to wrap multiple orWhereInAttribute inside:

Example:

category->products AND (
     product tags in (tag1, tag2, tag3)
     OR
      eav_attributes. IN (attr1, .attr2, ...) 
     OR
     eav_attributes. IN (attr3, .attr4, ...) 
     OR
     eav_attributes. IN (attr5, .attr6, ...) 
)

Can you tell me why the following does not work?

$query->where(function ($q) use ($filter) {
    $q->whereHas('tags', function ($query) {
       $query->where('slug', request()->tag);
     })->orWhereAttribute(function ( $q2) {
           $q2->whereAttribute(function ( $q3) {
                 $q3->orWhereAttribute('size', '=', 'small');
                 $q3->orWhereAttribute('active', '=', '1');
           });
     });
})->get();

The sql outup does not show the attributes conditions: .... .... inner join [category_product] on [product].[id] = [category_product].[product_id] where [category_product].[category_id] = ? THE ATTRIBUTES CONDITIONS ARE MISSING HERE

[sunel]

$posts = Products::whereHas('categories', function ($query) {
        $query->where('name', 'like', '%A%');
    })
    ->orWhereAttribute('search', '=', 1)
    ->orWhereAttribute('name', '=', 'Abigail Hickle')
    ->get(['name', 'sku']);

select 
  (
    select 
      `value` 
    from 
      `product_string` 
    where 
      `attribute_id` = 4 
      and `products`.`id` = `entity_id`
  ) as `name`, 
  (
    select 
      `value` 
    from 
      `product_string` 
    where 
      `attribute_id` = 3 
      and `products`.`id` = `entity_id`
  ) as `sku` 
from 
  `products` 
  inner join `product_boolean` as `search_attr` on `products`.`id` = `search_attr`.`entity_id` 
  and `search_attr`.`attribute_id` = 5 
  left join `product_string` as `name_attr` on `products`.`id` = `name_attr`.`entity_id` 
  and `name_attr`.`attribute_id` = 4 
where 
  exists (
    select 
      * 
    from 
      `category` 
      inner join `product_category` on `category`.`id` = `product_category`.`category_id` 
    where 
      `products`.`id` = `product_category`.`product_id` 
      and `name` like '%A%'
  ) 
  or `search_attr`.`value` = 1 
  or `name_attr`.`value` = 'Abigail Hickle'

$posts = Products::whereHas('categories', function ($query) {
        $query->where('name', 'like', '%A%');
    })->orWhereAttribute(function ($query) {
        $query->whereAttribute('search', '=', 1);
        $query->orWhereAttribute('name', '=', 'Abigail Hickle');
    })
    ->get(['name', 'sku']);

select 
  (
    select 
      `value` 
    from 
      `product_string` 
    where 
      `attribute_id` = 4 
      and `products`.`id` = `entity_id`
  ) as `name`, 
  (
    select 
      `value` 
    from 
      `product_string` 
    where 
      `attribute_id` = 3 
      and `products`.`id` = `entity_id`
  ) as `sku` 
from 
  `products` 
  left join `product_string` as `name_attr` on `products`.`id` = `name_attr`.`entity_id` 
  and `name_attr`.`attribute_id` = 4 
  inner join `product_boolean` as `search_attr` on `products`.`id` = `search_attr`.`entity_id` 
  and `search_attr`.`attribute_id` = 5 
where 
  exists (
    select 
      * 
    from 
      `category` 
      inner join `product_category` on `category`.`id` = `product_category`.`category_id` 
    where 
      `products`.`id` = `product_category`.`product_id` 
      and `name` like '%A%'
  ) 
  or (
    `search_attr`.`value` = 1 
    or `name_attr`.`value` = 'Abigail Hickle'
  )

[sunel]

@joaopmmartins

Can you check with the lasted code made some changes on the join condition