SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Stillwater, MN
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing a two-way relationship?

    I'm coding a blog, and I have a two-way relationship between my posts and categories. A post can have multiple categories and a category can have multiple posts. I also have a table that brings them togeter, posts_categories, using both IDs.

    The problem with this is it seems very inefficient when listing articles. I would use one query for all of the articles, but then I would have to use another query for every article. If I have 10 articles on a page, that means there are 11 queries. This doesn't seem like the best way to do it. Does anyone have suggestions?

  2. #2
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT * FROM articles, posts_categories
    WHERE
        articles.id = posts_categories.article_id
        AND posts_categories.category_id = 4
    Read more at http://www.w3schools.com/sql/sql_join.asp.
    There's a database forum for that btw.

  3. #3
    SitePoint Zealot CdeMky's Avatar
    Join Date
    Sep 2004
    Location
    United States
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Radley
    I'm coding a blog, and I have a two-way relationship between my posts and categories. A post can have multiple categories and a category can have multiple posts. I also have a table that brings them togeter, posts_categories, using both IDs.

    The problem with this is it seems very inefficient when listing articles. I would use one query for all of the articles, but then I would have to use another query for every article. If I have 10 articles on a page, that means there are 11 queries. This doesn't seem like the best way to do it. Does anyone have suggestions?

    an M X N relation typically requires 3 tables.


    So if you had two tables such as Employee & Organization, you would also need a "Belongs_To" table where the employee_id, and organization_id make up the primary key with a foreign key on both Employee.employee_id and Organization.organization_id

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Stillwater, MN
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken
    Code:
    SELECT * FROM articles, posts_categories
    WHERE
        articles.id = posts_categories.article_id
        AND posts_categories.category_id = 4
    Read more at http://www.w3schools.com/sql/sql_join.asp.
    There's a database forum for that btw.
    I know how to use joins and get all posts by category. However, if you want to display all posts and all of the categories that post is under, you would need 1 query per post, right?

    Quote Originally Posted by CdeMky
    an M X N relation typically requires 3 tables.


    So if you had two tables such as Employee & Organization, you would also need a "Belongs_To" table where the employee_id, and organization_id make up the primary key with a foreign key on both Employee.employee_id and Organization.organization_id
    That is how it is set up. My original problem is that it seems to be very slow to display all posts and the categories each post is under.

    The script I am working with is in procedural, and this is what it looks like currently.
    PHP Code:
    $posts mysql_query('SELECT * FROM posts');
    while (
    $post mysql_fetch_assoc($posts)) {
        
    $categories mysql_query('SELECT category.title FROM categories, posts_categories WHERE posts_categories.post_id = ' $post['id']);
        
    // display post

    This seems very ineffecient to me, and I was wondering if there was a better way to do it. I have been thinking and it doesn't really seem possible to do it another way as you can't really put multiple values in a field when you're selecting something (as far as I know). Perhaps I should just leave the categories out of the post list for performance and display the categories on individual post display?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Radley
    However, if you want to display all posts and all of the categories that post is under, you would need 1 query per post, right?
    absolutely not


    Quote Originally Posted by Radley
    I have been thinking and it doesn't really seem possible to do it another way as you can't really put multiple values in a field when you're selecting something (as far as I know).
    this is quite possible in mysql 4.1

    you want all articles, and all the categories each article is under?

    could you show the layout of the three tables please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Stillwater, MN
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    this is quite possible in mysql 4.1

    you want all articles, and all the categories each article is under?

    could you show the layout of the three tables please
    Here is what is important:

    Post has an ID, and also the standard post fields (title, body, etc..)

    Category has an ID and title

    Posts_categories has post_ID and category_ID

    I was able to figure out a solution that involves 2 queries instead of 11. I just made an array of all of the categories and which posts they belong to beforehand.

    If you can do that in 1 query I'm all for it, though

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select P.ID
         , P.title
         , P.body
         , Q.categories
      from Post as P
    inner
      join (
           select group_concat(C.title) as categories
             from Post_categories as PC
           inner
             join Category as C
               on PC.category_ID = C.ID
           group
               by PC.post_ID
           ) as Q    
        on P.ID = Q.post_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •