Participating Frequently

Question

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

Forum|Forum|18 years ago
November 8, 2007
9 replies
2372 views

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>

Database access

This topic has been closed for replies.

C

cf_dev2

Inspiring

> 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.

P

paross1

Participating Frequently

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

N

nettlesd1995Author

Participating Frequently

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.

N

nettlesd1995Author

Participating Frequently

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.

P

paross1

Participating Frequently

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

A

Anonymous

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?

P

paross1

Participating Frequently

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

N

nettlesd1995Author

Participating Frequently

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.

A

Anonymous

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>

N

nettlesd1995Author

Participating Frequently

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?

A

Anonymous

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.

D

Dan_Bracuk

Inspiring

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

P

paross1

Participating Frequently

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.

N

nettlesd1995Author

Participating Frequently

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>

P

paross1

Participating Frequently

<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

P

paross1

Participating Frequently

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded