Skip to main content
A
Anonymous
September 10, 2009
Answered

Self-referencing query

  • September 10, 2009
  • 2 replies
  • 1309 views

Hello~

This is probably a really simple question, but here is the query I am trying to run, with no success:

          <cfquery name="updatePage" datasource="#application.db#">
                UPDATE
                    admin_nav_test
                SET
                    lft =
                        <cfif lft GT parentLevel>
                            lft + 2
                        <cfelse>
                            lft
                        </cfif>
                WHERE
                    rght >= #parentLevel#
            </cfquery>

Basically, I want to reference the current value of lft in the table I am querying within the query itself, if that makes sense. However, all I end up with is an error that says "Variable LFT is undefined." I have tried using admin_nav_test.lft, and every other combo I can think of to self-reference this variable, but no luck! What am I missing? Thanks!

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    UPDATE admin_nav_test
    SET lft = lft + 2

    where lft > parentlevel

    and rght = #parentlevel#

    2 replies

    D
    Dan_BracukCorrect answer
    Inspiring
    September 10, 2009

    UPDATE admin_nav_test
    SET lft = lft + 2

    where lft > parentlevel

    and rght = #parentlevel#

    A
    Anonymous
    September 10, 2009

    This is perfect! Thank you so much. It looks so simple now, but I have been banging my head against this for hours. Thanks again!

    For anyone else's reference, these are the two queries I came up with for updating the left and right values before adding a new page to a nested set:

              <cfquery name="updateLeftValues" datasource="#application.db#">
                    UPDATE
                        admin_nav_test
                    SET
                        lft = lft + 2
                    WHERE
                        lft > #parentlevel#
                        AND rght >= #parentlevel#
                </cfquery>
               
                <cfquery name="updateRightValues" datasource="#application.db#">
                    UPDATE
                        admin_nav_test
                    SET
                        rght = rght + 2
                    WHERE
                        rght >= #parentlevel#
                </cfquery>

    A
    Adam Cameron.
    Inspiring
    September 10, 2009

    I think you're probably reinventing the wheel unnecessarily here: have a look @ http://nstree.riaforge.org/

    If you roll your own code - as part of a learning exercise perhaps - then make sure to transactionalise those queries, because you want both those updates to run as an atom.  Consider what would happen to your hierarchy data a second ADD operation starts being processed whilst your first one is still running.  So instead of this:

    (FIRST ADD) UPDATE LEFT

    (FIRST ADD) UPDATE RIGHT

    (SECOND ADD) UPDATE LEFT

    (SECOND ADD) UPDATE RIGHT

    You ended up with this:

    (FIRST ADD) UPDATE LEFT

    (SECOND ADD) UPDATE LEFT

    (FIRST ADD) UPDATE RIGHT

    (SECOND ADD) UPDATE RIGHT

    This will stuff your tree up.

    I also recommend you make a generic function to pad the tree by a specified amount, rather than hardcoding "2".  When you come to want to be moving or deleting whole branches, the amount you will need to shift things will not necessarily be 2, but the operation will be the same other than the offset amount.  So you might as well factor it out into a separate function, and use that for all occasions.

    Even if you decide to roll your own solution (it is a good exercise), at least eyeball the stuff on RIAForge to see how it's done, and possibly flag some considerations that might not be immediately apparent.  I rolled my own solution for this - before the RIAForge implementation was done - and it took a lot of wailing and gnashing of teeth to get it right.  And unfortunately some of the bugs didn't get noticed until the code was in production.  Which caused... "problems".

    --

    Adam

    ilssac
    ilssac
    Inspiring
    September 10, 2009

    I think you are missing that CFML runs in a ColdFusion Applicaiton Server and SQL runs in a Database Server.

    It is a powerful tool to use CFML to build the SQL that is then sent to the databse server.  But once sent the database server is going to have no idea what to do with any CFML you send it.

    To do what you want in the database is going to require the SQL equavalents of the if/else structure and would probably be a more natural fit to a stored procedure or database function then a straight up query.

    A
    Anonymous
    September 10, 2009

    OK, that does make sense. Is there any way to make what I have work as an interim solution, and work out the stored procedures at a later date?

    ilssac
    ilssac
    Inspiring
    September 10, 2009

    I would need to know quiet a bit more about the data and what you are trying to do, I have NO idea what lft and parentLevel are about or what they mean or what you want to do with them.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices