other conditions does not work with full text search in laravel and mysql-CodePudding

I've implemented the full text search in laravel and mysql and it works fine,but the problem appears when trying to put conditions in my query on fields not related to the full text index such as status and type "they are stored as enum".I am trying to select products with type = image and status = 1, but the result contains products in all types and any status that matches the full text search.

Here the query

Product::accepted()
            ->select('products.id as id','products.type','products.display_image as display_path',
                'products.watermark',
                DB::raw("concat('[', group_concat(JSON_OBJECT('id', keywords.id , 'keyword',keywords.keyword)
                                    order by keywords.id separator ','), ']') as keywords"))
            ->join('products_keywords','products_keywords.product_id','products.id')
            ->join('keywords','keywords.id','products_keywords.keyword_id')
            ->whereRaw(
                "products.type = ?
                 AND MATCH(keywords.keyword) AGAINST(? IN BOOLEAN MODE)
                  OR MATCH(products.description) AGAINST(? IN BOOLEAN MODE)
                   Or MATCH(products.description_en) AGAINST(? IN BOOLEAN MODE)",
                ['image',$searchTerms,$searchTerms,$searchTerms])
            ->groupBy('products.id')->groupBy('products.display_image')
            ->groupBy('products.type')->groupBy('products.watermark')
            ->paginate(50);

Accepted is a scope function in product model that implements the condition status = 1

public function scopeAccepted($query)
    {
        return $this->where('products.status','1');
    }

CodePudding user response：

The problem looks like the first OR in your raw sql part is negating all prior where attributes. You could wrap everything after the AND into a set of parentheses.
You might want to change

            ->whereRaw(
                "products.type = ?
                 AND MATCH(keywords.keyword) AGAINST(? IN BOOLEAN MODE)
                  OR MATCH(products.description) AGAINST(? IN BOOLEAN MODE)
                   Or MATCH(products.description_en) AGAINST(? IN BOOLEAN MODE)",
                ['image',$searchTerms,$searchTerms,$searchTerms])

            ->whereRaw(
                "products.type = ?
                 AND (MATCH(keywords.keyword) AGAINST(? IN BOOLEAN MODE)
                  OR MATCH(products.description) AGAINST(? IN BOOLEAN MODE)
                   Or MATCH(products.description_en) AGAINST(? IN BOOLEAN MODE))",
                ['image',$searchTerms,$searchTerms,$searchTerms])