SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Hiding categories that contain no results

    I was asked recently to create a searchable database for a number of publications and research documents that live on our website. The content is all static pages, but there's getting to be a considerable number of them (hundreds) so the users end up scrolling and reading through all these titles to find what they need. So I've made the index page a search form where you can choose various criteria (categories, document types) for your search.

    I have an SQL query that I use to build the search form (a bunch of SELECT elements, because the content owner preferred the look of that to checkboxes), and for any of the search categories you can choose "ALL" and get all the document types or categories.

    It's very common for a user to choose a document type, and leave "ALL" for the remaining criteria. But as you start to narrow down your results, the possibility of your search coming up empty becomes more likely. So I'd like to remove the criteria options with zero results when someone searches on a document type. I've been able to do this in the past by joining all the tables together with inner joins, but I this time it's not working, and I think it's because of how I have my database set up.

    The database consists of:

    documents table (cc_docs)
    doc_id
    doc_title
    doc_desc
    doc_url (the folder where the publication resides)

    Each document has a doctype: (cc_types). Each document can have more than one type.
    type_id
    type_name

    A table joining those two (cc_doctype)
    dt_id (I put an autonumber ID column on everything)
    doc_id (joined to the docs table)
    type_id (joined to the types table)

    Finally, each document can have many categories. The categories are arranged in a parent/child category table (cc_categories).
    cat_id
    cat_name
    cat_desc
    parent_cat

    Top level categories have a null parent_cat. Nothing gets assigned to parents categories.

    To join the categories to docs, there's another table (cc_doccat)
    dc_id
    doc_id (joined to the docs table)
    cat_id (joined to the categories table)

    The SQL to build the form is fairly simple.

    Code SQL:
    SELECT    cc_categories.cat_id AS parent_id
    	, cc_categories.cat_name AS parent_name
        	, cc_categories.cat_desc AS parent_desc
            , sub.cat_id AS sub_id
            , sub.cat_name AS sub_name
    FROM cc_categories
    LEFT JOIN cc_categories AS sub
    		ON sub.parent_cat = cc_categories.cat_id
    WHERE cc_categories.parent_cat IS NULL
    ORDER BY cc_categories.cat_name, sub.cat_name

    This lets me build a nice form with some labels and drop-downs. My next step was to join the other tables, but if I use inner joins (below) my criteria elements vanish.

    Code SQL:
    SELECT    cc_categories.cat_id AS parent_id
    	, cc_categories.cat_name AS parent_name
        	, cc_categories.cat_desc AS parent_desc
            , sub.cat_id AS sub_id
            , sub.cat_name AS sub_name
    FROM ( ( ( cc_categories
    LEFT JOIN cc_categories AS sub
    	ON sub.parent_cat = cc_categories.cat_id
            )
    INNER JOIN cc_doccat
    	ON cc_doccat.cat_id = cc_categories.cat_id
    		)
    INNER JOIN cc_doctype
    	ON cc_doctype.doc_id = cc_doccat.doc_id
            )
    INNER JOIN cc_types
    	ON cc_types.type_id = cc_doctype.type_id
    WHERE cc_categories.parent_cat IS NULL
    ORDER BY cc_categories.cat_name, sub.cat_name

    And if I use left joins of course I get the same results as not joining the tables at all, until I filter for document type with the WHERE clause, at which point I also lose my categories.

    I'm thinking that I need a more complex query due to how my categories table is set up, but I'm not exactly sure where to go from here.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first of all, thanks for the detailed explanation -- i wish more people did this when asking questions

    Quote Originally Posted by cydewaze View Post
    So I'd like to remove the criteria options with zero results when someone searches on a document type.
    you lost me right here


    Quote Originally Posted by cydewaze View Post
    dt_id (I put an autonumber ID column on everything)
    oh, FFS!!!!

    i hope you declare a composite UNIQUE index on the other two columns


    Quote Originally Posted by cydewaze View Post
    ... but if I use inner joins (below) my criteria elements vanish.
    criteria elements? what are these?

    also, why do you force evaluation of joins using parentheses?

    (oh, wait... is this MS Access?)

    try this --
    Code:
    SELECT ...
      FROM cc_categories
    INNER 
      JOIN cc_doccat
        ON cc_doccat.cat_id = cc_categories.cat_id
    INNER 
      JOIN cc_doctype
        ON cc_doctype.doc_id = cc_doccat.doc_id
    INNER 
      JOIN cc_types
        ON cc_types.type_id = cc_doctype.type_id
    LEFT OUTER
      JOIN cc_categories AS sub
        ON sub.parent_cat = cc_categories.cat_id
     WHERE cc_categories.parent_cat IS NULL
    ORDER 
        BY cc_categories.cat_name
         , sub.cat_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    first of all, thanks for the detailed explanation -- i wish more people did this when asking questions

    you lost me right here
    Hah, well I guess my detailed explanation was still lacking in some ways.

    Say I choose "Case Studies" for a document type, and click submit. This returns 15 documents.

    For categories, I have things like this:

    Transportation Mode (parent category)
    - Car
    - Bike
    - Rail
    - Bus
    - Aviation
    - Other

    Location
    [list of US states]

    Climate Impacts
    - Wind
    - Storm
    - Sea level rise
    - Storm Surge
    - Temperature

    etc...

    Out of those 15 results, there may be zero that are in Kentucky, so if I chose that state, I get zero results. In fact, those 15 results might only occur in 9 states, so I'd like to be able to hide the rest. My select drop-down would only contain the states that have documents associated with them.


    Quote Originally Posted by r937 View Post
    also, why do you force evaluation of joins using parentheses?

    (oh, wait... is this MS Access?)
    Yes. Someone killed our SQL Server setup Monday night, so nothing SQL-related is working unless it's using Access. Although the parens seem to work fine in SQL Server too.

    Quote Originally Posted by r937 View Post
    try this --
    Code:
    SELECT ...
      FROM cc_categories
    INNER 
      JOIN cc_doccat
        ON cc_doccat.cat_id = cc_categories.cat_id
    INNER 
      JOIN cc_doctype
        ON cc_doctype.doc_id = cc_doccat.doc_id
    INNER 
      JOIN cc_types
        ON cc_types.type_id = cc_doctype.type_id
    LEFT OUTER
      JOIN cc_categories AS sub
        ON sub.parent_cat = cc_categories.cat_id
     WHERE cc_categories.parent_cat IS NULL
    ORDER 
        BY cc_categories.cat_name
         , sub.cat_name
    When I do that and load the page (without submitting the form) I only see the document type dropdown. The categories are all hidden. Same if I search on a doctype.

    Could this be because none of the parent categories have any documents assigned, and since the parents are hidden, so are the subs?
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    When I do that and load the page (without submitting the form) I only see the document type dropdown.
    you realize that i can't see your dropdown, or whatever else is going on in your app

    how about testing your queries directly in the database, i.e. not driving your app

    i can help you with sql, but not application behaviour
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If I cfdump the query, the categories (sub and parent) are all null.

    But in doing this I realized that I was doing a separate query to get the list of document types (left over from a previous version of the form).

    If I pull the list of document types to the query above, they come up null as well.

    I need to take another look at this to figure out why my query is coming up with an empty result set when no search parameters are given.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ok, I'm now much closer. I have the result set mostly the way I want it with this:

    Code SQL:
    SELECT    cc_docmod.doc_id AS docid
    	, cc_types.type_name
    	, cc_types.type_id
            , parents.cat_id AS parent_id
    	, parents.cat_name AS parent_name
            , cc_categories.cat_id AS sub_id
            , cc_categories.cat_name AS sub_name
    FROM ( ( ( ( cc_docs
    INNER JOIN cc_doctype
    	ON cc_doctype.doc_id = cc_docs.doc_id
            )
    INNER JOIN cc_types
    	ON cc_types.type_id = cc_doctype.type_id
            )
    INNER JOIN cc_doccat
    	ON cc_doccat.doc_id = cc_docs.doc_id
            )
    INNER JOIN cc_categories
    	ON cc_categories.cat_id = cc_doccat.cat_id
            )
    LEFT OUTER JOIN cc_categories AS parents
    	ON parents.cat_id = cc_categories.parent_cat
    WHERE parents.parent_cat IS NULL
    ORDER BY cc_docmod.doc_id, cc_types.type_id, parents.cat_id, cc_categories.cat_id

    I decided to write the query as if I were making a list of all the documents in the database, then added categories and document types, then just removed from the SELECT statement the things I didn't need. I ended up changing how the parents/children were joined.

    I have what I'm looking for, but I have dupe results, so I think I have to handle that in my presentation code. Or maybe fiddle with the ORDER BY.
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ok, this solution works for the categories:

    Code SQL:
    SELECT    cc_types.type_name
    	, cc_types.type_id
            , parents.cat_id AS parent_id
    	, parents.cat_name AS parent_name
            , cc_categories.cat_id AS sub_id
            , cc_categories.cat_name AS sub_name
    FROM ( ( ( ( cc_docs
    INNER JOIN cc_doctype
    	ON cc_doctype.doc_id = cc_docs.doc_id
            )
    INNER JOIN cc_types
    	ON cc_types.type_id = cc_doctype.type_id
            )
    INNER JOIN cc_doccat
    	ON cc_doccat.doc_id = cc_docs.doc_id
            )
    INNER JOIN cc_categories
    	ON cc_categories.cat_id = cc_doccat.cat_id
            )
    LEFT OUTER JOIN cc_categories AS parents
    	ON parents.cat_id = cc_categories.parent_cat
    WHERE parents.parent_cat IS NULL
    ORDER BY parents.cat_id, cc_categories.cat_id

    The problem now (and I've had it before) is that I get duplicate document types, because with CF I can only group by so many things before I get dupes. So I'm probably going to do two queries - one for the doc types and one for the categories.
    <cfset myblog = "http://cydewaze.org/">

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    So I'm probably going to do two queries - one for the doc types and one for the categories.
    that's the best way to handle a situation where there are multiple many-to-many relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy!
    <cfset myblog = "http://cydewaze.org/">


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
  •