ColdFusion CFScript Query of Queries Example

This post is more for me than anything else...but I couldn't find a decent example of how to write a query of queries in ColdFusion's CFScript syntax. Below you'll see two queries; the first one is a simple query using ColdFusion's auto-generated dsn, the second one narrows the result set just a bit.

<cfscript>
    // Basic Query Syntax
    q1 = new Query();
    q1.setDatasource('cfartgallery');
    q1.setSQL('select * from artists');
    rs1 = q1.execute().getResult();
    WriteDump(var=rs1,label='RS1');
    
    // Query of Queries
    q2 = new Query();
    q2.setDBType('query');
    q2.setAttributes(rs=rs1); // needed for QoQ
    q2.addParam(name='state', value='CO', cfsqltype='cf_sql_varchar');
    q2.setSQL('SELECT * FROM rs where state = :state');
    q2.setMaxRows(2); // limit max rows, if desired
    rs2 = q2.execute().getResult();
    WriteDump(var=rs2,label='RS2');
</cfscript>

This should produce something similar to the following output:

Hope this helps...cheers!

Removing index.cfm From Mura CMS URLs on Windows/IIS7

If you have an installation of Mura CMS on Windows running IIS7+ and you're looking to remove "index.cfm" from the URL for search engine optimization (SEO) or any other reason, then here's a pretty simple and painless method to do just that.

First, IIS7 does not have their URL Rewrite Module installed by default. So you'll need to download and install URL Rewrite Module 2.0 for your server (x86 / x64).

After that's done, your next decision is to choose whether or not you wish to have the SiteID display in the URL. Some users prefer to have that, and some don't depending on their particular usage of Mura.

Option 1: Allow For Missing SiteID AND index.cfm From The URL

Create a "web.config" file with the following code and place it at the root of your Mura installation:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <rewrite>
            <rules>
                <rule name="Mura CMS Rewrite Option 1" enabled="true">
                    <match url="^([a-zA-Z0-9/-]+)$" ignoreCase="false" />
                    <conditions logicalGrouping="MatchAll">
                        <add input="{DOCUMENT_ROOT}{URL}" matchType="IsDirectory" ignoreCase="false" negate="true" />
                    </conditions>
                    <action type="Rewrite" url="/index.cfm{URL}" />
                </rule>
            </rules>
        </rewrite>
        <defaultDocument>
            <files>
                <remove value="index.cfm" />
                <add value="index.cfm" />
            </files>
        </defaultDocument>
    </system.webServer>
</configuration>

Then, update your settings.ini.cfm file with the following settings and from the Admin > Reload Application:

siteidinurls=0
indexfileinurls=0

Option 2: Allow For Missing index.cfm From The URL, But Keep SiteID

Create a "web.config" file with the following code and place it at the root of your Mura installation:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <rewrite>
            <rules>
                <rule name="Mura CMS Rewrite Option 2" enabled="true">
                    <match url="^([a-zA-Z0-9-]{1,})/([a-zA-Z0-9/-]+)$" ignoreCase="false" />
                    <conditions logicalGrouping="MatchAll">
                        <add input="{DOCUMENT_ROOT}{URL}" matchType="IsDirectory" ignoreCase="false" negate="true" />
                    </conditions>
                    <action type="Rewrite" url="/{R:1}/index.cfm/{R:2}" />
                </rule>
            </rules>
        </rewrite>
        <defaultDocument>
            <files>
                <remove value="index.cfm" />
                <add value="index.cfm" />
            </files>
        </defaultDocument>
    </system.webServer>
</configuration>

Then, update your settings.ini.cfm file with the following settings and from the Admin > Reload Application:

siteidinurls=1
indexfileinurls=0

Your site should now be able to handle URLs that are missing index.cfm.

Cheers!

How to Create Dynamic Content Display Objects in Mura CMS with MuraFW1 Plugins

If you've ever created a MuraFW1 plugin for Mura CMS (which this article assumes by the way!), you may have wondered how to go about creating dynamic Content Display Objects. Doing so would enable an end-user to edit a page, go to the Content Objects tab, select Plugins from the Available Content Objects dropdown, select your plugin, then select your "dynamic display object" which would then fire off and return yet form field containing the dynamic list of options available to be assigned to the desired content display region.

Still with me? Great!

Ultimately, there's three main parts to this:

1. Create the display method, i.e., myDisplay

2. Then create an "OptionsRender" display method which follows the naming convention of myDisplayOptionsRender

