CFQuery with an IN statement (how do you do it)

Report · Nov 08, 2007

I have a <cfquery> SQL statement with a 'where in ( )' clause but quotedValueList can't handle values greater than 1000, so, how do you do it?

Now, I can't just stick the first query into the second query to make it work. The first query is more complicated than the example I gave so that's why I have to break them up. So, how would I make the second query work if the first query returned 2000 students?

<cfquery name="getStudents" datasource="Oracle">
select pk1
from users
group by pk1
</cfquery>

<cfquery name="getStudentCourses" datasource="Oracle">
select course_id
from course_users
where users_pk1 in (......)
</cfquery>

Report · Nov 08, 2007

quote:

I can't just stick the first query into the second query to make it work. The first query is more complicated than the example I gave so that's why I have to break them up.

You know, I have written some queries with very complicated sub-queries that worked just fine, so I have doubts that you can't do it. Post your queries and maybe someone could give it a shot. Other options are putting it into a stored procedure, and since you are using Oracle, there is LOTS that you can do with PL/SQL, trust me. The least desireable option would be to loop through the output of the first query to drive the second withing ColdFusion. 2000 database hits on your page is not very efficient.

Phil

Report · Nov 08, 2007

Thanks for the reply and, yes, I understand what you are saying.

I was looking for some CF tag but I take it there isn't anything that will work? It seems quotedValueList and some of the other tags are limited by 1000 records.

I even tried doing where pk1 in ('#getStudents.pk1#') but that just gave me one record back.

The statement was:

