Excuse my ignorance, but is there a tidy way to automatically join or append meta values (set as temporary columns by key) from one table to another?
Table items
ID (int) title (varchar 255)
content (longtext)
Table meta
ID (int)
item_id (int)
key (varchar 255)
value (longtext)
Ideally, I'd like to be able to do a generic query like
SELECT * FROM items
and have the output as
item.ID item.title item.content metakey1 metakey2 1 Listing Title Listing content meta_val1 meta_val2 2 Another Title Listing content meta_val1 meta_val2
You can store your data in XML format and use MySQL XML Functions to extract them.
Example:
create table items (id int, title varchar(100), content longtext);
create table meta (id int, item_id int, metadata varchar(100));
insert into items values (1, 'test item1', 'some content');
insert into meta values (1, 1, '<key1>value1</key1><key2>value2</key2>');
select
i.id, i.title, i.content,
extractvalue(m.metadata, '//key1[$1]') as key1,
extractvalue(m.metadata, '//key2[$1]') as key2
from items i
inner join meta m on i.id = m.item_id;
+------+------------+--------------+--------+--------+
| id | title | content | key1 | key2 |
+------+------------+--------------+--------+--------+
| 1 | test item1 | some content | value1 | value2 |
+------+------------+--------------+--------+--------+
You can create a view called items_with_metadata and then call select * from items_with_metadata to get the kind of output you desire.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments