multiple joins per query

[Tim Pigden]

Hi

I'm not sure how to do multiple joins per query.

My table structure (loosely)

User (

  id integer,

  name string

)

Role (

  id integer,

  dataSet_id integer,

  rights integer,

  name

)

DataSet (

  id integer,

  name String

)

UserRole (

  user_id,

  role_id

)

I want to create a query something like

select dataSets from

dataSet join Role on dataSet.id = role.id

join userRole on role.id = userRole.role_id

join user on userrole.user_id = user.id

where

user.id = <X> and rights = <Y>

but I'm completely flumoxed on how to do it in mapperdao query language.

I have entities corresponding to User, DataSet and Role using the appropriate normal mappings.

Am using raw sql for time being but that feels like cheating.

Tim

[Kostas Kougios]

Hi Tim, quick reply, it should look like

...aliases...

Val ds=datasetentity

Val r= roleentity

Val u=userentity

...query...

Import Query._

(

select 

from ds

join (ds,ds.role,r)

join (r,r.users,u)

where u===myUser and r.rights===7

).toList(queryDao)

For every join you need to specify the 1st entity, the joining field and the target entity. For many to many you dont have to worry for intermediate tables.

For the where clause, you can compare straight on the values of the objects.

If you have debug logging on, you will see the actual query in the log.

Let me know if it doesnt work for you.

Cheers

Sent from my self, sorry for any typos

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

[Tim Pigden]

Hi

does it work ok the other way round?

my ds does not own the roll so it is more like

from ds

join (ds, r.dataset, r)

join (r, u.roles, u)

so the thing in the middle is a property of the thing on the right not the left.

--
Tim Pigden
Optrak Distribution Software Limited
+44 (0)1992 517100
http://www.linkedin.com/in/timpigden
http://optrak.com
Optrak Distribution Software Ltd is a limited company registered in England and Wales.
Company Registration No. 2327613 Registered Offices: Orland House, Mead Lane, Hertford, SG13 7AT England 
This email and any attachments to it may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Optrak Distribution Software Ltd. If you are not the intended recipient of this email, you must neither take any action based upon its contents, nor copy or show it to anyone. Please contact the sender if you believe you have received this email in error.

[Konstantinos Kougios]

Hmm, maybe try

from ds

join (r, r.dataset, ds)

join (u, u.roles, r)

Please let me know how it goes, I might have to check it with a usecase if it doesn't work

[Konstantinos Kougios]

What's the relationship between DataSet and Role? Looks like one-to-many or maybe one-to-one

On 19/04/13 22:15, Tim Pigden wrote:

[Tim Pigden]

many roles to one dataset

[Konstantinos Kougios]

can you share your DataSetEntity & RoleEntity?

[Tim Pigden]

  object DataSetEntity extends OptionalIntIdEntity[String, DataSet](DATA_SET) {

    override val databaseSchema = schema

    val name = column("name") to (_.name)

    val description = column("description") option (_.description)

    val active = column("active") to (_.active)

    def constructor(implicit m: ValuesMap) = {

      new DataSet (getId, name, description, active) with SurrogateIntId

    }

  }

  object RoleEntity extends OptionalIntIdEntity[String, Role](ROLE) {

    override val databaseSchema = schema

    val name= column("name") to (_.name)

    val description = column("description") option (_.description)

    val dataSet = manytoone(DataSetEntity) to (_.dataSet)

    val rights = column("rights") to (_.rights)

    def constructor(implicit m: ValuesMap) = new Role(getId, name, description, dataSet, rights) with SurrogateIntId

  }

  object UserEntity extends OptionalIntIdEntity[String, User](USER) {

    override val databaseSchema = schema

    val email = column("email") to (_.email)

    val password = column("password") to (_.password)

    val fullName = column("fullname") to (_.fullName)

    val roles = manytomany(RoleEntity) join ("user_roles", "user_id", "roles_id") to (_.roles)

    def constructor(implicit m: ValuesMap) = new User(

      getId,

      email,

      password,

      fullName,

      roles

    ) with SurrogateIntId

  }

  abstract class OptionalIntIdEntity[NK, T <: OptionalIntId[NK]](tableName: String)(implicit m: ClassManifest[T]) extends Entity[Int, SurrogateIntId, T](tableName) {

    val id = key("id") autogenerated  (_.id)

    def getId(implicit m: ValuesMap) : Option[Int] = Some(id)

  }

[Konstantinos Kougios]

aha, I see, so you got a manytoone on RoleEntity but you want to query on DataSetEntity

You can add a "forQueryOnly) pseudo - column on DataSet:

        // used only for queries
        val roles = onetomany(DataSetEntity) forQueryOnly() to (_ => Nil)

and you will need one more for Role-User in RoleEntity:

        val users = manytomanyreverse(UserEntity) forQueryOnly() to (_ => Nil)

Then the query could be like:

(

select 

from ds

join (ds,ds.roles,r)

join (r,r.users,u)

where u===myUser and r.rights===7

).toList(queryDao)

The following link is for a similar use-case. object UseCaseTraitInheritanceOfEntityAndForQueryOnlySuite at the end of the code has the forQueryOnly column.

https://code.google.com/p/mapperdao/source/browse/src/test/scala/com/googlecode/mapperdao/UseCaseTraitInheritanceOfEntityAndForQueryOnlySuite.scala

[Tim Pigden]

Hmm doesn't work

     val ds = DataSetEntity

      val r = RoleEntity

      val u = UserEntity

      (

        select

          from ds

          join (ds,ds.roles,r)

          join (r,r.users,u)

          where u===user and r.rights===7

        ).toList(dataSetDao.queryDao)

