ByList using OR instead of IN?

[aw]

From reading Exploring Lift (Section 6.1, page 81), it says that
ByList corresponds to the “field IN (x,y,z)” syntax in SQL.
Well, that was what I needed, so I took it for a test drive. However,
instead of seeing SQL like: where some.id in (4, 9, 20)
I am seeing SQL like: where some.id = 4 or some.id = 9 or some.id =
20.

I double checked the latest code:
http://github.com/dpp/liftweb/blob/master/framework/lift-persistence/lift-mapper/src/main/scala/net/liftweb/mapper/MetaMapper.scala
and it looks like there isn't a magical workaround to get IN working
as I expected.

To me, this is a violation of SQL Tuning 101: as a rule of thumb,
prefer the IN clause over an OR clause.

Is there a good reason why this code creates OR clauses instead of an
IN clause?

[David Pollak]

It used to be IN and then someone asked that it be changed to the current OR and they had a reasonable argument, so we changed it.

--
You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to lif...@googlegroups.com.
To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/liftweb?hl=en.

--
Lift, the simply functional web framework http://liftweb.net
Beginning Scala http://www.apress.com/book/view/1430219890
Follow me: http://twitter.com/dpp
Surf the harmonics

[aw]

I was searching for a prior thread and couldn't find it last night,
but I think I found it today:
http://groups.google.com/group/liftweb/browse_thread/thread/1cf5c0e153e98b47/5b60451a961ad78d?lnk=gst&q=ByList#
Is this the thread you are referring to?

It leaves me with much to be desired since the precise issue is
unclear and limited to Postgres. (It sounds like the author might
have experienced the issue with the IN (select ...) construct which
isn't even the same thing.)

I like James Iry's idea that the final SQL would be dependent on the
database dialect (Driver, I guess). I know that different vendors
have different limits for the number of elements in an IN clause. If
we had a limit of 250, for example, but had 300 items, you could
transform it into:

where (a in (1, 2, 3, ..., 250) or a in (251, 252, ..., 300))

That would be clever (and probably necessary if the database vendor
has a relatively small number for the maximum number of IN clause
elements). I'm pretty sure that Oracle has a limit of 1,000 elements
for an IN clause, and I can't imagine requiring more than that.

For the average case (< 30 items), the IN clause is going to be easier
to read, faster to parse, and likely faster to execute on almost all
database vendors -- that is why I like it.

(I luckily don't have a large data set right now, so this won't bother
me too much.)

[Naftoli Gugenheim]

Maybe there could be
case class Or(qps: QueryParam*) extends QueryParam
or something like it? This way ByList could use IN but one can also do OR?

-------------------------------------
aw<ant...@whitford.com> wrote:

--