SteveJ's ByList
David Pollak
SteveJ added a way to query by a list and I've been using it non-stop for the last couple of days. Thanks SteveJ!
David
--
lift, the secure, simple, powerful web framework http://liftweb.net
Collaborative Task Management http://much4.us
Steve Jenson
User.findAll(ByList(User.id, [1,2,3,4])) returns User's numbered 1 -
4. You can combine it with other QueryParams like MaxRows, By, etc.
Steve
David Pollak
Steve Jenson wrote:
You are welcome! For those who haven't used it, let me explain: User.findAll(ByList(User.id, [1,2,3,4])) returns User's numbered 1 - 4. You can combine it with other QueryParams like MaxRows, By, etc.
val users = User.findAll(... some criteria ...)
val companies = Company.findAll(ByList(Company.id, users.map(_.company.is).removeDuplicates))
val coMap = Map.empty ++ companies.map(c => (c.id.is, c))
Alex Boisvert
I just looked at the implementation and wanted to suggest the use of the "IN" SQL operator.
e.g. WHERE columnName IN ('foo', 'bar', ... ,'baz')
instead of combining "AND" / "OR" operators. It generally helps the database optimizer...
alex
Steve Jenson
I've actually had problems using IN with postgres where using IN
should result in a certain number of rows but instead you only see
some subset of those rows. :-(
Steve
Alex Boisvert
Michael Campbell
> Ah, ok, one more for the SQL incompatibility grief list. :(
>
>
>
>
> Hey there Alex,
>
> I've actually had problems using IN with postgres where using IN
> should result in a certain number of rows but instead you only see
> some subset of those rows. :-(
Have you been able to nail down when it happens; like, if the list is over 'x'
items? If so, might an idea be to use "IN" when it'll work, and the AND/OR
construct when it won't?
I've done something along these lines; check the list for any items, if none,
return a fast-fast SQL string that won't return anything; if 1, return "foo =
:fooValue", if > 1, return "foo in (?, ?, ...)", etc.
If you don't know what triggers that maladjusted behavior in PostgreSQL, you
can't fix it of course. =)
Is this a known bug in PostgreSQL? Seems a rather common thing to want for
it to be considered a corner case.
Steve Jenson
I never got it to happen when I had small tables, only when I had
tables large enough
that the query planner used index scans so it might be related to that.
Actually, the problems I've had were when I used subqueries with IN,
not in using
the form that Alex suggests (IN (1, 2, 3,4)) so maybe this problem
doesn't exist with that form.
If you read the Postgres documentation for IN, it says that subqueries
are not guaranteed
to run until completion. Alex's form isn't a subquery so maybe it
would work better.
I'll try changing it when I'm doing some query work later this week.
Also, it's too late to safely
change it for this release, I feel. I've given the existing use heavy usage.
Steve
