OneToMany generates additional requests. How to populate result set from the join itself

19 views
Skip to first unread message

y.sh...@gmail.com

unread,
May 21, 2013, 10:02:15 AM5/21/13
to mapp...@googlegroups.com
Hi all,
I am facing with the problem using OneToMany, ManyToOne etc. relations. After the query has been made mapperdao retrieves information by making single queries to the database.
Is there any way to get the resultant object with all related data fetched with only one query?

This query
      val i=ItemEntity
      val b=BrandEntity
      val m=ImageEntity

      val q=select from i join (i, i.images, m) join(i,i.brand,b) where i.id===1
      val results=queryDao.query( q)

produces these requests to the database( if I remove where clause and select some number of rows it make ridiculously huge amount of request while the data is already available from the first request with all the information included in the join):
167 Query
select 
  it1.id,it1.parent_id,it1.category_id,it1.brands_id,it1.name,it1.price,it1.state,it1.color,it1.material,it1.description,it1.brands_id
from 
  items it1 
inner join images im1 on it1.id = im1.item_id
inner join brands br1 on it1.brands_id = br1.id
where it1.id = 1

168 Query SET autocommit=1
168 Query
select id,name,description
from brands 
where id = 1

172 Query SET autocommit=1
172 Query
select id,item_id,num
from images 
where item_id = 1

First query is perfect, however additional queries are made in order to create case classes and fill brand and images fields. Is there any way to retrieve everything from the join query in one shot?
Here are the Entities:

object ItemEntity extends Entity[Int,SurrogateIntId,Itemm]("items") {

  val id = key("id") autogenerated (_.id)
  val parent_id = column("parent_id") option (_.parent_id)
  val category_id = column("category_id") to (_.category_id)
  val brands_id = column("brands_id") to (_.brands_id)
  val name = column("name") to (_.name)
  val price = column("price") to (_.price)
  val state = column("state") to (_.state)
  val color = column("color") to (_.color)
  val material = column("material") to (_.material)
  val description = column("description") to (_.description)
  val brand = manytoone(BrandEntity) to (_.brand)
  val images = onetomany(ImageEntity) foreignkey "item_id" to (_.images)

  def constructor(implicit m:ValuesMap) = new Itemm(id, parent_id, category_id, brands_id, name, price, state, color, material ,description, brand, images)
    with Stored
}

object BrandEntity extends Entity[Int,SurrogateIntId,Brand]("brands") {
  val id = key("id") to (_.id)
  val name = column("name") to (_.name)
  val description = column("description") to (_.description)

  def constructor(implicit m:ValuesMap) = new Brand(id,  name, description)
    with Stored
}

object ImageEntity extends Entity[Int,SurrogateIntId,Image]("images") {

  val id = key("id") autogenerated (_.id)
  val item_id = column("item_id") to (_.item_id)
  val num = column("num") to (_.num)

  def constructor(implicit m:ValuesMap) = new Image(id,  item_id, num)
    with Stored
}

case class Itemm(
                 id: Int ,
                 parent_id: Option[Int],
                 category_id: Int,
                 brands_id: Int,
                 name: String,
                 price: Float,
                 state: Int,
                 color: String,
                 material: String,
                 description: String,
                 brand:Brand,
                 images:Set[Image]
                 )
case class Image(id:Int,item_id:Int,num:Int)
case class Brand(id: Int,name: String,description: String)

kostas....@googlemail.com

unread,
May 21, 2013, 1:14:31 PM5/21/13
to mapp...@googlegroups.com, mapp...@googlegroups.com
Hi, this is one optimization i want to do for the library. At the moment it works as you described. I'll try to do the refactoring this summer

Sent from my self
--
You received this message because you are subscribed to the Google Groups "mapperdao" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Kostas Kougios

unread,
May 21, 2013, 3:53:18 PM5/21/13
to mapp...@googlegroups.com, y.sh...@gmail.com
In the meantime you might want to lazy load or skip loading of related data:

https://code.google.com/p/mapperdao/wiki/LazyLoading
https://code.google.com/p/mapperdao/wiki/ConfigurableCRUD
Reply all
Reply to author
Forward
0 new messages