Nesting queries in CF

I'm using this code to display a list of platforms. If a platformID was specified upon entering the page, I would like to create a list of genres underneath the specified platform.

  1. browse.cfm was accessed via a link that specified a platformID of 1
  2. browse.cfm will list all available platforms
  3. browse.cfm will now list all available genres under platformID of 1.

    <ul> <li>Browse</li> <cfoutput query="qGetPlatforms"> <li> <a href="browse.cfm?platformID=#URLEncodedFormat(Trim(qGetPlatforms.platformID))#">#qGetPlatforms.pName#</a> <cfif URL.platformID EQ qGetPlatforms.platformID> <ul> <cfoutput query="qGetGenres"> <li><a href="browse.cfm?genreID=#URLEncodedFormat(Trim(qGetGenres.genreID))#">#qGetGenres.gName#</a></li> </cfoutput> </ul> </cfif> </li> </cfoutput> </ul>

By using this approach, however, I'm getting an invalid nesting configuration. How do I fix this? Or is there another approach to achieve the same idea?

Thanks

MY queries:

<!---Get platforms---> <cffunction name="fGetPlatforms" access="public" returntype="query" output="false" hint="I get all the platforms"> <!---Local var---> <cfset qGetPlatforms = ""> <!---Database query---> <cfquery name="qGetPlatforms" datasource="#REQUEST.datasource#"> SELECT platforms.platformID, platforms.platformName AS pName FROM platforms </cfquery> <cfreturn qGetPlatforms> </cffunction> <!---Get genres---> <cffunction name="fGetGenres" access="public" returntype="query" output="false" hint="I get all the genres"> <!---Local var---> <cfset qGetGenres = ""> <!---Database query---> <cfquery name="qGetGenres" datasource="#REQUEST.datasource#"> SELECT genres.genreID, genres.genreName AS gName FROM genres </cfquery> <cfreturn qGetGenres> </cffunction>

-------------Problems Reply------------

You can use <cfloop query="qGetGenres"></cfloop>, they can be nested.

IMO, using cfoutput for looping over the queries is old style and should be avoided. Use cfoutput for output, cfloop for looping and you'll have more readable code.

more food for thought is to use an inner join between the two tables, combine and retrieve everything in one query and then use cfoutput's group attribute to display the results:

<cfset URL.platformID = int(val(URL.platformID))>

<cfquery name="getPlatformsAndGenres" datasource="#REQUEST.datasource#">
SELECT
p.platformID AS platformID
,p.platformName AS pName
,g.genreID AS genreID
,g.genreName AS gName
FROM
platforms p
INNER JOIN genres g
ON p.platformID = g.platformID
WHERE
p.platformID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.platformID#">
ORDER BY
pName
,genreName
</cfquery>

Once you have everything in one query, you can use <cfoutput query="getPlatformsAndGenres" group="pName">
to lessen your code:

<ul>
<li>Browse</li>
<cfoutput query="getPlatformsAndGenres" group="pName">
<li>
<a href="browse.cfm?platformID=#URLEncodedFormat(Trim(platformID))#">#pName#</a>
<ul>
<cfoutput>
<li><a href="browse.cfm?genreID=#URLEncodedFormat(Trim(genreID))#">#gName#</a></li>
</cfoutput>
</ul>
</cfif>
</li>
</cfoutput>
</ul>

Category:coldfusion Views:1 Time:2010-07-12

