如何用一个序列化数据联接两列? [英] How to JOIN two columns with one serialized data?
问题描述
我正在尝试建立一个对产品提供多类别支持的eshop,因此,每当我从< select>
中选择特定类别时,我 serialize()
并将它们保存到数据库中。
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.
因此,现在,我要输出JOINed表以及所需的所有数据库数据通过尝试加入两个表: app / models / ecommerce_model中的
: CATEGORIES
和 ECOMMERCE_PRODUCTS
。 php
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();
第一个JOIN代表产品类别,第二个JOIN代表产品制造商,它们也存储在<$中c $ c>类别表。
First JOIN stands for product categories, the second JOIN stands for product manufacturers which are also stored in categories
table.
因此要澄清一下, ecommerce_products.catid
其中是在第一个JOIN内部被序列化的,我想知道在执行JOIN之前如何将其反序列化吗?
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 ?
推荐答案
MySQL不会知道什么是PHP序列化。您可以使用以下格式将类别的ID存储为字符串:
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,...
然后,使用 SUBSTRING_INDEX()和 FIND_IN_SET() MySQL函数来检查 categories.id $ c是否存在
ecommerce_products.catid
中的$ c>:
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)
要为每个产品记录选择类别标题,我们需要通过 GROUP_CONCAT()
函数,因此最终查询如下:
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
在这种方法中,您可能需要使用 $ this-> db-> query();
方法来手动运行查询。
In this approach, you might need to use $this->db->query();
method to run the query manually.
注意:
,您可以在 ON
语句中使用以下内容:
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}')
测试用例
这是我的测试用例 SQLFiddle :
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
这篇关于如何用一个序列化数据联接两列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!