How To Join Two Columns With One Serialized Data?
Solution 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 ORDERBY cat.id SEPARATOR '|') as'categories',
p.manid,
p.name
FROM ecommerce_products AS p
LEFTJOIN categories AS cat
ON cat.id = SUBSTRING_INDEX(SUBSTRING_INDEX(p.catid, ',', FIND_IN_SET(cat.id, p.catid)), ',', -1)
-- more query...GROUPBY p.id; -- Group the result to concatenate the categories titlesIn 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:
LEFTJOIN 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 ORDERBY 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)
GROUPBY 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
Solution 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 ).
Post a Comment for "How To Join Two Columns With One Serialized Data?"