[Lift] Re: how to do basic joins

2009-08-19 Thread David Pollak 
On Sat, Aug 15, 2009 at 11:14 AM, harryh har...@gmail.com wrote:


  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?


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

 



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

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

[Lift] Re: how to do basic joins

2009-08-15 Thread 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

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

[Lift] Re: how to do basic joins

2009-08-14 Thread 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

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

[Lift] Re: how to do basic joins

2009-08-13 Thread 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

On Aug 13, 3:41 pm, harryh har...@gmail.com wrote:
 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
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
Lift group.
To post to this group, send email to liftweb@googlegroups.com
To unsubscribe from this group, send email to 
liftweb+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/liftweb?hl=en
-~--~~~~--~~--~--~---

[Lift] Re: how to do basic joins

2009-08-13 Thread 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

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

[Lift] Re: how to do basic joins

2009-08-13 Thread 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]


On Thu, Aug 13, 2009 at 1:48 PM, harryh har...@gmail.com wrote:


 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:
  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 frameworkhttp://liftweb.net
  Beginning Scalahttp://www.apress.com/book/view/1430219890
  Follow me:http://twitter.com/dpp
  Git some:http://github.com/dpp

 



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

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

[Lift] Re: how to do basic joins

2009-08-13 Thread 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

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

[Lift] Re: how to do basic joins

2009-08-13 Thread 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?

On Thu, Aug 13, 2009 at 2:31 PM, harryh har...@gmail.com wrote:


 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

 



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

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