0

I've been trying to do a join query with cayenne but I'm getting stuck with the use of Expressions and that. In sql it would be something like this:

SELECT *
FROM usuarios, rol, user_rol
WHERE usuarios.cod_usuario = user_rol.cod_usuario
AND user_rol.cod_rol = rol.cod_rol

This would be the basic. Thanks in advance

Vanessa
  • 51
  • 7
  • Just to clarify before I can write an answer, is the goal to filter usuarios records to only those that are returned in the inner join or you just need to read these 3 tables together? – andrus_a Nov 25 '13 at 06:44
  • I need to return a specific value(permisos) of 'rol' table given an certain user(login) from 'user' table. usuarios: cod_usuario, login, password user_rol: cod_usuario, cod_rol rol: cod_rol, permisos If it's not possible to return that value in a direct way with cayenne I would satisfy with been able to read these tables together. – Vanessa Nov 26 '13 at 02:04

1 Answers1

0

The answer to that, just like with the other question that you had is to use relationships. Once you map the relationships between Usuarios / UserRol and UserRol / Rol, you can either traverse the relationship from the user object by calling its methods:

Usuarios u = ..
for(UserRol ur : u.getUserRols()) {
    Rol r = ur.getRol();
    // now do something with it
}

Or find Rols with a query matching that user:

SelectQuery query = new SelectQuery(Rol.class);
query.andQualifier(ExpressionFactory.matchExp("userRol.user", u);
List<Rol> roles = context.performQuery(query);

Path argument of 'ExpressionFactory.matchExp' (i.e. "userRol.user" String) should be adjusted to match the actual relationship names starting at the Rol entity (as Rol is the root of the query), going to UserRol, and from that to Usuarios.

Community
  • 1
  • 1
andrus_a
  • 2,528
  • 1
  • 16
  • 10
  • I tried the first one and it worked but the second one gave an error "Can't resolve path component: [Rol.UserRol]." Could you tell me how does it work the method 'matchExp'? – Vanessa Nov 29 '13 at 00:31
  • So you have at least one solution already :) ... I'll expand the answer to clarify about the second option – andrus_a Nov 29 '13 at 12:45
  • Thanks. I'll be waiting for your response. – Vanessa Dec 02 '13 at 02:33
  • I already amended the answer by the time you posted your last comment. – andrus_a Dec 04 '13 at 10:25