Reusing entity mappings with custom queries

[samzil...@gmail.com]

Hi again,

I got some more advanced queries I need to run that I don't see how I can perform with mapperDao.

Basically they join some tables and return info about several entities.

1. Is it possible to query with mapperDao and return a tuple of a couple of entities?

2. Is it possible to query using a lower level api (like jdbc) and then using the resultset (or any other object holding the results - like List<Map> perhaps) and the entities to construct the models ?

Thanks,

Sam

[kostas....@googlemail.com]

1. Νο, with mapperdao you query and the result is always one entity. The entity can be fully, partially or lazilly loaded. That is, related entities can be ie lazy loaded. But no tuple of entities can be returned. What tuples were you planning to fetch?

2. You can call querydao.lowLevelQuery, read it's scaladoc before using. But then again better use the dsl. What would your query look like?

Cheers

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.
 
 

[samzil...@gmail.com]

Here's some more info about the usecase

case class User(id:Long, email:String, more stuff...)

case class Blog(id:Long, creator:User, more stuff...)

case class BlogUserInfo(user:User, blog:Blog, liked:Option[Boolean], read:Option[Boolean],...)

Each one of these has an entity.

Blog has oneToOne User, User has oneToOne Blog.

BlogUserInfo has OneToOne User and  OneToOne Blog relations.

BlogUserInfo is a join table that contains some extra info about a user-blog relation such as:

did the user read this blog? did he "like" it? more stuff...

 

I'm creating a play action that returns json info back with Blogs and the current logged in user Info about them. So the client can tell if the user already read/liked that blog...

the query is something like:

Select 

b.*, u.*, bu.* 

from 

blogs b left join users u on (b.creator_id = u.id) 

left join users_blogs bu on (bu.blog_id=b.id and bu.user_id=$(loggedin_user_id))

where bla bla bla

so the query actually returns info about 3 entities - (blog, user, bloguserinfo).

or actually only about 2 entities, (blog, bloguserinfo).

Because user can be folded into the Blogs User relation.

Now that I think about it perhaps a simple solution using the ORM would be to add a lazy relation of oneToMany from Blog to BlogUserInfo and then with this kind of query the Many side of the relation will actually only be one result. 

So Blog entity would infact hold all that is needed - blog info, creator info, blog-loggeduser info.

Nevertheless, sometimes the complexity of queries cannot be reached with an ORM and so we have to resort to lower level apis.

In these cases I would be interested to know if it's possible to reuse the ORM mappings.

I imagine something like this flow:

1. make a query like the above.

2. get the resultset( or List<Map>) 

3. retrieve the mapper for Entity A

4. create Model A by sending columns a,b,c,d,e to Entity A mapper

5. retrieve mapper for Entity B

6. create Model B by sending other cols to Entity B mapper

I don't know how feasible all this is, your'e welcome to tell me if I'm just dreaming and this is too complicated :)

[Konstantinos Kougios]

Hi, answers follows below:

On 23/04/13 18:50, samzil...@gmail.com wrote:

Here's some more info about the usecase

case class User(id:Long, email:String, more stuff...)

case class Blog(id:Long, creator:User, more stuff...)

case class BlogUserInfo(user:User, blog:Blog, liked:Option[Boolean], read:Option[Boolean],...)

Each one of these has an entity.

Blog has oneToOne User, User has oneToOne Blog.

BlogUserInfo has OneToOne User and  OneToOne Blog relations.

BlogUserInfo is a join table that contains some extra info about a user-blog relation such as:

did the user read this blog? did he "like" it? more stuff...

 

I'm creating a play action that returns json info back with Blogs and the current logged in user Info about them. So the client can tell if the user already read/liked that blog...

the query is something like:

Select 

b.*, u.*, bu.* 

from 

blogs b left join users u on (b.creator_id = u.id) 

left join users_blogs bu on (bu.blog_id=b.id and bu.user_id=$(loggedin_user_id))

where bla bla bla

so the query actually returns info about 3 entities - (blog, user, bloguserinfo).

or actually only about 2 entities, (blog, bloguserinfo).

Because user can be folded into the Blogs User relation.

Now that I think about it perhaps a simple solution using the ORM would be to add a lazy relation of oneToMany from Blog to BlogUserInfo and then with this kind of query the Many side of the relation will actually only be one result. 

So Blog entity would infact hold all that is needed - blog info, creator info, blog-loggeduser info.

Correct.

Alternatively query on BlogUserInfo.

Nevertheless, sometimes the complexity of queries cannot be reached with an ORM and so we have to resort to lower level apis.

In these cases I would be interested to know if it's possible to reuse the ORM mappings.

I imagine something like this flow:

1. make a query like the above.

2. get the resultset( or List<Map>) 

3. retrieve the mapper for Entity A

4. create Model A by sending columns a,b,c,d,e to Entity A mapper

5. retrieve mapper for Entity B

6. create Model B by sending other cols to Entity B mapper

I don't know how feasible all this is, your'e welcome to tell me if I'm just dreaming and this is too complicated :)

It is complicated because an entity has related entities and somehow you have to fetch those. So in your List[Map], Map's can contain other List[Map] and so on.

But I've deployed a snapshot of rc21 with a method in QueryDao:

queryDao.lowLevelValuesToEntities(queryConfig,entity,values).

You can provide the values (effectively a Map[String,Any] from column-name to value) that you get from the database. Values *only* for the entity table has to be provided, i.e. if you run it against Blog then
you need to provide values from the Blog table only. MapperDao will still fetch from the database any related data needed (but you can lazy-load that using the queryConfig). You
need to provide the correct data types for the values. It's a low level api, not recommended :)


    /**
     * low level conversion from database values to entities. Client code must provide a List[DatabaseValues]. Each
     * DatabaseValues must contain the map of columnname/value with value been in the correct type, otherwise
     * class cast exceptions will be thrown.
     *
     * Please note: Don't use this, better use the Query DSL. Use this method only
     * if the query dsl doesn't provide the flexibility that is needed.
     *
     * @param    queryConfig            the QueryConfig to use for this query
     * @param    entity                the entity that the query is for, i.e. ProductEntity
     * @param    values                a list of values, 1 item per row. Client code must fetch this from
     *                                 the database. Only the Entity's table has to be provided with each
     *                                 DatabaseValues.
     */
    def lowLevelValuesToEntities[ID, PC <: Persisted, T](queryConfig: QueryConfig, entity: Entity[ID, PC, T], values: List[DatabaseValues]): List[T with PC]


Cheers

On Tuesday, April 23, 2013 6:46:30 PM UTC+2, samzil...@gmail.com wrote:

Hi again,

I got some more advanced queries I need to run that I don't see how I can perform with mapperDao.

Basically they join some tables and return info about several entities.

1. Is it possible to query with mapperDao and return a tuple of a couple of entities?

2. Is it possible to query using a lower level api (like jdbc) and then using the resultset (or any other object holding the results - like List<Map> perhaps) and the entities to construct the models ?

Thanks,

Sam

--

[samzil...@gmail.com]

Thanks :)

I d/l but it broke some of my other code cause this RC probably doesn't contain the custom types change.

I will definitely try it later on.

On Tuesday, April 23, 2013 6:46:30 PM UTC+2, samzil...@gmail.com wrote:

[Konstantinos Kougios]

hmm it does contain custom types but there was a bit of package refactoring, maybe you need to reimport some classes

cheers

--