1

I have an application where almost everything is dynamic. I am creating an edit form for a user and essentially need to search a query to select a group of checkboxes.

I have a table assigning the user to programs that holds userid and programid which maps to the corresponding records in the users table and the programs table. Initially I grab one user and all the programs and I loop over the programs query to build the checkboxes.

<cfloop query="Rc.programs">
    <dd><input type="checkbox" name="programs" value="#Rc.programs.id#" /> #Rc.programs.name#</dd>
</cfloop>

What I ideally want to do is pull all records in the program memberships table and do some sort of search through that. I could do a query of queries, but I was wondering if there was a faster way to essentially search a query. My query of queries would be like the following if this helps people understand.

SELECT * FROM Rc.programs WHERE programid = #Rc.programs.id#

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Dave Long
  • 9,569
  • 14
  • 59
  • 89

1 Answers1

4

QoQ is certainly the easiest way to do it, but don't forget your CFQUERYPARAM:

SELECT * FROM Rc.programs WHERE programid =
   <cfqueryparam value="#Rc.programs.id#" cfsqltype="WHATEVER_IT_IS">

You can also reference an individual column/field of a query as an array, and search through just that column using array functions, including arrayFind() (which might just be in recent versions).

arrayFind( Rc.programs.programId, YOUR_ID_HERE )

If that's not fast enough you could always build some sort of data structure or index in memory, and keep it around in an Application-scope variable if such is appropriate.

But is your database really that slow? Reducing the number of queries executed by a page is almost always a good thing, but for average, uncomplicated queries you probably won't be able to beat the speed, caching, etc of your DB server.

bpanulla
  • 2,988
  • 1
  • 19
  • 23
  • It isn't that my database is slow, or even that their will be that many users. I really was just wondering how other people would do it. I will probably just use a QoQ. My issue with querying the database over and over is that the table will have roughly 50,000 records in it. – Dave Long Feb 22 '11 at 16:32
  • `cfqueryparam` is not needed for a query of queries since you are not actually hitting the database - you are hitting a recordset for results - therefore there is no danger of SQL injection, nor any performance gain AFAIK. It is a good habit though. More info here: http://cfsilence.com/blog/client/index.cfm/2006/10/31/Query-of-Queries-Ignores-cfqueryparam – Ciaran Archer Feb 22 '11 at 16:35
  • I am in the habit of using cfqueryparam and usually [controversially] leave it out on forum posts for ease of typing... unless my error is in regards to an actual query throwing an error. – Dave Long Feb 22 '11 at 17:11
  • While 50,000 rows isn't a lot of data for any RDBMS (particularly with table indexes on the right fields) that might be a lot of data to keep around in memory or even pull across the network from the DB server (might be many MB depending on the schema). It may seem strange, but in that case many small queries to the highly-indexed database may be faster or kinder on your server resources than the QoQ. – bpanulla Feb 22 '11 at 18:54