http://coldfusion.com logo
Join Slack
Powered by
Exploring some things. Say I have a component with...
# lucee
d
Exploring some things. Say I have a component with a bunch of different methods that all have different SQL queries but all use the same “team” query param. I want them all to share the same params so I don’t have to copy and past a bunch of times to each method. I would think something like this at the top of the component might work…
Copy code
variables.params = [
		{
			'name'      : 'team',
			'value'     : team,
			'cfsqltype' : 'cf_sql_varchar'
		}
	];
I could then just use variables.params in the queryExecute in each method. But at that point in the component above, the 
team
variable doesn’t exist yet. It only exists as it gets passed into each method. Any idea how I would do this?
w
does the team variable value change between the methods?
d
Yeah, it would be different all the time.
w
and the goal is to simply avoid having to declare the params, which are all the same names/types, in each method?
d
Yeah, basically. Simplified, but yes. I know I could do this with one common method that holds the params, gets the SQL string from one of the methods, then executes the query. I just want to explore this way a bit.
w
i'd be inclined to create a new method, getCommonParams( argumentcollection=arguments ) that returns an array of param structs. gives you way more flexibility to modify the resulting params than just a stale old variables scoped variable
you could pass in the existing params array and have the common ones appended to it, etc
is it just the one team param or are there many
you may also find using new Query() syntax gives you options for chaining that queryexecute doesn't, assuming you're not averse to that syntax
d
I'm fairly averse to that syntax. 😀 There are several params, less than 5ish though. Yeah, the getCommonParams() is a good idea. I guess with that, there still really isn't a way to not pass anything into that method and just get the common params array back if there are variables hanging around in it (like team), right? Which might be a good indicator that this is a weird direction. 🤔
w
yeah, it's a bit odd but i'm sure you have your reasons. if there were fifty params then i could get behind trying to reduce hardcoding, but given how INfrequently data access methods actually change i would have probably just propagated the 'common' params into all the methods and have been done with it. doing so will make it easier for others that come after you to intuit what the code is doing, without having to chase through other methods/variables to know what's ultimately going on in each query
unless you hate your coworkers as much as you do Query() object syntax, that is
😂 1
d
There are like 20 different methods, so just trying to avoid the 20x5 param lines in a not too cute way. The common runAllTheThings() method that has the params in it and just gets the SQL from one of the other 20 methods is probably where I'm leaning.
w
sounds like you're going to end up with some variation of this:
Copy code
/**
	 * Creates a new resource from a resource bean
	 * 
	 * @bean any  					a resource bean
	 *
	 * @return struct 				{ "id" : newid }
	 * 
	 */
    public struct function create(
        required any bean
    ) {        

		var q = "";
        var qr = "";
        var sql = bean.buildSql("insert","schools");

        q = queryExecute(
            sql.statement,
            sql.parameters,            
            { datasource : getDsn(), result : "qr" }
        );

        doDebug( qr, "create", getDebugFilePath() );

        if( !qr.recordcount ) {
            throw( type="ResourceCreationFailed", message="The resource could not be created", errorcode="500" );
        }

        return { "id" : qr.GENERATEDKEY };

    }
where the bean in question has like fifty properties
but it's pretty well stripped of any meaningful info were you to look at the method alone
i can paste the bean's buildSql method if you're interested in more inspiration
d
Sure, paste away of you don't mind, it won't hurt.
w
i had an integration where i wanted all the db operations to be bean-driven, so a bean has properties that effectively align with the db table columns, with some additional metadata attributes, e.g.:
Copy code
property name="sid" type="numeric" sqltype="cf_sql_integer" pk="true";
    property name="schoolType" type="string" sqltype="cf_sql_varchar" maxlength="40";
    property name="schoolSector" type="string" sqltype="cf_sql_varchar" maxlength="20";
    property name="schoolName" type="string" sqltype="cf_sql_varchar" maxlength="255" required="true";
then you could use the following function inside the bean to ask the bean to return the sql necessary to execute:
Copy code
/**
     * Returns the various parts of sql queries so a DAO can execute them
     *
     * @operation string                    insert | update |d delete
     * @tablename string                    the tablename the query targets
     * 
     * @retrun struct 
     */
    public struct function buildSql( required string operation, required string tablename ) {

        // validate requested sql operation
        if( !listfindnocase("insert,update,delete", arguments.operation) ) {
            throw( type="InvalidOperation", message="The requested operation is not supported" );
        }

        var sql = {
            "statement" : "", 
            "parameters" : {}
        };

        var properties = getMetadata(this).properties; 
        
        // set any getdate()-like timestamp values we may have
        setTimestamps( properties, arguments.operation );

        var populated = getProperties(false); // returns only those properties whose values have been set
        // filter properties down to only those that are populated
        var workingProps = arrayfilter( properties, function( prop ) {
            return structkeyexists(populated,prop.name);
        });

        switch ( arguments.operation ) {

            case "insert":

                var sqlColumns = [];
                var sqlValues = [];
                var params = {};

                for( var prop in workingProps ) {
                    arrayappend( sqlColumns, prop.name );
                    arrayappend( sqlValues, ":" & prop.name );
                    params[prop.name] = { cfsqltype : prop.sqltype, value : getProperty(prop.name), null : sqlNullifier( prop.type, getProperty(prop.name) ) };
                }

                sql.statement = "INSERT INTO " & arguments.tablename & "(" & arraytolist(sqlColumns) & ") VALUES (" & arraytolist(sqlValues) & ")";
                sql.parameters = params;

                break;

            case "update":

                var sqlColumns = [];
                var params = {};
                var identifier = getIdentifier( properties );

                for( var prop in workingProps ) { 
                    if( prop.name != identifier.column ) {
                        arrayappend( sqlColumns, prop.name & " = :" & prop.name );
                    }
                    params[prop.name] = { cfsqltype : prop.sqltype, value : getProperty(prop.name), null : sqlNullifier( prop.type, getProperty(prop.name) ) };
                }

                sql.statement = "UPDATE " & arguments.tablename & " SET " & arraytolist(sqlColumns) & " WHERE " & identifier.column & " = :" & identifier.column; 
                sql.parameters = params; 
                
                break; 


            case "delete":

                var params = {};
                var identifier = getIdentifier( properties );

                for( var prop in workingProps ) { 
                    if( prop.name == identifier.column ) { // we only need the PK column
                        params[prop.name] = { cfsqltype : prop.sqltype, value : getProperty(prop.name) };
                        break;
                    }
                }

                sql.statement = "DELETE FROM " & arguments.tablename & " WHERE " & identifier.column & " = :" & identifier.column; 
                sql.parameters = params; 
                
                break; 

            default:
                throw( type="InvalidOperation", message="The requested operation is not supported" );
                
        }

        return sql;
    
    }
worked fine, but tbh it got a little too ORM like for my tastes. but the goal was achieved: everything required to generate the sql parts was all bean-driven
i still have a lot of reservations about the approach which i stated before, but the payoff was i could just create a city bean, or a person bean, or whatever, and throw them at the same basic DAO operations. not sure if it was worth it, but there's some stuff for you to dissect
2 Views
Open in Slack
PreviousNext
Powered by