And the lesson is, do not ask the NZ PHP User Group mailing list for
database help.
In the first instance, your model is confusing:
1. Is there only one photo with a priority of 1 per article?
2. Do articles have unique IDs? because a KEY is a key, not a UNIQUE
KEY. Nor is it a PRIMARY KEY (additional NOT NULL constraint)
3. Do photos have to reference an article? if so, why aren't you using
FOREIGN KEY or REFERENCES to constrain the relationship (and if your
answer is "I'm not using InnoDB", rethink that immediately).
A straight translation of your model into something more sane:
CREATE TABLE article (
id INT AUTO_INCREMENT PRIMARY KEY,
title TINYTEXT
);
CREATE TABLE photo (
id INT AUTO_INCREMENT PRIMARY KEY,
article_id INT REFERENCES article (id) ON DELETE CASCADE,
name TINYTEXT,
priority INT,
UNIQUE INDEX (article_id, priority)
);
Given this model, there is only one priority N image per article
(enforced), and each photo always references an existing article
(enforced, article deletion removes associated photos).
As a result of this far stricter model, the following query becomes
very efficient:
SELECT article.id, photo.name FROM article JOIN photo ON article.id =
photo.article_id WHERE photo.priority=1;
Alternatively, you can obtain all articles, with an optional photo name:
SELECT article.id, photo.name FROM article LEFT JOIN photo ON
(article.id = photo.article_id AND photo.priority=1);
The secret to good use of an RDBMS is to design your data model to be
precise. Ambiguity causes inefficiency.
At the same time, it is also vital that every database-using
programmer gain a solid understanding of relational database systems.
SQL does not have to be a scary, confusing mess. It can be your best
friend or your worst enemy, and the only difference between the two is
whether you've done the hard yards learning how they work.
Regards,
Richard.
On 11 February 2010 10:22, Chris <[email protected]> wrote:
> Hi guys,
>
> I am using MySql 5.0.89-community
>
> I have two database tables 'articles' and 'article_photos' where each
> article has multiple photos and each photo has an priority field
> associated with it.
>
> I am trying to get a list of articles with a photo for each one that
> has the priority of 1
>
> CREATE TABLE articles (
> article_id int(11) unsigned NOT NULL auto_increment,
> article_title tinytext NOT NULL,
> KEY article_id (article_id)
> );
>
> INSERT INTO articles VALUES (1, 'News Article 1');
>
> CREATE TABLE article_photos (
> photo_id int(11) unsigned NOT NULL auto_increment,
> photo_article_id int(11) unsigned NOT NULL default '0',
> photo_name tinytext NOT NULL,
> priority tinyint(4) unsigned NOT NULL default '1',
> KEY photo_id (photo_id)
> );
>
> INSERT INTO article_photos VALUES (1, 1, 'Photo 1', 2);
> INSERT INTO article_photos VALUES (2, 1, 'Photo 2', 3);
> INSERT INTO article_photos VALUES (3, 1, 'Photo 3', 1);
>
> The query below should return the article with a photo that has the
> priority set to 1 but it does not.
> Does anyone know a workaround for this?
>
> SELECT article_id, photo_name, priority FROM articles
> INNER JOIN (SELECT * FROM article_photos GROUP BY photo_article_id
> ORDER BY priority ASC) photos ON article_id = photos.photo_article_id
>
> --
> NZ PHP Users Group: http://groups.google.com/group/nzphpug
> To post, send email to [email protected]
> To unsubscribe, send email to
> [email protected]
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
