Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    argentina
    Posts
    5

    Unanswered: Single large query... or a few small ones?

    Hello,
    I have a query to creates breadcrumb, on a site where each article might belong to multiple categories.

    So for each level in the breadcrumb, I must join 2 tables: one holding the breadcrumb's description (what the user sees) and the other to find the relation between each item and its parent.

    So the question is:
    1. should I build the breadcrumb into on big query, where I'd have 10 joins to get the titles of a 5 level breadcrumb?, or
    2. should I fire 5 separate queries, joining 2 tables on each and getting 1 title each query?

    Is there any information I'm missing as to get proper advice?
    Thanks for any help on this!

    -Manuel

  2. #2
    Join Date
    Oct 2003
    Posts
    706
    I don't know what your data-structure is, and generally you like to structure hierarchies so that you don't have to run a query for each level of that hierarchy. (Usually this is done by having a pointer to the root-node in each record.)

    But nonetheless, you certainly can have multiple queries to solve a problem and, as long as there aren't too many of them, it usually works just fine.

    In fact, given the choice between "a bunch of joins" and "a bunch of queries," I would definitely lean toward multiple queries. Temporary tables can be a really great thing. The more complex a query becomes, the less control you have over "what the heck the computer's gonna do."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Feb 2004
    Location
    argentina
    Posts
    5
    Hello, thanks for answering my post.

    For the parent/child relation, I have:

    CREATE TABLE `itemRel` (
    `idItem` int(4) unsigned default '0',
    `idParent` int(4) unsigned default '0',
    ) TYPE=MyISAM;

    Then there's another table holding the string (titleBcrumb) the users sees
    CREATE TABLE `category` (
    `idItem` int(4) unsigned default '0',
    [snip]
    `titleBcrumb` varchar(100) default '',
    PRIMARY KEY (`idItem`)
    ) TYPE=MyISAM;


    I'm not getting what you mean by "having a pointer to the root-node in each record". Is there any way I can extend my first table (itemRel) to avoid the multiple joins or queries?

    For example, to get a 3 level breadcrumb I currently use:
    SELECT cat1.titleBcrumb, cat2.titleBcrumb, cat3.titleBcrumb
    FROM category AS cat1
    INNER JOIN category as cat2 ON cat1.idItem <> cat2.idItem
    INNER JOIN category as cat3 ON cat2.idItem <> cat3.idItem
    INNER JOIN itemRel as itemRel1 ON cat1.idItem = itemRel1.idParent AND cat2.idItem = itemRel1.idItem
    INNER JOIN itemRel as itemRel2 ON cat2.idItem = itemRel2.idParent AND cat3.idItem = itemRel2.idItem
    WHERE cat1.titleURL = 'home' AND cat2.titleURL = 'section' AND cat3.titleURL = 'subsection'

    Thanks again!!

  4. #4
    Join Date
    Jan 2004
    Location
    Kennesaw, GA
    Posts
    10
    Or, what you could do is add a path variable to category table. For example, say you have a category called "Shoes", the path to that category could be "/Apparel/Accessories/Shoes/".

    Then, you select the Path column for the selected directory and use your cgi program to split the path into Shoes => /Apparel/Accessories/Shoes/, Accessories => /Apparel/Accessories/, and Apparel => /Apparel/ to create your bread crumb links.

  5. #5
    Join Date
    Jan 2004
    Location
    Kennesaw, GA
    Posts
    10
    I forgot to mention if you use the above, then your category management code has to remember that when you change the name of a category that it has to recursively go through all of the child categories to update their path information accordingly.

  6. #6
    Join Date
    Sep 2003
    Posts
    1
    Recently implemented crumbs in Perl, flatfile but you could do it via db queries. Would be a few fast ones.
    http://cvs.livejournal.org/browse.cg...-cvsweb-markup

    Basically your table would look like

    id
    name
    url
    parent

Posting Permissions

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