0

In CF I am trying to do a QoQ where the rows are in a list of other rows. Basically moving some code from cftags to cfscript (Not important why). In tags we have a main query and we have several nests that do some heavy lifting. I am moving this to cfscript and have the following syntax that is working:

var submissionList = new Query(dbtype="query", QoQsrcTable=ARGUMENTS.answers, sql="
SELECT submission_id FROM QoQsrcTable GROUP BY submission_id
").execute().getResult();
var submissions = new Query(dbtype="query", QoQsrcTable=ARGUMENTS.answers, sql="
    SELECT * FROM QoQsrcTable WHERE submission_id IN (#submissionList.submission_id#)
").execute().getResult();

I have tried the following but it fails to work:

var submissions = new Query(dbtype="query", QoQsrcTable=ARGUMENTS.answers, sql="
    SELECT * FROM QoQsrcTable WHERE submission_id IN (SELECT submission_id FROM QoQsrcTable GROUP BY submission_id)
").execute().getResult();

I think the second example should work. I've tried messing with it in various ways. But can't seem to figure out what I am doing wrong. Maybe a nested QoQ doesn't work like that. Is there another way I can accomplish what I am trying without two chunks of code? Just so it's more readable and I don't have to assign variables twice.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Leeish
  • 5,203
  • 2
  • 17
  • 45
  • 2
    I cannot stick around, but it would help to clarify what you mean by *fails to work*. An error occurs, zero results, ..? – Leigh Aug 29 '12 at 22:47
  • Error. Gives me something similar to "Syntax error at "submission_id IN (SELECT submissions_id" expected condition [like|in|between]" blah blah – Leeish Aug 29 '12 at 22:50
  • 1
    can you post the full error message? – Matt Busche Aug 29 '12 at 22:57
  • Either you're not showing us everything, or you've got a convoluted set of code that does `SELECT TOP 1 * FROM Arguments.answers` ? – Peter Boughton Aug 29 '12 at 23:03
  • Your query makes no sense! You're selecting all the entries in the Answers table. Then you're selecting all the entries from the Answers table where the submission_id is in the list you just selected. So you're getting everything a second time? Or am I misunderstanding? – duncan Aug 30 '12 at 08:08
  • Following from Duncan, if this is your actual code, then it's not the best way of accomplishing your task. Could you clarify this point please. – Dpolehonski Aug 31 '12 at 16:05

2 Answers2

3

QoQ doesn't support subqueries. That's the long and the short of it.

Docs

Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • 2
    As Adam said, QoQ doesn't support sub queries but you can use in (#valuelist(someOtherQuery.columnName)#) – genericHCU Aug 30 '12 at 11:15
  • I just used @Travis' idea today. Break out the subquery into it's own QoQ, save the values with `` then I used `where xxx in (#PreserveSingleQuotes(myList)#)` – djailer Sep 13 '12 at 04:59
  • @djailer - You should not need `PreserveSingleQuotes`. Just use `cfqueryparam` with the `list` attribute. – Leigh Sep 27 '12 at 21:20
0

In Coldfusion 10 or Railo 4, you can utilize the groupBy function of Underscore.cfc to accomplish what you want in much less code:

_ = new Underscore();// instantiate the library    

submissions = _.groupBy(arguments.answers, 'submission_id');

groupBy() returns a structure where the keys are the values of the group element (in this case, submission_id).

(Disclaimer: I wrote Underscore.cfc)

Russ
  • 1,931
  • 1
  • 14
  • 15