SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditional JOIN based on a row's column value

    Tell me if I designed this table poorly and I'll change it, but this is what I have:
    Code:
                   FilterSlug
    id | slug | filter_type | lookup_id
    FilterSlug.slug is a unique string
    FilterSlug.filter_type determines the type of filter and used to tell which table to join with to get the rest of the info on that table
    FilterSlug.lookup_id is the unique key used to lookup the info once the join is established.

    What I would like to be able to do is select all of the information based on the slug given. Example:
    Code:
    SELECT info.*
      FROM FilterSlug
      LEFT OUTER
      JOIN article AS info
        ON article.id = FilterSlug.lookup_id
     WHERE FilterSlug.slug = 'this-is-a-slug'
    (fake query I just made up)

    The hard part is that based on the FitlerSlug.filter_type, the JOIN may be an article, a category, an author, etc. What is a good way to do this?

    I tried joining all 3 days and using a conditional to just retrieve the cols accordingly, but I got an error. Ideally I would be able to just JOIN the tables that mattered, but I don't know if you can use CASE in the JOIN clause.

    Any ideas? Maybe I've made it more difficult than it has to be?
    MySQL v5.1.58
    PHP v5.3.6

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    something definitely amiss with this design, but before i can make a suggestion, i need to understand something

    what's a slug? what are they for? why do you need a separate table for them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Edit:

    My attempt was a mess, I gave up.

  4. #4
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a "slug" (a term stolen from WordPress) is a translation from a title to a URI piece. For example if your article name is "Save 10% By Switching to Verizon", the slug might look like "save-10-by-switching-to-verizon" or even "save-10-percent-by-switching-to-verizon". It can be auto generated or hand written.

    I decided to create a separate table because they need to be absolutely unique across the 4 different tables. If I have an author that is "john-smith" and then someone comes along and writes an article "john-smith", they share the same slug and I won't be able to tell which article to reference based on the URI.

    Now I know someone is going to say "why not preface the slug with a type in the URI", and my reason for that is beyond a MySQL thread, but it's simply that I want to keep the URL's simple as possible (in this case they might be /blog/john-smith/ or /read/john-smith/).
    MySQL v5.1.58
    PHP v5.3.6

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dan, i saw your attempt in the email announcement that goes out when a thread is updated

    it wasn't bad at all -- in fact, that sql was exactly what this particular model forces you to write

    you nailed it in one -- a mess

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you were temporarily to set aside the issue of a slug being unique across all tables, what other purpose would the single table have? none

    joining a single table to one of possibly four other tables, maybe more...

    ... is a mess

    my advice: translate the uri using a lookup table exactly like your current FilterSlug table

    this will tell you what type of thing you're dealing with

    then issue a second, subsequent query against the appropriate table for that type of thing

    keep it simple
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, two queries would actually keep my PHP code very simple as well. I like the idea and will try to go that route.

    I can see why you would frown on the separate table for the slugs, and I was very reluctant to use it. The problem was enforcing a unique column across multiple tables (especially when the number of tables is likely to get larger as we add more criteria for filtering).

    Any other input on this before I write the code?
    MySQL v5.1.58
    PHP v5.3.6

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, you have successfully passed my wee little test

    now do this --
    Code:
    select 'article' as type
         , article_id as id
      from articles
     where article_slug = 'this-slug'
    union all
    select 'category' as type
         , category_id as id
      from categories
     where category_slug = 'this-slug'
    union all
    select 'author' as type
         , author_id as id
      from categories
     where author_slug = 'this-slug'
    here we can see that each separate table has its own slug column, and that this query simply returns the id and the name of the table that it was found in

    now you do your second query to return the details of whatever type it was

    see? i made your filterslug table disappear!

    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
  •