3. Finally, we'll add the basic display method (i.e., myDisplay) to the config.xml.cfm's list of available displayObjects

1. Create The Display Method

In the pluginEventHandler.cfc, we can add our basic display method. Ultimately, we should eventually expect a JSON formatted variable to be passed in via $.event('params'). Obviously, we'll need to deserialize the JSON, and once we do that, we can do whatever we like with it. An easy way to pass this information over to FW/1 is to stuff any desired variables into the URL scope ... and then call the typical renderApp() method and let FW/1 take over from there. Check out our simple example below which is expecting to find "whateverID" to be in the unpacked params:

<cffunction name="dspWhatever" output="false" returntype="any">
    <cfargument name="$" hint="mura scope" />
    <cfscript>
        var local = {};

        // grab everything that's been passed in from OptionsRender() and unpack the JSON option values that were saved
        local.params = DeserializeJSON($.event('params'));

        // stuff selected WhateverID into the url scope so that fw1 will pick it up
        if ( StructKeyExists(local.params, 'whateverid') ) {
            url.whateverid = local.params.whateverid;
        };

        // something went wrong ... so let's not bomb everything
        if ( not StructKeyExists(url, 'whateverid') ) {
            return '';
        } else {
            return renderApp($,'public:main.default');
        };
    
</cfscript>
</cffunction>

2. Create An "OptionsRender" Method

Now for the magical part! Whatever you named your displayMethod, you simply append "OptionsRender" to the end of it. So if you have dspWidgets, then you would have dspWidgetsOptionsRender as an available method. In the example below, I'm creating a simple recordset which we'll then loop over to output the options and dynamically generate the option value which is a tilde (~) separated list where the last item in the list is a JSON formatted list of params...yes, the very same params our display method in step one is expecting to find!

<cffunction name="dspWhateverOptionsRender" output="false" returntype="any">
    <cfargument name="$" />
    <cfscript>
        var local = {};
        
        // this recordset could come from anywhere ... doesn't even have to be a recordset, could be anything that contains data that you want to grab options from
        local.rs = QueryNew('WhateverID,WhateverTitle,WhateverField', 'VarChar,VarChar,VarChar');

        QueryAddRow(local.rs, 1);
        QuerySetCell(local.rs,'WhateverID', 'anyid-1', 1);
        QuerySetCell(local.rs,'WhateverTitle', 'My Awesome Title', 1);
        QuerySetCell(local.rs, 'WhateverField', 'Hi Ho Cherry-O', 1);

        QueryAddRow(local.rs, 1);
        QuerySetCell(local.rs, 'WhateverID', 'anyid-2', 2);
        QuerySetCell(local.rs, 'WhateverTitle', 'Another Great Title', 2);
        QuerySetCell(local.rs, 'WhateverField', 'Knick Knack Paddy Whack', 2);
    
</cfscript>
    <cfsavecontent variable="local.str">
        <cfif local.rs.recordcount>
            <select name="availableObjects" id="availableObjects" class="dropdown">
                <!--- we're going to pack everything up into a tilde (~) separated list where the last item in the is a JSON formatted list of params to be passed over to the display method --->
                <cfoutput query="local.rs">
                    <option value="plugin~#HTMLEditFormat(Replace(WhateverTitle,'~','','ALL'))#~#$.event('objectid')#~{'whateverid':'#WhateverID#','anotherfield':'#WhateverField#'}">
                        #HTMLEditFormat(WhateverTitle)#
                    </option>
                </cfoutput>
            </select>
        <cfelse>
            <p><em>Please create some Whatevers first.</em></p>
        </cfif>
    </cfsavecontent>
    <cfreturn local.str />
</cffunction>

3. Add The Rendering Method To config.xml.cfm

The final step in all this is to add your basic displayMethod to your config.xml.cfm file so that it will be registered with Mura CMS. Once it is, then we should expect to see it show up in the Content Objects tab when someone selects our plugin.

<displayobjects location="global">
    <displayobject name="Display Whatever" displaymethod="dspWhatever" component="pluginEventHandler" persist="false" />
</displayobjects>

That's it! Hopefully this helps anyone else who is creating FW/1 plugins for Mura CMS.

Cheers!

More Entries

© 2024, Stephen J. Withington, Jr.  |  Hosted by Hostek.com

Creative Commons License   |   This work is licensed under a Creative Commons Attribution 3.0 Unported License.