CODEWITHSUNDEEP
selectcoldfusionresultset
frequent
frequent

Reputation: 28513

how to select a specific record from a query in Coldfusion?

I'm trying to optimize a product search to be used on mobile devices using Coldfusion8/MySQL.

Right now the following happens:

1. user searches for "FOO"
2. Q1: search database for no of results
3. Q2: search again to get actual data for results FROM TO depending on pagination
4. user clicks details for a product
5. redo Q1 to get no of results
6. redo Q2 to get single result
7. use single result to query for product details (sizes, colors, pricings...)

I'm looking for a way to skip redoing Q1 and Q2, because the user picked a result from the resultset, so why should I rebuild the resultset again just to get a single result.

Question:
I guess the way would be to pass a variable indicating I'm only needing details of a record in an exinsting query. Say I have results 1-20 and I pass a variable "DETAIL" and an articlenumber "123", how would I select the matching record from an existing resultset = how can I select a specific record from a resultset?uery

EDIT: So I think the only way for my case is to run the query the first time, store the results in Session.storage and then pick the respective record from the storage vs. re-running my initial search.

Query of Queries does not work, because my initial query is inside an cfinvoke method, so when it's done, it's gone and on the next pass through I cannot query the results of the last cfinvoke.

I also tried to store into my cfc variables scope. Also does not work, I guess for the same reason. I don't want to start using cfobject, because I'm still running CF8 and I'm reading a lot on how cfobject hampers performance.

Anyway. On my first query called "results", I'm now storing

<cfset Session.storate = results>

When I dump session storage I get:

catch - query
    CACHED: false 
    EXECUTIONTIME: 2031 
    SQL: SELECT a.*, p.ek, p.vk, p.x, p.y
        FROM arts a
    LEFT JOIN p ON 
            ...
            LEFT JOIN f ON 
            ... 
            WHERE a.aktiv = "ja"
            AND 
            ... 20 conditions ...

    SQLPARAMETERS: [array]
    1) %402% 
    ... 20 parameters

    RESULTSET: 
     [Record # 1] 
        a: true
        style: 402
        price: 2.3
        currency: CHF
        ...
     [Record # 2] 
        a: true
        style: 402abc
        ...

My question would now be:

How can loop over session.storage and check for style? If found, this is my result, which I will build the detail view from.

Upvotes: 2

Views: 1461

Answers (3)

Phillip Senn
Phillip Senn

Reputation: 47635

Another thing to consider is cachedwithin attribute.

Upvotes: 0

Dave Ferguson
Dave Ferguson

Reputation: 773

I am not sure I understand why you would redo the search to get a single record. If the user clicked on a details link that link should have some identifier back to the item selected. I would then use that id to query the database and get product detail. The original search query should only return relavent data to the results display.

Upvotes: 2

Matt Busche
Matt Busche

Reputation: 14333

I'm not sure if I follow, but if I am you could do a Query of Queries, which allows you query an existing query that ran on your page.

<cfquery name="getOneRecord" dbtype="query">
SELECT record
FROM originalqueryname
WHERE articlenumber = 123
</cfquery>

If you only need to retrieve one result this is probably the fastest way.

Upvotes: 4

Related Questions