Joins/Lookups on CRUD pages

[OldShatterhand]

Hi,

I'm searching for a way to make joins in CRUD pages possible.

Actually I just need to lookup some data from referenced tables.

So for example I have a table that stores information about website-projects. One of the fields in there is a foreign key to the table "site_type" that stores the different Id's for the possible types and their description. In the website-project table is just the id of each type. Both tables are correctly connected with a foreign key constraint on the mysql innodb database.

So what I'm looking for is a way to make the dropdowns on a CRUD displaying the Type Description and not the ID. The user obviously wouldn't know what to do if he sees just that ID.

I tried to join the two tables but unfortunately I have no experience with HQL at all, so far I just worked with normal SQL(e.g. mysql queries) and OpenSQL.(SAP-ABAP queries).  .

After searching for information about HQL in the Web I tried several different queries but nothing of it worked.:

joining on the table

from main_site

JOIN site_type

 

from main_site AS m

JOIN m.site_type

joining with the foreign key (found some tutorial where they said HQL works like this):

from main_site AS m

JOIN m.fk_main_site_site_type1

Nothing of that worked.

It would be great if someone could help me and say me if and how it's possible to do a lookup as described above (hasn't to be a join solution)

how joins generally work in CRUD pages (in case I need it later, which is high probable).

 

Thanks in advance.

OldShatterhand

[Alessio Stalla]

Hi, welcome to the list!

This topic has been discussed a few times before. The short answer is: generic JOINs are not currently supported, we will address them in a future version. However, the special case of lookup/decoding of related values is well covered.
For general information, see the page about the Tables admin panel (http://www.manydesigns.com/en/portofino/docs/reference/administration/tables) and specifically read about the "short name" - used to decode IDs to a user-friendly string - and "selection providers" - used to create custom relationships (e.g. when a foreign key is not explicit in the underlying database or when you want to filter the selection of values somehow).

You can also search this group through the web interface (https://groups.google.com/forum/#!forum/manydesigns-portofino) for the terms I gave you to see relevant past discussions.

Cheers,
Alessio

[OldShatterhand]

Hi Allessio,

thank you for this answer. With that and some other hints from the mailing list I was so far able to solve most of my "problems".

I just don't really under stand that selection provider thing:

I have a many-to-many link table which links sites to contacts. I use a child crud for displaying those Contact below the site details.

Now I want to change the in the search screen displayed name with a selection provider.

I created a selection provider with the following sql:

SELECT c.contact_id, CONCAT(c.first_name,' ', c.last_name, ' - ', c.language_department)

FROM contact c

As column I entered contact_id (column in the link table has the same name as in contact table).

The query works if I execute it directly on the DB.

Unfortunately it isn't possible to select that provider durably. After saving it just switches back to "none" and in the tomcat log occurs the following message:

13:37:52.468 [userId=testadmin,i89398844444444] c.m.p.p.c.ModelSelectionProviderSupport [ModelSelectionProviderSupport.java:282]

ERROR: Exception in populating selection provider contact_link_details

org.hibernate.exception.GenericJDBCException: error executing work

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:2001) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

at com.manydesigns.portofino.application.QueryUtils.runSql(QueryUtils.java:109) ~[portofino-core-4.0.10.jar:4.0.10]

at com.manydesigns.portofino.pageactions.crud.ModelSelectionProviderSupport.createSelectionProvider(ModelSelectionProviderSupport.java:280) [portofino-web-4.0.10.jar:4.0.10]

at com.manydesigns.portofino.pageactions.crud.ModelSelectionProviderSupport.createSelectionProvider(ModelSelectionProviderSupport.java:186) [portofino-web-4.0.10.jar:4.0.10]

at com.manydesigns.portofino.pageactions.crud.ModelSelectionProviderSupport.setupSelectionProvider(ModelSelectionProviderSupport.java:149) [portofino-web-4.0.10.jar:4.0.10]

at com.manydesigns.portofino.pageactions.crud.ModelSelectionProviderSupport.setup(ModelSelectionProviderSupport.java:82) [portofino-web-4.0.10.jar:4.0.10]

at com.manydesigns.portofino.pageactions.crud.CrudAction.prepare(CrudAction.java:193) [portofino-web-4.0.10.jar:4.0.10]

at sun.reflect.GeneratedMethodAccessor73.invoke(Unknown Source) ~[na:na]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_51]

at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_51]

at net.sourceforge.stripes.controller.BeforeAfterMethodInterceptor.invoke(BeforeAfterMethodInterceptor.java:154) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.BeforeAfterMethodInterceptor.intercept(BeforeAfterMethodInterceptor.java:104) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.ExecutionContext.proceed(ExecutionContext.java:155) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.ExecutionContext.wrap(ExecutionContext.java:74) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.DispatcherHelper.invokeEventHandler(DispatcherHelper.java:454) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.DispatcherServlet.invokeEventHandler(DispatcherServlet.java:278) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.DispatcherServlet.service(DispatcherServlet.java:160) [stripes-1.5.7.jar:1.5.7]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) [servlet-api.jar:na]

at net.sourceforge.stripes.controller.DynamicMappingFilter$2.doFilter(DynamicMappingFilter.java:431) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.StripesFilter.doFilter(StripesFilter.java:260) [stripes-1.5.7.jar:1.5.7]

