Where ID is NOT equal to the current Product ID being viewed - Laravel 5.2

Question

I'm displaying similar products in my Product Page. I have a query that finds all products with a similar brand_id or cat_id for the product being viewed. My problem is that it also displays the current product being viewed in the similar section. I need to make it so it removes the current product being viewed from the similar products section.

This is the query I have right now. ( The 'id', '!==', $product->id part is not working)

    /**
     * Show a Product in detail
     *
     * @param $product_name
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function show($product_name) {

        // Find the product by the product name in URL
        $product = Product::ProductLocatedAt($product_name);

        // Select All from "products" table where the brand_id is = to the current product being viewed with its brand_id, OR where
        // the category_id is = to the current product category Id being viewed. This is so we can display similar products for a           // particular product being shown.
        $similar_product = Product::where('brand_id', '=', $product->brand_id)
                 ->where('id', '!==', $product->id)
                 ->orWhere('cat_id', '=', $product->cat_id)->get();

        return view('pages.show_product', compact('product', 'similar_product'));
    }

******** EDIT ******** I'm getting this when using your query method:

Do you know y that might be?

`!==` is not a valid operator. Try `!=`. – patricus Apr 16 '16 at 19:25 — patricus, Apr 16 '16 at 19:25

Todd Christensen · Accepted Answer · 2016-04-16T21:21:50.643

2

Keep in mind when using OR that Laravel's query builder doesn't add parenthesis by default.

https://laravel.com/docs/5.2/queries

Your query will end up like this:

SELECT *
FROM products
WHERE brand_id = 1
   AND id != 2
   OR cat_id = 3

Because of the OR, the results include the product based on its cat_id.

What you probably want is:

$similar_product = Product::where('id', '!=', $product->id)
    ->where(function ($query) use ($product) {
        $query->where('brand_id', '=', $product->brand_id)
            ->orWhere('cat_id', '=', $product->cat_id);
    })->get();

This will put the OR part inside a set of parenthesis:

SELECT *
FROM products
WHERE id != 2
   AND (brand_id = 1 OR cat_id = 3)

Please keep in mind that, often, MySQL does a bad job at optimizing with OR clauses. If your tables are large, you may want to double check the performance and index usage.

edited Apr 16 '16 at 21:21

answered Apr 16 '16 at 19:34

Todd Christensen

1,297
8
11

Oops, sorry, you need a `uses ($product)` on the closure. I'll edit. – Todd Christensen Apr 16 '16 at 21:05
Sorry, but there are some errors, I edited my question in the bottom, have a look. – David Apr 16 '16 at 21:10
1

Oops, I misremembered without proper highlighting - it's `use ($product)` not `uses`. Sorry. – Todd Christensen Apr 16 '16 at 21:21

score 0 · Answer 2 · answered Apr 16 '16 at 19:27

Have you tried changing the !== to <>?

    /**
 * Show a Product in detail
 *
 * @param $product_name
 * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
 */
public function show($product_name) {

    // Find the product by the product name in URL
    $product = Product::ProductLocatedAt($product_name);

    // Select All from "products" table where the brand_id is = to the current product being viewed with its brand_id, OR where
    // the category_id is = to the current product category Id being viewed. This is so we can display similar products for a           // particular product being shown.
    $similar_product = Product::where('brand_id', '=', $product->brand_id)
             ->where('id', '<>', $product->id) // changed this line..
             ->orWhere('cat_id', '=', $product->cat_id)->get();

    return view('pages.show_product', compact('product', 'similar_product'));
}

!== isn't the correct syntax for MySQL.

tried it, doesn't work, I will have to maybe reorder my statement, like karina said above. — David, Apr 16 '16 at 19:31

score 0 · Answer 3 · answered Apr 16 '16 at 19:29

0

I think it is not working because logically you are saying

Brand ID equals AND product is not the same OR category equals

Since there isn't a precedence defined if the category does match that clause will return true for the whole where statement

You need to figure out how to group it so that you query has precedence as follows

(ID is not the same) AND (brand matches OR category matches)

answered Apr 16 '16 at 19:29

karina

805
5
10

Ya, I will have to reorder that definitively – David Apr 16 '16 at 19:31
Not just a reorder. You will need to group it differently. Maybe check this answer http://stackoverflow.com/questions/18660180/laravel-or-where – karina Apr 16 '16 at 19:36

Where ID is NOT equal to the current Product ID being viewed - Laravel 5.2

3 Answers3