Using parameters with ormExecuteQuery and Railo
Apr 8
posted by Brian in
ORM, Railo, ColdFusion
| 0 Comments
Though the new ORM functions in Railo are great there is often times you need just a little more than matching one or two columns exactly when you're getting data. When you reach this point it's time to bring in ormExecuteQuery as a method of gaining that little bit more of control over getting your data. Though there is more to ormExecuteQuery but I want to discuss how to use the conditional parameters of your where clause.
Though you could just insert the parameters directly into your query, we all know that that is not safe and is how SQL Injection attacks occur. In fact I am not even going to show that as we don't even want you to think about doing it that way, and since cfqueryparam cannot be used here we need to find another way to keep our system secure but get the data we need.
ormExecuteQuery
The basic ormExecuteQuery() would be kind of silly to use a but I am showing it just as an example.
The above is obviously basic and would be no different from using EntityLoad('ledger') so let's add some parameters to the query to make it more specific. There are two way to use parameters with Railo and ColdFusion and below we will go over how to use them and why you may use one over the other.
String Replacement
The string replacement method works by replacing all the ? in your query in order by the array of parameters passed in. So let's dive right in a see what our query will look like using our example above expanded.
2var test = ormExecuteQuery("from ledger where transactionAmount = ?",['20.00']);
3// select * from ledger where transactionAmount = '20.00'
4//Two params
5var test = ormExecuteQuery("from ledger where transactionAmount = ? or transactionAmount = ?",['20.00','10.00']);
6//select * from ledger where transactionAmount = '20.00' or transactionAmount = '10.00'
Pretty simple right? Yes, it is and it keeps your queries safe and we get the data we need. Though there may be times when it would be easier to form the query if we didn't need our parameters in a specific order or we could actually use names instead.
Named Parameters
The second method we are going to go over is using named parameters or defining them as :param vs using a ?. The advantage I feel we have with this method is putting together your query can be easier if you can use names instead of ? as part of your where clause. Just think of some large queries where you have several parameters passed in. using ? could get messy and make things more complicated. Now let's look at using them in a query.
2var test = ormExecuteQuery("from ledger where transactionAmount = :amount",{amount='20.00'});
3// select * from ledger where transactionAmount = '20.00'
4//Two params
5var test = ormExecuteQuery("from ledger where transactionAmount = :amountOne or transactionAmount = :amountTwo",{amountOne='20.00',amountTwo='10.00'});
6//select * from ledger where transactionAmount = '20.00' or transactionAmount = '10.00'
As you can see with this method with a lot of parameters you will have an easier time keeping your values straight. The other advantage you have is you can build your paramter struct outside of your query and just pass your struct in and you have your parameters.
2var test = ormExecuteQuery("from ledger where transactionAmount = :amountOne or transactionAmount = :amountTwo",amounts);
3//select * from ledger where transactionAmount = '20.00' or transactionAmount = '10.00'
That is where you can see the advantages of using named parameters!
Closing Notes
OrmExecuteQuery returns an array of your models as objects. If you like this your all set but if you would like to have that returned as a query so you can use QueryofQueries or just to go through the data you can use EntityToQuery(orm_object); to convert it to a query
2 var test = ormExecuteQuery("from ledger where transactionAmount = :amountOne or transactionAmount = :amountTwo",amounts);
3 //select * from ledger where transactionAmount = '20.00' or transactionAmount = '10.00'
4 query = EntityToQuery(test);
OrmExecuteQuery gives you all the flexibility of writing your own queries with the advantages of leveraging the ORM features or ColdFustion and Railo.