CODEWITHSUNDEEP
mysqllaravel
TuanTai
TuanTai

Reputation: 140

How to select products by multi tags

I have a data example like this image enter image description here

I'd like to get products have tags: phone, 8th and apple (iphone 8)

What is query when I using mysql ?

What is code when I using Laravel Eloquent ORM ?

Sorry for my poor English. Many thanks !

--- Update ---

Here is my query solution, it return my desired product (iPhone 8) but it isn't good solution

SELECT * 
FROM products
WHERE id IN (
    SELECT product_id 
    FROM maps 
    JOIN tags ON maps.tag_id = tags.id 
    WHERE tags.name = "phone"
    AND product_id IN (
        SELECT product_id 
        FROM maps 
        JOIN tags ON maps.tag_id = tags.id 
        WHERE tags.name = "8th"
        AND product_id IN (
            SELECT product_id 
            FROM maps 
            JOIN tags ON maps.tag_id = tags.id 
            WHERE tags.name = "apple"
        )
    )
)

Upvotes: 1

Views: 728

Answers (4)

TuanTai
TuanTai

Reputation: 140

Thanks all answers, this issue has been resolved

Mysql query:

SELECT * 
FROM products
WHERE id IN (SELECT product_id FROM maps JOIN tags ON maps.tag_id = tags.id WHERE tags.name = "phone")
AND id IN (SELECT product_id FROM maps JOIN tags ON maps.tag_id = tags.id WHERE tags.name = "8th")
AND id IN (SELECT product_id FROM maps JOIN tags ON maps.tag_id = tags.id WHERE tags.name = "apple");

Laravel code:

$tags = ['phone','8th','apple'];
$products = new Product();
foreach ($tags as $value) {
    $query = Map::select('product_id')
    ->join('tags','maps.tag_id','=','tags.id')
    ->where('tags.name',$value);
    $products = $products->whereIn('id',$query);
}
$products = $products->get();

Upvotes: 0

Hardeep Singh
Hardeep Singh

Reputation: 780

Following is query

SELECT
    * 
FROM
    products
    WHERE id IN(
            SELECT 
                DISTINCT maps.product_id 
            FROM
                maps
                INNER JOIN tags ON maps.tag_id = tags.id
            WHERE
                tags.name IN ('phone', '8th', 'apple')
    );

Upvotes: 0

Morteza Rajabi
Morteza Rajabi

Reputation: 2913

First, you need to add relation in your models.

Product Model:

public function tags()
{
    return $this->belongsToMany(\App\Tag::class, 'maps', 'product_id', 'tag_id');
}

Tag Model:

public function products()
{
    return $this->belongsToMany(\App\Product::class, 'maps', 'tag_id', 'product_id');
}

Now, in order to get the desired products you can do the followings:

$products = Product::whereHas('tags', function ($query) {
    $query->where('name', 'phone')
    ->orWhere('name', '8th')
    ->orWhere('name', 'apple');
})
->get();

If you have an array of your tag names, then you can do:

$products = Product::whereHas('tags', function ($query) use ($tagNames) {
    $query->whereIn('name', $tagNames);
})
->get();

Upvotes: 1

Guilherme Duarte
Guilherme Duarte

Reputation: 101

You can get all products using Eloquent and Collections.

$tags = Tag::whereIn('name', array('phone', '8th', 'apple'))->with('products')->get();
$products = (collect($tags->pluck('products')))->collapse()->unique('id');

Set products relation in Tag entity.

class Tag extends Model
{
  public function products()
  {
    return $this->belongsToMany('App\Product', 'maps', 'tag_id', 'product_id');
  }
}

Upvotes: 0

Related Questions