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

[y.sh...@gmail.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]

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]

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