at net.sourceforge.stripes.controller.DynamicMappingFilter.doFilter(DynamicMappingFilter.java:418) [stripes-1.5.7.jar:1.5.7]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at com.manydesigns.portofino.servlets.DispatcherFilter.doFilter(DispatcherFilter.java:90) [portofino-web-4.0.10.jar:4.0.10]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at com.manydesigns.portofino.servlets.AppUrlFilter.doFilter(AppUrlFilter.java:81) [portofino-web-4.0.10.jar:4.0.10]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at com.manydesigns.portofino.servlets.ApplicationFilter.doFilter(ApplicationFilter.java:92) [portofino-web-4.0.10.jar:4.0.10]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at net.sourceforge.stripes.controller.StripesFilter.doFilter(StripesFilter.java:260) [stripes-1.5.7.jar:1.5.7]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:399) [urlrewritefilter-4.0.4.jar:4.0.4]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at com.manydesigns.portofino.servlets.CleanupFilter.doFilter(CleanupFilter.java:46) [portofino-web-4.0.10.jar:4.0.10]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) [shiro-web-1.2.0.jar:1.2.0]

at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) [shiro-web-1.2.0.jar:1.2.0]

at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) [shiro-core-1.2.0.jar:1.2.0]

at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) [shiro-core-1.2.0.jar:1.2.0]

at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:380) [shiro-core-1.2.0.jar:1.2.0]

at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) [shiro-web-1.2.0.jar:1.2.0]

at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) [shiro-web-1.2.0.jar:1.2.0]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at com.manydesigns.elements.servlet.ElementsFilter.doHttpFilter(ElementsFilter.java:136) [elements-4.0.10.jar:4.0.10]

at com.manydesigns.elements.servlet.ElementsFilter.doFilter(ElementsFilter.java:83) [elements-4.0.10.jar:4.0.10]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at com.manydesigns.portofino.servlets.UTF8RequestFilter.doFilter(UTF8RequestFilter.java:40) [portofino-web-4.0.10.jar:4.0.10]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) [catalina.jar:6.0.36]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:6.0.36]

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) [catalina.jar:6.0.36]

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) [catalina.jar:6.0.36]

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) [catalina.jar:6.0.36]

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) [catalina.jar:6.0.36]

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [catalina.jar:6.0.36]

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) [catalina.jar:6.0.36]

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861) [tomcat-coyote.jar:6.0.36]

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606) [tomcat-coyote.jar:6.0.36]

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) [tomcat-coyote.jar:6.0.36]

at java.lang.Thread.run(Thread.java:744) [na:1.7.0_51]

Caused by: java.sql.SQLException: No database selected

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119) ~[mysql-connector-java-5.1.15.jar:na]

at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281) ~[mysql-connector-java-5.1.15.jar:na]

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) ~[c3p0-0.9.1.2.jar:0.9.1.2]

at com.manydesigns.portofino.application.QueryUtils$1.execute(QueryUtils.java:116) ~[portofino-core-4.0.10.jar:4.0.10]

at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1997) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

... 66 common frames omitted

[Ciro Fiorillo]

Hi,

Perhaps (I cannot test at the moment) the query needs a name for the second column in the query.

--

Cordialmente...

---

Ciro Fiorillo

--
You received this message because you are subscribed to the Google Groups "manydesigns-portofino" group.
To unsubscribe from this group and stop receiving emails from it, send an email to manydesigns-port...@googlegroups.com.
To post to this group, send email to manydesign...@googlegroups.com.
Visit this group at http://groups.google.com/group/manydesigns-portofino.
For more options, visit https://groups.google.com/groups/opt_out.

[Alessio Stalla]

On Sat, Feb 15, 2014 at 10:45 PM, Ciro Fiorillo <ciro.f...@races.it> wrote:

Hi,

Perhaps (I cannot test at the moment) the query needs a name for the second column in the query.

Hmm... I see in the posted stack trace the line:

Caused by: java.sql.SQLException: No database selected

I guess the MySQL connection string does not include a database name (i.e. it only specifies host and port). If that's the case, the OP should either modify the connection string to include the default database (= schema in MySQL) or prefix table names in SQL queries with the db name (e.g. ... FROM my_schema.contact c).

Ciao,
Alessio

[OldShatterhand]

Thanks, that solved my problem.

I just didn't know that I have to include the default DB in the connection string. I thought it would be enough if I select the right Database everywhere. But now it works perfectly, thank you.

To unsubscribe from this group and stop receiving emails from it, send an email to manydesigns-portofino+unsub...@googlegroups.com.

To post to this group, send email to manydesign...@googlegroups.com.
Visit this group at http://groups.google.com/group/manydesigns-portofino.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "manydesigns-portofino" group.

To unsubscribe from this group and stop receiving emails from it, send an email to manydesigns-portofino+unsub...@googlegroups.com.

[Alessio Stalla]

The database you select is the db connection on the Portofino side; the "database" you specify in the MySQL connection string is really the default schema to use in queries (MySQL has its own idiosyncratic terminology). HQL queries work the same because Hibernate always prefixes table names with the schema name; however, in custom SQL queries you have to specify the schema if a default one is not provided in the connection string.

To unsubscribe from this group and stop receiving emails from it, send an email to manydesigns-port...@googlegroups.com.