how to do basic joins

[harryh]

I have 3 classes like so

class Foo extends LongKeyedMapper[Foo] with IdPK
def getBazes = {
Baz.findAll(...)
}
}

class Bar extends LongKeyedMapper[Bar] with IdPK
object fooid extends MappedLongForeignKey(this, Foo)
}

class Baz extends LongKeyedMapper[Baz] with IdPK
object barid extends MappedLongForeignKey(this, Bar)
}

How do I write Baz.findAll(...) so it will give me all the Bazes that
point to any Bars that point to the Foo. In SQL I would write
something like this:

SELECT * FROM bazzes WHERE bazzes.barid = bars.id AND bars.fooid =
111; (where 111 is foo.id)

I feel like I should be using ByRef here? But it's not clear to me
how to do it.

-harryh

[jon]

One possibility is to do Baz.findAllBy{PreparedStatement,InsecureSql}
(section 6.3.2 of lift book)

I am also curious if there is a safe way to construct a join.

- Jon

[David Pollak]

On Thu, Aug 13, 2009 at 12:41 PM, harryh <har...@gmail.com> wrote:

I have 3 classes like so

class Foo extends LongKeyedMapper[Foo] with IdPK
 def getBazes = {
   Baz.findAll(...)
 }
}

Baz.findAll(In.fk(Baz.barid, By(Bar.fooid, this.id)))
 

class Bar extends LongKeyedMapper[Bar] with IdPK
 object fooid extends MappedLongForeignKey(this, Foo)
}

class Baz extends LongKeyedMapper[Baz] with IdPK
 object barid extends MappedLongForeignKey(this, Bar)
}

How do I write Baz.findAll(...) so it will give me all the Bazes that
point to any Bars that point to the Foo.  In SQL I would write
something like this:

SELECT * FROM bazzes WHERE bazzes.barid = bars.id AND bars.fooid =
111;   (where 111 is foo.id)

I feel like I should be using ByRef here?  But it's not clear to me
how to do it.

-harryh

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

[harryh]

Foo = Venue, Bar = VenueAlias, Baz = Checkin

lazy val checkins = Checkin.findAll(In.fk(Checkin.aliasid, By
(VenueAlias.venueid, this.id)))

confusing error message on compile:

found : com.harryh.model.Checkin.aliasid.type (with underlying type
object com.harryh.model.Checkin.aliasid)
required: net.liftweb.mapper.MappedForeignKey
[?,?,com.harryh.model.Checkin]
Note that implicit conversions are not applicable because they are
ambiguous:
both method mapToType in object MappedField of type [T,A <:
net.liftweb.mapper.Mapper[A]](net.liftweb.mapper.MappedField[T,A])T
and method mapToType in object MappedField of type [T,A <:
net.liftweb.mapper.Mapper[A]](net.liftweb.mapper.MappedField[T,A])T
are possible conversion functions from
com.harryh.model.Checkin.aliasid.type to
net.liftweb.mapper.MappedForeignKey[?,?,com.harryh.model.Checkin]
lazy val checkins = Checkin.findAll(In.fk(Checkin.aliasid, By
(VenueAlias.venueid, this.id)))


On Aug 13, 4:20 pm, David Pollak <feeder.of.the.be...@gmail.com>
wrote:

> Beginning Scalahttp://www.apress.com/book/view/1430219890

[David Pollak]

Sorry... try:

class Foo extends LongKeyedMapper[Foo] with IdPK {
 def getBazes = {

   Baz.findAll(In(Baz.barid, Bar.id, By(Bar.fooid, this)))
 }
}

object Foo extends Foo with LongKeyedMetaMapper[Foo]

class Bar extends LongKeyedMapper[Bar] with IdPK {
 object fooid extends MappedLongForeignKey(this, Foo)
}

class Baz extends LongKeyedMapper[Baz] with IdPK {
 object barid extends MappedLongForeignKey(this, Bar)
}

object Baz extends Baz with LongKeyedMetaMapper[Baz]

object Bar extends Bar with LongKeyedMetaMapper[Bar]

Lift, the simply functional web framework http://liftweb.net

Beginning Scala http://www.apress.com/book/view/1430219890

[harryh]

Now I get an NPE :(

Message: java.lang.NullPointerException
net.liftweb.mapper.In$$anon$3.<init>(MetaMapper.scala:1206)
net.liftweb.mapper.In$.apply(MetaMapper.scala:1200)
com.harryh.model.Venue.checkins(Venue.scala:40)

-harryh

[David Pollak]

Please post a reproducible example.  I use this call pretty regularly and have not seen any problems.

Also, what version of Lift are you using?

[harryh]

> Please post a reproducible example.  I use this call pretty regularly and
> have not seen any problems.

Sure thing. I will put something together tomorrow. If feel like I
must be making a silly newbie mistake of some kind because I'm sure
that you (and others) must be doing this sort of things without
problems. Haven't been able to figure out what it is though.

> Also, what version of Lift are you using?

1.0

-harryh

[harryh]

> class Foo extends LongKeyedMapper[Foo] with IdPK {
>  def getBazes = {
>    Baz.findAll(In(Baz.barid, Bar.id, By(Bar.fooid, this)))
>  }

OK, upgrading to 1.1-M4 got rid of the NPE so I'm just gonna assume
there was a bug that has been fixed. However the SQL query that lift
is generating is no good. It's doing something like this:

SELECT bazes.cityid, bazes.xdatetime, bazes.aliasid, bazes.userid,
bazes.id FROM bazes WHERE barid IN (SELECT id FROM bars WHERE fooid =
22120 );

which is WAY WAY more inefficient than this (by a factor of like
100x):

SELECT bazes.cityid, bazes.xdatetime, bazes.aliasid, bazes.userid,
bazes.id FROM bazes, bars WHERE bazes.barid = bars.id AND bars.fooid =
22120;

At least on MySQL (which is what I'm using). Is there any way to get
Lift to use a query like the latter instead of the former?

-harryh

[David Pollak]

No.  Lift uses IN ( ) rather than joins.  I know that it's fairly recent that MySQL added nested queries, but I would have thought that they would have had a reasonable optimizer for them.
 

-harryh

--