Skip to main content
R
Robin James
Inspiring
April 16, 2013
Answered

Query of Search Results

  • April 16, 2013
  • 1 reply
  • 2969 views

I need some best practices advice.  I'm not sure if there is a better way to do what I'm trying to accomplish.  The execution time is very high which is why I'm looking for alternatives.  I welcome any tips to help reduce this execution time.  Hopefully, I can explain this clearly because I'm not seeing it clearly.  I'm going to just use one form field for this example, however, there are about 10 search field options.

Multiple items (checkboxes) can be selected for one field.  I am going to use CODES for my example.

The cfquery on the results page uses a view that has been created in the database.  This view joins a primary table (company) to several other tables (i.e., codes) that possibly have many records for that one company.  Within the cfquery, I use IN for the joined tables just to see if that company has any of those records.  It appears to be working and not taking too long.  In order to output the results, a  distinct company is being used because multiple rows could be returned for one company, and I only want to output each company one time. 

Then, I want to output the company information.  I also want to output the multiple codes for returned for each company.  So, I have to do a little query within the main query output to get those records.  Because there are so many search field options, this is where I believe the execution time is slowing down because is has to loop very every company returned (800+) and multiple search fields means different queries.  Below is a sample piece of the code.

I am thankful and open to any ideas.  Sincerely.

<cfquery datasource="xxx" name="Company">

Select distinct company

from vwCompany

where active='true'

cfif isdefined("code") and code neq ''> and code IN (<cfqueryparam value="#form.code#" list="yes">)</cfif>

</cfquery> 

<cfoutput query="company">

<CFQUERY DATASOURCE="xxx" NAME="getCodes">

    SELECT     *

    FROM     tblCodes

    WHERE code IN (<cfqueryparam value="#form.code#">)

</CFQUERY>

#company#

codes: #valuelist(getcodes.code)#

</cfquery>

    This topic has been closed for replies.
    Correct answer meensi

    There is a select distinct in it, but it still returns those companies multiple times if there have more than one code.

    <cfquery datasource="xxx" name="Company">

    Select distinct company, codes

    from vwCompany

    where active='true'

    cfif isdefined("code") and code neq ''> and code IN (<cfqueryparam value="#form.code#" list="yes">)</cfif>

    </cfquery> 


    <cfquery datasource="xxx" name="Company">

    Select distinct company, codes

    from vwCompany

    where active='true'

    cfif isdefined("code") and code neq ''> and code IN (<cfqueryparam value="#form.code#" list="yes">)</cfif>

    </cfquery> 

    from this you can take distinct compnay and get the codes accordingly.

    <cfquery dbtype="query" name = "getDisCompany">

         select distinct company from company

    </cfquery>

    <cfloop from =1 to =#getDiscompany.recordcount# index = i>

        

    <cfquery dbtype="query" name = "getDisCodes">

         select distinct codes from company where company = #getDisCompany.Company#

    </cfquery>

    </cfloop>

    1 reply

    M
    meensi
    Inspiring
    April 17, 2013

    You can try to join the queries and get the result as one query.

    Using queries of query, you can display the result for each company.

    Your first query will return all the results. Second query will use the first query(queries of query) and get the results for each company.

    R
    Robin JamesAuthor
    Inspiring
    April 17, 2013

    I've recently been coming across "query of queries," but I'm unfamiliar with it.

    I will research it.  Thank you so much.

    R
    Robin JamesAuthor
    Inspiring
    April 17, 2013

    Query of queries is a great tool to have.

    As I'm testing it, I've become stumped.

    How do I avoid having the query of query inside a loop to get the records to output for each company?  Am I doing this wrong?

    Using the same query as above (primary query)

    <cfloop query="companies">

    <cfquery dbtype="query" name="codes">

    Select codes

    from company

    where companyid= <cfqueryparam value="#company.companyid#" cfsqltype="cf_sql_integer">

    </cfquery>

    <cfoutput>#company# #valuelist(codes.code)#

    </cfloop>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices