WHERE IN clauses

229 views
Skip to first unread message

Raymond Camden

unread,
Oct 9, 2009, 11:08:54 AM10/9/09
to cf-or...@googlegroups.com
Has anyone been able to get a WHERE IN clause with HQL to work? Assume
you have an array of IDs and want to do something like

from foo where id in (?)

It _always_ fails for me.

--
===========================================================================
Raymond Camden, ColdFusion Jedi Master

Email : r...@camdenfamily.com
Blog : www.coldfusionjedi.com
AOL IM : cfjedimaster

Keep up to date with the community: http://www.coldfusionbloggers.org

Shannon Hicks

unread,
Oct 9, 2009, 11:11:44 AM10/9/09
to cf-orm-dev
I got it to work:

itemList = arrayToList(items.getresult()["beerID"]); // this is just a
comma delimited list of IDs
return ORMExecuteQuery("FROM Beer WHERE beerID IN (#itemList#)",
{},false,{cacheable=true}); // ORM query

Shan

Raymond Camden

unread,
Oct 9, 2009, 11:15:04 AM10/9/09
to cf-or...@googlegroups.com
Ok, that toi me is cheating. ;) I want to use bound parameters and I
want to use an array. That should be possible, should it not?

Shannon Hicks

unread,
Oct 9, 2009, 11:18:49 AM10/9/09
to cf-orm-dev
I'd have to assume that like cfqueryparam you need to somehow tell
hibernate that the param you're passing is a list. No idea how,
though. Maybe it's a good question for ask a jed.... oh, wait. :)

Shan

Dan Vega

unread,
Oct 9, 2009, 1:43:22 PM10/9/09
to cf-orm-dev
Seems like a bug to me. I tried everything I could think of and It
just does not like the array.

Bob Silverberg

unread,
Oct 9, 2009, 2:26:50 PM10/9/09
to cf-or...@googlegroups.com
I'd have to agree with Shannon that the parameter would need to be passed as a list, rather than an array, because that's simply what the IN operator expects.  So in any case I think you'd have to convert an array to a list.  I don't consider _that_ a bug.

It does seem, however (and perhaps this is what Dan is eluding to), that it is not possible to bind a list of values to a parameter, and that seems like missing functionality.  When you try to do it, for example like this:

DeptArray=[20,22,25];
listing = ORMExecuteQuery("FROM Department WHERE DeptId IN (:DeptIds)", {DeptIds=ArrayToList(DeptArray)});

You get an error back from CF:

The value 20,22,25 cannot be converted to a number.

So it looks like we need a way to tell CF that the param we're passing is a list, rather than a simple value (which it looks like it's assuming).  I suppose that enhancement request could be combined with a suggestion that we be allowed to pass an array, in addition to a list, as the parameter value, but I think that array vs. list issue is really secondary to the issue that it seems that it is impossible to bind a list of values to a param for use with the IN operator.

Thoughts?
--
Bob Silverberg
www.silverwareconsulting.com

John Whish

unread,
Oct 9, 2009, 3:17:58 PM10/9/09
to cf-orm-dev
I know that hibernate has the setParameterList() method for this. I
can see it if I dump out query, but can't figure out how to call it.
This is what I've tried.

<cfscript>
query = ORMGetSession().createQuery( "FROM Art WHERE ArtID IN
(:mylist)" );
WriteDump( query );
// this line throws an error "The setParameterList method was not
found."
query.setParameterList( "mylist", "1,2,3" );
</cfscript>

John Whish

unread,
Oct 9, 2009, 3:39:15 PM10/9/09
to cf-orm-dev
Ah, I'd forgotten about method overloading in Java. This does it:

<cfscript>
query = ORMGetSession().createQuery( "FROM Art WHERE ArtID IN (:mylist)" );

parameters = "1,4,8";
query.setParameterList( "mylist", parameters.split(",") );
writeDump(query.list());
</cfscript>

Whilst this doesn't answer Ray's question it is a possible work around.

Raymond Camden

unread,
Oct 9, 2009, 4:07:10 PM10/9/09
to cf-or...@googlegroups.com
Interesting. Did a native CF array work as well?

parameters=[1,4,8]
query.setParameterList( "mylist", parameters )

John Whish

unread,
Oct 9, 2009, 4:15:02 PM10/9/09
to cf-or...@googlegroups.com
Hi Ray, no I tried that first and it didn't work, it had to be a java array.

Raymond Camden

unread,
Oct 9, 2009, 4:21:53 PM10/9/09
to cf-or...@googlegroups.com
Lovely. I will say that I spoke w/ an engineer at MAX and he was
_convinced_ it should work. He promised to get back into touch with me
and I assume he will verify what you guys found - that there is no way
to do it directly.


On Fri, Oct 9, 2009 at 3:15 PM, John Whish <john....@googlemail.com> wrote:
>
> Hi Ray, no I tried that first and it didn't work, it had to be a java array.
>
> >
>



John Whish

unread,
Oct 9, 2009, 4:36:43 PM10/9/09
to cf-or...@googlegroups.com
Well, it would be nice if it did work :) Let us know what he says!

What would be really cool is if you passed in an native cf array and
ColdFusion would know that because it was an array it should treat it
as multiple params to bind. If you passed in a string or cf list then
it would treat it as a single param in case you passed a string with a
comma in it (which I supposed could happen if you were searching for a
post title or something).

This code against cfartgallery datasource, does seem to do the trick:

<cfscript>
query = ORMGetSession().createQuery( "FROM Art WHERE ArtID IN (:mylist)" );

parameters = "4,8";

Raymond Camden

unread,
Oct 9, 2009, 4:39:27 PM10/9/09
to cf-or...@googlegroups.com
Of course, it would fail if you were doing a string search, and one of
the items had a comma in it. This code would effectively only be
useful for numerical filters.

(Well, you could use other tokens, but....)

John Whish

unread,
Oct 9, 2009, 4:52:23 PM10/9/09
to cf-or...@googlegroups.com
True! I did say that it worked against cfartgallery :)

Sean Corfield

unread,
Oct 9, 2009, 6:02:07 PM10/9/09
to cf-or...@googlegroups.com
On Fri, Oct 9, 2009 at 1:07 PM, Raymond Camden <rca...@gmail.com> wrote:
> Interesting. Did a native CF array work as well?
>
> parameters=[1,4,8]
>  query.setParameterList( "mylist", parameters )

Try:

query.setParameterList( "mylist", parameters.toArray() );

(I've not tried it but it does return a native array - it's just a
native array of Object rather than a native array of int which might
be what it requires so it all depends on whether it can cast the
elements)
--
Sean A Corfield -- (904) 302-SEAN
Railo Technologies US -- http://getrailo.com/
An Architect's View -- http://corfield.org/

"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood

Bob Silverberg

unread,
Oct 9, 2009, 9:06:17 PM10/9/09
to cf-or...@googlegroups.com
Interestingly, that does work on the cfartgallery database, but not on MySQL.  Also John's solution of myList.split(",") does not work on MySQL.  Each of them throw a Java casting error:

java.lang.String cannot be cast to java.lang.Integer

If I manually JavaCast("int",value) when storing the items in the array it works, but that's not a very friendly workaround.  The error is being thrown by Hibernate, so I guess the routines that it uses to set parameters is different between databases.  It seems to have no problem using an array like [10,20] when running against the Art table (which does have an integer PK), but throws the error when running against my MySQL table (with an integer PK).

So it looks like one would have to manually JavaCast one's values in either a list or an array in order to pass the list/array to a bound IN parameter on MySQL, and possibly other DBMSs as well.  Or maybe one of you Java folks knows of a simple method of casting an entire array in one fell swoop?  One extra line of code (or method call), which would take the array of strings and cast them into ints (or whatever is required), might be a handy workaround.  Any ideas on that?

Anyone care to try it out on another DBMS to see what happens?


On Fri, Oct 9, 2009 at 6:02 PM, Sean Corfield <seanco...@gmail.com> wrote:

Try:

query.setParameterList( "mylist", parameters.toArray() );

(I've not tried it but it does return a native array - it's just a
native array of Object rather than a native array of int which might
be what it requires so it all depends on whether it can cast the
elements)
--
Sean A Corfield -- (904) 302-SEAN
Railo Technologies US -- http://getrailo.com/
An Architect's View -- http://corfield.org/

"If you're not annoying somebody, you're not really alive."
-- Margaret Atwood


--
Bob Silverberg
www.silverwareconsulting.com

Sean Corfield

unread,
Oct 9, 2009, 10:42:56 PM10/9/09
to cf-or...@googlegroups.com
On Fri, Oct 9, 2009 at 6:06 PM, Bob Silverberg <bob.sil...@gmail.com> wrote:
> Interestingly, that does work on the cfartgallery database, but not on
> MySQL.  Also John's solution of myList.split(",") does not work on MySQL.
> Each of them throw a Java casting error:
>
> java.lang.String cannot be cast to java.lang.Integer
>
> If I manually JavaCast("int",value) when storing the items in the array it
> works, but that's not a very friendly workaround.

I had a suspicion that might happen :(

> on MySQL, and possibly other DBMSs as well.  Or maybe one of you Java folks
> knows of a simple method of casting an entire array in one fell swoop?  One

Not off the top of my head...

Bob Silverberg

unread,
Feb 9, 2010, 6:42:22 AM2/9/10
to cf-or...@googlegroups.com
Here's a suggestion from Barney to an old problem discussed on the list.

---------- Forwarded message ----------
From: Barney Boisvert <bboi...@gmail.com>
Date: Tue, Feb 9, 2010 at 12:12 AM
Subject: Re: WHERE IN clauses
To: Bob Silverberg <bob.sil...@gmail.com>


Don't know if this is useful, but it's your one-liner.

<cffunction name="ormList" output="false">
       <cfargument name="list" />
       <cfargument name="type" default="int" />
       <cfset var result = [] />
       <cfset var i = "" />
       <cfloop list="#list#" index="i">
               <cfset arrayAppend(result, javaCast(type, i)) />
       </cfloop>
       <cfreturn result.toArray() />
</cffunction>

Here it is in action:

       <cfset images = ormGetSession().createQuery('from Image where id in
(:id)') />
       <cfset images.setParameterList('id',
ormList(session.filters.imageId)) />
       <cfset images = images.list() />

I couldn't figure out how to reply to the group.  Oh well.

cheers,
barneyb

On Oct 9 2009, 5:06 pm, Bob Silverberg <bob.silverb...@gmail.com>
wrote:

> Interestingly, that does work on the cfartgallery database, but not on
> MySQL.  Also John's solution of myList.split(",") does not work on MySQL.
> Each of them throw a Java casting error:
>
> java.lang.String cannot be cast to java.lang.Integer
>
> If I manually JavaCast("int",value) when storing the items in the array it
> works, but that's not a very friendly workaround.  The error is being thrown
> by Hibernate, so I guess the routines that it uses to set parameters is
> different between databases.  It seems to have no problem using an array
> like [10,20] when running against the Art table (which does have an integer
> PK), but throws the error when running against my MySQL table (with an
> integer PK).
>
> So it looks like one would have to manually JavaCast one's values in either
> a list or an array in order to pass the list/array to a bound IN parameter
> on MySQL, and possibly other DBMSs as well.  Or maybe one of you Java folks
> knows of a simple method of casting an entire array in one fell swoop?  One
> extra line of code (or method call), which would take the array of strings
> and cast them into ints (or whatever is required), might be a handy
> workaround.  Any ideas on that?
>
> Anyone care to try it out on another DBMS to see what happens?
>



--
Bob Silverberg
www.silverwareconsulting.com

Hands-on ColdFusion ORM Training @ cf.Objective() 2010
www.ColdFusionOrmTraining.com


Ron West

unread,
Feb 28, 2012, 11:57:18 AM2/28/12
to cf-or...@googlegroups.com
I am able to get this to work:

var arNews = ORMExecuteQuery("from news where newsID in (:lstNewsID)", { lstNewsID = listToArray(arguments.lstNewsID) });
Reply all
Reply to author
Forward
0 new messages