I hate responding to my self on a list, but I don't think I outlined the
problem clearly enough:
The article table data looks like this:
select * from articles;
+----+--------+
| id | active |
+----+--------+
| 1 | 1 |
+----+--------+
and the articles_attributes table looks like this:
select * from articles_attribs;
+------------+--------+-------------+---------------------------------+
| article_id | locale | attrib_key | attrib_value |
+------------+--------+-------------+---------------------------------+
| 1 | en_CA | content | <h1>Some Content</h1> |
| 1 | en_CA | description | This is the article description |
| 1 | en_CA | keyword | These are, article, keywords |
| 1 | en_CA | title | Test article |
+------------+--------+-------------+---------------------------------+
If I want to select all articles, my first thought was to do this:
SELECT a.id, a.active, b.* FROM articles as a LEFT JOIN articles_attribs
as b ON a.id = b.article_id
however, this results in an "un-normalized" result set:
+----+--------+------------+--------+-------------+---------------------------------+
| id | active | article_id | locale | attrib_key |
attrib_value |
+----+--------+------------+--------+-------------+---------------------------------+
| 1 | 1 | 1 | en_CA | content | <h1>Some
Content</h1> |
| 1 | 1 | 1 | en_CA | description | This is the article
description |
| 1 | 1 | 1 | en_CA | keyword | These are, article,
keywords |
| 1 | 1 | 1 | en_CA | title | Test
article |
+----+--------+------------+--------+-------------+---------------------------------+
As you can see, I have 4 rows for article of ID one. One row for each
article attribute.
What I want to have returned to me would look something like this (I
really want to avoid programmatically cleaning up the result set):
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| id | active | content | title |
description | keyword |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| 1 | 1 | <h1>Some Content</h1> | Test article | This is the
article description | These are, article, keywords |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
The closets I have come to is this beastly SQL:
SELECT
a.id,
a.active,
a1.attrib_value as `content`,
a2.attrib_value as `title`,
a3.attrib_value as `description`,
a4.attrib_value as `keyword`
FROM articles as a
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'content') as a1
ON
a.id = a1.article_id
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'title') as a2
ON
a.id = a2.article_id
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'description') as a3
ON
a.id = a3.article_id
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'keyword') as a4
ON
a.id = a4.article_id;
which results in what I am looking for:
Code:
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| id | active | content | title |
description | keyword |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| 1 | 1 | <h1>Some Content</h1> | Test article | This is the
article description | These are, article, keywords |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
However, these subqueries look like they will be wildly inefficient.
What I gather the DB (MySQL) is doing here is getting _all_ attributes
of type X for all articles, and then joining that result set together
with article ID of the current row.
Any ideas?
Michael Caplan wrote:
Hi there,
I am trying to work through a DB design that is giving me some trouble
with the result sets. The situation is this:
I have a table called "articles" and a related table call
"article_attributes" Within the table "articles" I am maintaining
basic info about an article, such as article id, active, etc.
However, I am not maintaining any lanuage specific data about the
article in that table (title, description, body, etc). All that info
is stored in "articles_attributes". The goal of storing all language
specific info about an article in a seperate table is two fold:
[CODE][/CODE]
1) I wish to maintain multiple language versions of an article without
prior knowedge to the languages I have to handle,
2) I also wish to be able to easly add arbitrary new article
attributes (eg: footnotes) without needing the modify the db tables.
This is what I came up with for the structure:
CREATE TABLE `articles` (
`id` int(10) unsigned NOT NULL default '0',
`active` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `active_idx_idx` (`active`)
)
CREATE TABLE `articles_attribs` (
`article_id` int(10) unsigned NOT NULL default '0',
`locale` varchar(5) NOT NULL default ' ',
`attrib_key` varchar(255) NOT NULL default ' ',
`attrib_value` longtext NOT NULL,
PRIMARY KEY (`article_id`,`locale`,`attrib_key`),
KEY `values_idx_idx` (`attrib_value`(767))
)
This works okay, however, when querying the database for one record,
because of the join between articles => articles_attributes, I don't
get one result set, but rather 1 X the number of attributes recorded
for the article:
+----+--------+------------+--------+-------------+---------------------------------+
| id | active | article_id | locale | attrib_key |
attrib_value |
+----+--------+------------+--------+-------------+---------------------------------+
| 1 | 1 | 1 | en_CA | content | <h1>Some
Content</h1> |
| 1 | 1 | 1 | en_CA | description | This is the
article description |
| 1 | 1 | 1 | en_CA | keyword | These are,
article, keywords |
| 1 | 1 | 1 | en_CA | title | Test
article |
+----+--------+------------+--------+-------------+---------------------------------+
What I am struggling with is an elegant way I can normalize the result
set. I want to end up with a result set that looks like this:
+----+--------+------------+--------+-----------------------+---------------------------------+------------------------------+--------------+
| id | active | article_id | locale | content |
description | keyword |
title |
+----+--------+------------+--------+-----------------------+---------------------------------+------------------------------+--------------+
| 1 | 1 | 1 | en_CA | <h1>Some Content</h1> | This is
the article description | These are, article, keywords | Test article |
+----+--------+------------+--------+-----------------------+---------------------------------+------------------------------+--------------+
I can programmatically go through the result set and flatten it so
that all attrib_key values => corresponding attrib_value, but this is
less than ideal. I'm wondering if any of you have ideas how I can
acheive the desired result with some creative SQL?
Thanks,
Michael
--
Michael Caplan - Zend Certified PHP Engineer
Programming Manager
Apison Communications
Suite 110, 151 Provost Street
New Glasgow, NS, Canada B2H 2P6
Phone: (902) 695-3375
Toll Free: (800) 845-6998
Fax: (902) 695-7777
email: [EMAIL PROTECTED]
URL: http://www.apison.com
Specializing in web development, graphic design and Internet marketing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
