How to JOIN two columns with one serialized data?

Question

I'am trying to build an eshop with multicategory support for products, so whenever I select a specific categories from <select>, I serialize() them and save them into DB.

So for now, I would like to output the JOINed tables with all db datas I need by trying to join two tables: CATEGORIES and ECOMMERCE_PRODUCTS in my app/models/ecommerce_model.php:

$this->db
    ->select('
            ecommerce_products.id,
            categories.id AS catid,
            categories.title AS categories,
            ecommerce_products.manid,
            ecommerce_products.name
        ')
    ->join('categories', 'ecommerce_products.catid = categories.id', 'left');
    ->join('categories as man', 'ecommerce_products.manid = man.id', 'left');
    $this->db->get('ecommerce_products')->result();

First JOIN stands for product categories, the second JOIN stands for product manufacturers which are also stored in categories table.

So to clarify, ecommerce_products.catid which is called inside the first JOIN is serialized, and I wonder how to unserialize it before doing a JOIN ?

Answer 1

MySQL doesn't know what a PHP serialization is. You could store the IDs of categories as a string in the following format:

2,4,5,10,...

Then, use a combination of SUBSTRING_INDEX() and FIND_IN_SET() MySQL functions to check the existence of categories.id in ecommerce_products.catid:

SUBSTRING_INDEX(
    SUBSTRING_INDEX(ecommerce_products.catid,
        ',',
        FIND_IN_SET(categories.id, ecommerce_products.catid)
), ',', -1)

To select the categories titles for each product record, we need to concatenate the titles by GROUP_CONCAT() function, So the final query would be something like this:

SELECT p.id,
       p.catid
       GROUP_CONCAT(cat.title ORDER BY cat.id SEPARATOR '|') as 'categories',
       p.manid,
       p.name
FROM ecommerce_products AS p
LEFT JOIN categories AS cat
    ON cat.id = SUBSTRING_INDEX(SUBSTRING_INDEX(p.catid, ',', FIND_IN_SET(cat.id, p.catid)), ',', -1)
-- more query...
GROUP BY p.id; -- Group the result to concatenate the categories titles

In this approach, you might need to use $this->db->query(); method to run the query manually.

Note:
As an alternative, you could use the following for ON statement:

LEFT JOIN categories AS cat
    ON p.catid REGEXP CONCAT('[,]{0,1}', cat.id, '[,]{0,1}')

Test-case

Here is my test-case on SQLFiddle:

SELECT p.id,
       p.name,
       GROUP_CONCAT(c.title ORDER BY c.id SEPARATOR '|') as 'categories'
FROM products as p
JOIN categories as c
  ON c.id = SUBSTRING_INDEX(SUBSTRING_INDEX(p.cat_id, ',', FIND_IN_SET(c.id, p.cat_id)) , ',', -1)
GROUP BY p.id;

Result:

ID    NAME          CATEGORIES
--    ---------     -----------
1     Product 1     Cat 1|Cat 3
2     Product 2     Cat 2|Cat 4
3     Product 3     Cat 1|Cat 4
4     Product 4     Cat 2|Cat 3

Answer 2

I think in this case is bad solution to serialize ecommerce_products.catid`s which would later be used for joining tables. Unknown as will be used serialized data in the future and what problems may arise: look, you already have a problem with a simple select with joins.

So while no later, I would advise to reconsider that approach. I think, in this specify moment the most optimal solution is to add third table categories2ecommerce_products with two fields: category_id and ecom_product_cat_id (for example). It will connect (relate) categories with ecommerce_products by theirs ids ( create table of ties ).