Related post

  • Join queries taking more execution time than their corresponding nested queries 2009-11-30

    I have 2 tables namely Person_Organization and Person_Organization_other and nested query is :- SELECT Person_Organization_id FROM Person_Organization_other WHERE company_name IN ( SELECT company_name from Person_Organization_other WHERE Person_Organ

  • Avoiding Nested Queries 2010-05-06

    How Important is it to avoid nested queries. I have always learnt to avoid them like a plague. But they are the most natural thing to me. When I am designing a query, the first thing I write is a nested query. Then I convert it to joins, which someti

  • Nested queries in Arel 2010-05-24

    I am attempting to nest SELECT queries in Arel and/or Active Record in Rails 3 to generate the following SQL statement. SELECT sorted.* FROM (SELECT * FROM points ORDER BY points.timestamp DESC) AS sorted GROUP BY sorted.client_id An alias for the su

  • Nested queries using Arel (Rails3) 2010-11-05

    For example, I have 2 models: Purchase (belongs_to :users) User (has_many :purchases) I want to select all users that have at least one purchase. In SQL I would write like this: SELECT * FROM `users` WHERE `id` IN (SELECT DISTINCT `buyer_id` FROM `pu

  • Linq: Nested queries are better than joins, but what if you use 2 nested queries? 2010-12-10

    In her book Entity Framework Julie Lerman recommends using nested queries in preference to joins (scroll back a couple of pages). In her example see populates 1 field this way, but what id you want to populate 2? I have an example here where I would

  • JdbcTemplate problems with nested queries 2011-09-02

    I'm trying to use nested queries with JdbcTemplate but found the issue, it seems to me that it's doesn't suppot nested queries.. Am i right? or what i need to change? So, i invoke getJdbcTemplate().query(request, new Object[]{name}...) // request is

  • Node JS, express, Mongoose, nested queries 2011-11-13

    I have a set of nested queries with express/mongoose, pretty much like so: app.get(..., function(...) { Schema1.query(..., function(..., res1) { for ( var key in res1 ) { Schema2.query(..., function(..., res2) { data[key].appendedAttribute = res2.som

  • Performans of nested queries 2012-01-30

    I want to ask a question about database queries. In case of query such like where clause of the query is coming from the another query. For example select ? from ? where ? = select ? from ? This is the simple example so it is easy to write this. But

  • Are nested queries standard in the SQL language? 2012-02-25

    I know that for example MySQL implement nested query. Is it a standard SQL feature, if such a standard exists? --------------Solutions------------- Yes, there exists an SQL standard, first formalized by ANSI in 1986, and the revised in 1989, 1992, 19

  • Creating a leaderboard (ranked list of members) in MySQL with nested queries 2012-03-24

    I'm trying to create a leaderboard of members on my site by using the two nested queries below. The first query grabs a list of members: SELECT member_id, username, screen_name FROM exp_members WHERE group_id IN (1,5) LIMIT 100 The second query is ne

  • Can I run these nested queries as one query, and how do I work with the returned information? 2012-04-12

    I've never used MySQL for anything involving many tables; when I have, I've nested queries to get the information that I need. A good example I can think of when I want to output some blog posts and their related comments. The tables could look like

  • Nesting queries in SQL 2012-09-17

    The goal of my query is to return the country name and its head of state if it's headofstate has a name starting with A, and the capital of the country has greater than 100,000 people utilizing a nested query. Here is my query: SELECT country.name as

  • Nested queries performance on MySQL vs Multiple calls. (PHP) 2013-10-24

    Is there any advantages to having nested queries instead of separating them? I'm using php to frequently query from MySQL and would like to separate them for better organization. For example: Is: $query = "SELECT words.unique_attribute FROM words LEF

  • Postgres: regex and nested queries something like Unix pipes 2009-08-24

    Command should do: Give 1 as output if the pattern "*@he.com" is on the row excluding the headings: user_id | username | email | passhash_md5 | logged_in | has_been_sent_a_moderator_message | was_last_checked_by_moderator_at_time | a_moderator ------

  • Can Django do nested queries and exclusions 2010-03-01

    I need some help putting together this query in Django. I've simplified the example here to just cut right to the point. MyModel(models.Model): created = models.DateTimeField() user = models.ForeignKey(User) data = models.BooleanField() The query I'd

  • Optimise Microsoft Access Nested Queries 2010-08-31

    I'm trying to run a set of nested or stacked queries in Microsoft Access. As in I run Query 1 -->I use the results of Query 1 in Query 2 ---->I use the results of Query 2 in Query 3 ------>I use the results of Query 3 in Query 4 Query 3 take

  • SQL Nested Queries in C# and Connection Open Problem 2011-05-19

    I have three tables and my requirement is to fetch their records by using the parent record of each table. So I have three queries. Here is a code sample: First Query SQLConnection con = new SQLConnection("connectionstring"); SQLCommand cmd1 = new SQ

  • linq to entity Contains() and nested queries 2009-06-30

    i've a trouble with linq, i'll explain on example : i have a database table called Employee which got FirstName and LastName columns, and a method to search employees which gets a nameList list as argument, the elements in this list are names formatt

  • Django: Nested Queries from Non-Nested Models 2009-09-03

    I'm trying to figure out the best way to derive a nested menu from a set of non-nested models. Given a layout something like this: class Beverage(models.Model): country = models.ForeignKey(Country,null=True,blank=True) region = models.ForeignKey(Regi

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.100 (s). 11 q(s)