I want filter product by gender(men,women)

gender of the product saved in database within json format like ["\u0622\u0642\u0627\u06cc\u0627\u0646"]

in my controller want get product by gender name

    public function index(){     
        $product = Product::where(['gender' => json_decode('men',true)])
            ->orderBy('created_at','Desc')->get();
}

how can fetch product that gender is men or women Some products can be used for both men and women, so I save in Json format

dd from product as you can see gender store in json format:

thanks a lot for your help

CodePudding user response：

The problem is that in the database you wrote in persian, but in the controller you wrote in english. I would suggest to save this data in english into your database, but if it is not an option try this:

 $product = Product::whereIn('gender',  ['man-in-persian-language','woman-in-persian-language'] )
        ->orderBy('created_at','Desc')
        ->get();

Also I dont know how many genders you have in your projects, but if there is only 'man' and 'woman' then there is no need to query for gender, and you will get al products which gender is man or woman.

CodePudding user response：

Searching values in Json

Mysql

Actually having json data for a column, make it hard for indexing and searching in RDBMS databases like Mariadb, mysql, ....

Key-value No-Sql DBMS

if you use mongo, cockroach, DynoDB, ... they are embracing this approach. but you would need to redesign all the DB architecture !!!

Postgres

but if you are using Postgres DB it would be fine as long as you use the Jsonb data type for the gender column.

but as i can see your foreign keys as 1,2,3 ..., I assume that you are using mysql maybe.

Redesign DB Schema instead of migrating to Postgres

I would like to recommend to use boolean or enum type column which more suites your scenario, instead of Json.

• with boolean columns you can have multiple selected attributes for given data. like : either "Mardaneh" & "Zananeh" & "Bacheganeh" ...

• with the enum you would be sure at DB level that, there is a gender has been selected

in your case I guess two boolean column makes more sense.