Arch Linux

xelados

Member

Registered: 2007-06-02

Posts: 314

Website

Trying to do the right queries..

I have a blog source I'm working on that uses the following tables:

TABLE posts:

+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| date    | int(11)          | NO   |     | NULL    |                | 
| title   | varchar(80)      | NO   |     | NULL    |                | 
| author  | int(11)          | NO   |     | NULL    |                | 
| content | longtext         | NO   |     | NULL    |                | 
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

TABLE tags:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name  | varchar(20)      | NO   |     | NULL    |                | 
| nav   | tinyint(1)       | NO   |     | NULL    |                | 
| icon  | varchar(40)      | YES  |     | NULL    |                | 
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

TABLE tagpost:

+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| tagid  | int(10) unsigned | NO   |     | NULL    |       | 
| postid | int(10) unsigned | NO   |     | NULL    |       | 
+--------+------------------+------+-----+---------+-------+

tags and posts are pretty obvious; tagpost will be filled with multiple duplicates for normalization, basically. The issue I'm having is figuring out which sort of query I need to display posts properly. I'll need to fetch information from posts, tags, and users (for usernames).

I'm not sure where I need to start with this.. I think maybe subqueries or joining is what I need but I can't figure it out for myself.. any help?

I can post some of the code I'm using, but it's sort of hackish PHP.

Edit: Here's a graphical representation of my problem: http://bb.xieke.com/files/mysql-tables.png

Last edited by xelados (2008-10-15 19:19:44)

---

sporkbox@GitHub

e_tank

Member

Registered: 2006-12-21

Posts: 80

Re: Trying to do the right queries..

if i understand you correctly you want:
for i in each post record
    get the name of the user for post i
    get all tagposts for post i
    for j in each tagpost record
        get the name of the tag for tagpost j

if this is the case i believe the following sql will generate the above

select posts.id, posts.title, posts.author, \
    users.name as username, \
    tagpost.tagid, \
    tags.name \
    from posts inner join users on posts.author = users.id \
    inner join tagpost on posts.id = tagpost.postid \
    inner join tags on tagpost.tagid = tags.id;

all the work here is done in the join commands.  to understand how this works think of each join command as a for loop on the left table; for each row in the left table find a matching row in the right based on the join condition and add it to the current result set.  on the first join command the left table is the posts table and the right is the users.  on the second the left table is the results from the previous join command and the right is the tagposts table, and so on.

ssjlegendx

Member

Registered: 2008-01-01

Posts: 94

Website

Re: Trying to do the right queries..

select posts.id, posts.title, posts.author, \
    users.name as username, \
    tagpost.tagid, \
    tags.name \
    from posts inner join users on posts.author = users.id \
    inner join tagpost on posts.id = tagpost.postid \
    inner join tags on tagpost.tagid = tags.id;

Unless you require that all posts have at least one tag, you should replace the last two INNER JOINs (or at least the second-to-last one -- I'm not certain what would happen in that case) with LEFT OUTER JOINs; as it stands, posts with no tags would be excluded from the results. (I assume an author is required, in which case the first INNER JOIN shouldn't cause a problem.)

Edit: I just verified in MySQL that -- indeed -- both of the last two joins should be LEFT OUTER JOINs (or, equivalently, LEFT JOINs).

Last edited by ssjlegendx (2008-10-16 01:04:32)

---

#!/vim/rocks