<cfquery name="getStudentCourses" datasource="Oracle">
select course_id
from course_users
where users_pk1 in (#getStudents.pk1#)
</cfquery>

Report · Nov 08, 2007

<cfquery name="getStudentCourses" datasource="Oracle">
SELECT cu.course_id
FROM course_users cu
WHERE EXISTS(SELECT 1
FROM users u
WHERE u.pk1 = cu.users_pk1)
</cfquery>

Of course, your sub-query is probably much more complex....

Phil

Report · Nov 08, 2007

Why don't you just join the tables in your query?

Report · Nov 08, 2007

quote:

Why don't you just join the tables in your query?

That would be the obvious method. I only posted a sub-query example because the OP said that the query was complex.

Report · Nov 08, 2007

While it seems there are other better options available to you (I agree that it seems like it should be a single query or join), the most direct answer to your question is to do this for your second query:

<cfquery name="getStudentCourses" datasource="Oracle">
select course_id
from course_users
where users_pk1 in (<cfqueryparam cfsqltype="cf_sql_integer" value="#valueList(getStudents.pk1)#" list="true" />)
</cfquery>

Again, this is not the recommended method as the other aforementioned methods are typically preferred.

Report · Nov 08, 2007

Nevermind my previous post, I missed the part about having too many values for an IN clause. Let's assume that for some reason your first query is too complicated and the GROUP BY won't work with the course or whatever else. Even then you could use nested subqueries (as previously suggested, something like:

<cfquery name="getStudentsCourses" datasource="Oracle">
SELECT s.pk1, c.course_id FROM
(select pk1
from users
group by pk1) s,
(select course_id, user_pk1
from course_users) c
WHERE s.pk1 = c.user_pk1
</cfquery>

Report · Nov 08, 2007

Yes, I do know how to do what everyone is suggesting but can you do it some way passing in a CF tag, Array, Structure or something else? Can it be done strickly using CF? QuotedValueList will work if the count is 1000 or less but is there another tag or CF fucntion that will accomplish the same task that I don't know about?

Report · Nov 08, 2007

I think 1,000 is a limitation of the DB and not the quotedvalueList function. If that is the case, then there is no workaround unless you want to create a stored procedure and pass it a complex value.

But this all seems like overkill to me. Particularly if you "know how to do what everyone is suggesting" then why not implement one of those more sensical approaches?

Report · Nov 08, 2007

quote:

is there another tag or CF fucntion that will accomplish the same task that I don't know about?

I think what everyone has been trying to say without saying it directly is no unless you try some sort of gymnastics using Q-of-Q.

Phil

Report · Nov 08, 2007

quote:

Originally posted by: paross1

quote:

is there another tag or CF fucntion that will accomplish the same task that I don't know about?
I think what everyone has been trying to say without saying it directly is no unless you try some sort of gymnastics using Q-of-Q.

Phil

Thanks, that's all I needed. I don't know CF all that well so that's why I was asking. Sometimes it's as simple as you must do it like this; #getStudent.x# , queryToArrary() or some structure.

Report · Nov 08, 2007

quote:

Originally posted by: paross1

quote:

is there another tag or CF fucntion that will accomplish the same task that I don't know about?
I think what everyone has been trying to say without saying it directly is no unless you try some sort of gymnastics using Q-of-Q.

Phil

Actually there is, I've done it.

Take your big list and convert it into an array of smaller lists. Then write a union query that loops through this array.

But tell me again why you can't join the tables?

Report · Nov 08, 2007

It comes down to the first query, which, selects the students based on some criteria. This also provides my count of students and, what I was hoping, my input values to the next query since I already know the subset to use (obtained from the first query). The second query will always be faster if it's supplied the student's primary key versus a sub-query based on some type of criteria you must wade through.

Otherwise, if I combine the two queries it doesn't give me the student count but a course count. I would still need a student query count or I would have to loop through the course query counting the students.

Report · Nov 08, 2007

Well, here is one "method", although it is a major kludge. You just create a series of OR statements in your 2nd query based on the number of items returned from your first query. Not elegant or efficient, but it is strictly "ColdFusion".

<cfquery name="getStudents" datasource="Oracle">
SELECT pk1
FROM users
GROUP BY pk1
</cfquery>

<cfquery name="getStudentCourses" datasource="Oracle">
SELECT course_id
FROM course_users
WHERE 1=0
<cfoutput query="getStudents">
OR users_pk1 = #pk1#
</cfoutput>
</cfquery>

Of course, now you may have a query with 2000 OR lines in it, so your query might run a little slowly the more items you select from the first query.

Phil

Report · Nov 08, 2007

Based on your description of what you're trying to I fail to see how combining the queries in my example won't work for you. You can get your students (and count) programmatically and then join and also have the courses. See attached code.

If for some reason you ever needed to just get the students again, you could simply <cfoutput> over the results and use the group attribute on the student ID.

I have a very hard time believe that a single DB query with a subquery as such is not faster than two DB queries, the latter using a billion OR statements or some variation of. Give it a try and see.

Report · Nov 08, 2007

Keep in mind that the two queries I gave were only examples of what I was trying to do. The getStudentCourses query already uses 6 tables.

I do appreciate the help and the SQL coding that some of you have done but, it's not a simple 1 + 1 statement and more like a complex mathematical equation.

As pointed out, I thought about moving towards that direction anyway, is to break up the page using pagination. That way, I'm not trying to display all the students at once but only a subset of the total count. I was trying to accommodate the user versus speed.

Report · Nov 08, 2007

> Actually there is, I've done it.

I don't think the point was whether its possible but that it involves "some sort of gymnastics" in comparison to a join or subqueries.

Report · Nov 09, 2007

quote:

Keep in mind that the two queries I gave were only examples of what I was trying to do. The getStudentCourses query already uses 6 tables.

A six table join is not particularly unusual or "complex". One of the problems here is that you are not displaying exactly what you are dealing with, but just 'examples" which handicaps anyone attempting to help or offer suggestions. Now that you have posted what you are trying to accomplish, why don't you post some of your actual code, especially your SQL, since what may seem complicated to you might be fairly simple to some of the folks that read this forum.

Or not, and we can all continue to play this little game of hypotheticals.....

Phil