Need help for complex query - Printable Version

Need help for complex query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Need help for complex query (/showthread.php?tid=77723)

Need help for complex query - webdevron - 10-10-2020

I have a product table, category table and another table to join product and category where category stored in JSON format. What I need to do is to get all the product with a list category IDs.

Category table

Code:
-----------------------

| id | category_name

-----------------------

Product table

Code:
-----------------------

| id | product_title

-----------------------

Product Category relation table

Code:
-----------------------

| product_id | categories [varchar(100) but data stored in JSON format]

-----------------------

| 1          | [1,4,6]

-----------------------

| 2          | [34,8,9]

-----------------------

| 2          | [27,18,29]

According to example, what will be the query if I need to get all the product with the category list (array)[34,18,6]

I will be very glad if you suggest any better table structures. Thanks in advance. Shy

RE: Need help for complex query - nc03061981 - 10-10-2020

PHP Code:
//With current data store:
    $find  = [34,18,6];
    $where = [];

    foreach ($find as $cID)
    {
        $where[] = "categories LIKE '%[".$cID.",%'";
        $where[] = "categories LIKE '%,".$cID.",%'";
        $where[] = "categories LIKE '%,".$cID."]%'";
    }
    
    $strW = "(".implode(" OR ", $where).")";
    
    // Query
    $sql  = " SELECT product_id";
    $sql .= " FROM Product_Category";
    $sql .= " WHERE ".$strW; 

RE: Need help for complex query - nc03061981 - 10-11-2020

After

PHP Code:
$query = "SELECT * FROM Product WHERE id IN (".$sql.")"; 

RE: Need help for complex query - T.O.M. - 10-11-2020

It is not very nice way to store multiple values in one field - it is not convenient and not practical.
You can modify your relationship table to store category IDs one per field, but on different rows.
So your table structure should be:

Category table:

Code:
-----------------------

| id | category_name

-----------------------

Product table:

Code:
-----------------------

| id | product_title

-----------------------

Product_category table:

Code:
-----------------------

| product_id | category_id

-----------------------

| 1          | 1

-----------------------

| 1          | 4

-----------------------

| 1          | 6

-----------------------

| 2          | 34

-----------------------

| 2          | 8

-----------------------

| 2          | 9

-----------------------