complains about ds.roles as below: 

error: type mismatch;

found   : com.googlecode.mapperdao.schema.ColumnInfoTraversableOneToMany[Int,com.optrak.bimodel.access.AccessControl.DataSet,Int,com.optrak.bimodel.access.AccessControl.DataSet]

required: com.googlecode.mapperdao.schema.ColumnInfoRelationshipBase[com.optrak.bimodel.access.AccessControl.DataSet, _, Int, Product with Serializable with com.optrak.persistence.mapperdao.OptionalIntIds.OptionalIntId[String]]

Note: Traversable[com.optrak.bimodel.access.AccessControl.DataSet] <: Any (and com.googlecode.mapperdao.schema.ColumnInfoTraversableOneToMany[Int,com.optrak.bimodel.access.AccessControl.DataSet,Int,com.optrak.bimodel.access.AccessControl.DataSet] <: com.googlecode.mapperdao.schema.ColumnInfoRelationshipBase[com.optrak.bimodel.access.AccessControl.DataSet,Traversable[com.optrak.bimodel.access.AccessControl.DataSet],Int,com.optrak.bimodel.access.AccessControl.DataSet]), but class ColumnInfoRelationshipBase is invariant in type V.

You may wish to define V as +V instead. (SLS 4.5)

Note: com.optrak.bimodel.access.AccessControl.DataSet <: Product with Serializable with com.optrak.persistence.mapperdao.OptionalIntIds.OptionalIntId[String] (and com.googlecode.mapperdao.schema.ColumnInfoTraversableOneToMany[Int,com.optrak.bimodel.access.AccessControl.DataSet,Int,com.optrak.bimodel.access.AccessControl.DataSet] <: com.googlecode.mapperdao.schema.ColumnInfoRelationshipBase[com.optrak.bimodel.access.AccessControl.DataSet,Traversable[com.optrak.bimodel.access.AccessControl.DataSet],Int,com.optrak.bimodel.access.AccessControl.DataSet]), but class ColumnInfoRelationshipBase is invariant in type F.

You may wish to define F as +F instead. (SLS 4.5)

join (ds,ds.roles,r)

[Tim Pigden]

also if I comment out the actual query I get the following runtime error

java.lang.Exception: Could not instantiate class com.optrak.bimodel.access.TestAccess: null

org.specs2.reflect.Classes$class.liftedTree1$1(Classes.scala:104)

org.specs2.reflect.Classes$class.tryToCreateObjectEither(Classes.scala:93)

org.specs2.reflect.Classes$.tryToCreateObjectEither(Classes.scala:213)

org.specs2.reflect.Classes$class.tryToCreateObject(Classes.scala:70)

org.specs2.reflect.Classes$.tryToCreateObject(Classes.scala:213)

org.specs2.specification.SpecificationStructure$$anonfun$createSpecificationOption$1.apply(BaseSpecification.scala:107)

org.specs2.specification.SpecificationStructure$$anonfun$createSpecificationOption$1.apply(BaseSpecification.scala:107)

scala.Option.orElse(Option.scala:257)

org.specs2.specification.SpecificationStructure$.createSpecificationOption(BaseSpecification.scala:107)

org.specs2.specification.SpecificationStructure$.createSpecification(BaseSpecification.scala:94)

org.specs2.runner.ClassRunner.createSpecification(ClassRunner.scala:64)

org.specs2.runner.ClassRunner.start(ClassRunner.scala:35)

org.specs2.runner.NotifierRunner.start(NotifierRunner.scala:25)

sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

java.lang.reflect.Method.invoke(Method.java:601)

org.jetbrains.plugins.scala.testingSupport.specs2.JavaSpecs2Runner.runSingleTest(JavaSpecs2Runner.java:99)

org.jetbrains.plugins.scala.testingSupport.specs2.JavaSpecs2Runner.main(JavaSpecs2Runner.java:76)

sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

java.lang.reflect.Method.invoke(Method.java:601)

com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

scala.collection.generic.GenSetFactory$$anon$1.<init>(GenSetFactory.scala:41)

scala.collection.generic.GenSetFactory.setCanBuildFrom(GenSetFactory.scala:41)

scala.collection.immutable.Set$.canBuildFrom(Set.scala:46)

scala.collection.TraversableOnce$class.toSet(TraversableOnce.scala:253)

scala.collection.AbstractTraversable.toSet(Traversable.scala:105)

com.googlecode.mapperdao.schema.ColumnRelationshipBase.<init>(ColumnRelationshipBase.scala:7)

com.googlecode.mapperdao.schema.ManyToOne.<init>(ManyToOne.scala:5)

com.googlecode.mapperdao.Entity$ManyToOneBuilder.to(Entity.scala:691)

com.optrak.bimodel.access.AccessControl$RoleEntity$.<init>(AccessControl.scala:68)

com.optrak.bimodel.access.AccessControl.RoleEntity$lzycompute(AccessControl.scala:63)

com.optrak.bimodel.access.AccessControl.RoleEntity(AccessControl.scala:63)

It doesn't like those forQueryOnly statements at all

[kostas....@googlemail.com]

Hmm probably due to cyclic deps of the objects scala cant instantiate them. The only workaround i found is to have the entity objects within an other object. 

Anyway, seems i've to write a usecase suite with similar entities and will let you know how it goes.

Sent from my self

[Tim Pigden]

Don't bother with this one on my account. The alternative of low level query works fine for me.in this case