many-to-many relationship in SQL datasource?

[christoferjennings]

I'm evaluating SmartGWT EE 2.5 and am trying to create a page that relies on a many-to-many relationship. I want to use SQL DataSource (i.e., no Hibernate or JPA).

For example, I have a User page the needs to list the Products she has purchased. So my database has tables Users, Products, and an association table Users_Products. The SQL to select all Products for a user could be...

SELECT *
FROM Products CROSS JOIN Users_Products
ON Users_Products.User_id = {id} AND
Users_Products.Products_id = Products.id;

...where {id} is the User's id.

After hours of searching and looking through examples, I'm stumped. It seems like the only way to do this is to use Hibernate or JPA since those are the only examples. Is that true?

It seems like the User.ds.xml file could have a reference to the Product.ds.xml and Users_Products.ds.xml somehow that defines the relationship. Is that possible?

Maybe I'm approaching the problem wrong. Please let me know if I'm way out on a limb. What is the best way to do this?

Thanks,
boz

[Isomorphic]

Not sure where you've been searching, but the QuickStart Guide is the right place to start. Read the whole thing, but the answers and the links to the many related samples are all in the Server Framework chapter.

[christoferjennings]

I've been reading the guide and searching for examples online as well as in the downloaded examples source.

By the way, the guide says "For an example of a DataSource relationship using foreignKey, see the TreeGrid example in the Smart GWT EE Showcase (TreeGrid UI) and samples\showcase\war\ds\ employees.ds.xml (associated DataSource)." but there does not seem to be a TreeGrid example in the EE showcase. At least, there's nothing named "TreeGrid" in the list of examples as there is in the SmartGWT showcase.

,boz

[christoferjennings]

...and been reading the javadocs too, of course.
,boz

[Isomorphic]

This is very strange.. you say the only samples are Hibernate or JPA but the largest folder of samples is SQL - see here.

Also you show a SQL query that would do what you want, and the QuickStart Guide explains in depth how to customize SQL queries.. see the Server Framework chapter, multiple sections on SQL, including sections on doing joins and pulling in columns from other tables.

So your use case seems to be very well covered by the docs and samples (in fact it's an easy one relative to the more advanced stuff we show in samples), but let us know if we're missing something.

[christoferjennings]

I think my trouble is that I'm trying to use the basic dynamic ListGrid and DynamicForm as in the built-in-ds sample. The Large Value Map example uses a ListGridField to bind the itemId and itemName. So I think I just need to make my code more specific with explicit ListGridFields. Too tired at the moment but I'll try it tomorrow.

Thanks!
boz

[christoferjennings]

I've made progress on my many-to-many relationship in a SQL datasource. My fetch now loads all the products associated with a userId criteria. I'm not jazzed with the whereClause part, but it works.

But remove isn't working yet. I've set up the select to load a userId "field" which you can see is there in the criteria when remove is called. But further down, SQLDataSource takes out all the criteria except the primary key 'id' and then I get a "Unknown column '$criteria.userId' in 'where clause'" exception. Why does it strip out the other criteria? And how can I make it not strip out the 'userId' criteria?

******* UserProducts.ds.xml **********************
<DataSource dbName="MySqlDB" tableName="Product" ID="UserProducts" serverType="sql" autoDeriveSchema="true">
<fields>
<field name="id" type="integer" primaryKey="true" hidden="true"/>
<field name="description" length="100" type="text" />
</fields>
<operationBindings>
<operationBinding operationType="fetch">
<selectClause>$defaultSelectClause, User_Product.User_id as userId</selectClause>
<tableClause>Product CROSS JOIN User_Product ON User_Product.User_id = $criteria.userId AND User_Product.Product_id = Product.id</tableClause>
<whereClause>1 = 1</whereClause>
</operationBinding>
<operationBinding operationType="remove">
<!-- Desired SQL: "DELETE FROM User_Product WHERE User_id = 1 AND Product_id = 1;" -->
<tableClause>User_Product</tableClause>
<whereClause>User_Product.User_id = $values.userId AND User_Product.Product_id = $criteria.id</whereClause>
</operationBinding>
</operationBindings>
</DataSource>

******* log ***********************************
=== 2011-09-13 00:43:08,994 [0-20] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
id:6,
userId:3,
description:"blah blah",
_selection_9:true
},
operationConfig:{
dataSource:"UserProducts",
operationType:"remove"
},
componentId:"isc_ListGrid_1",
appID:"builtinApplication",
operation:"UserProducts_remove",
oldValues:{
id:6,
userId:3,
description:"blah blah",
_selection_9:true
}
}
=== 2011-09-13 00:43:08,994 [0-20] INFO IDACall - Performing 1 operation(s)
=== 2011-09-13 00:43:08,994 [0-20] DEBUG AppBase - [builtinApplication.UserProducts_remove] No userTypes defined, allowing anyone access to all operations for this application
=== 2011-09-13 00:43:08,995 [0-20] DEBUG AppBase - [builtinApplication.UserProducts_remove] No public zero-argument method named '_UserProducts_remove' found, performing generic datasource operation
=== 2011-09-13 00:43:08,995 [0-20] INFO SQLDataSource - [builtinApplication.UserProducts_remove] Performing remove operation with
criteria: {id:6} values: {id:6}
=== 2011-09-13 00:43:09,001 [0-20] DEBUG PoolableSQLConnectionFactory - [builtinApplication.UserProducts_remove] Returning pooled Connection
=== 2011-09-13 00:43:09,002 [0-20] DEBUG SQLTransaction - [builtinApplication.UserProducts_remove] Started new MySqlDB transaction "175310947"
=== 2011-09-13 00:43:09,002 [0-20] INFO SQLDriver - [builtinApplication.UserProducts_remove] Executing SQL update on 'MySqlDB': DELETE FROM User_Product WHERE User_Product.User_id = $criteria.userId AND User_Product.Product_id = 6
=== 2011-09-13 00:43:09,003 [0-20] WARN RequestContext - dsRequest.execute() failed:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '$criteria.userId' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
at com.mysql.jdbc.Util.getInstance(Util.java:382)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MySqlDBIO.checkErrorPacket(MySqlDBIO.java:3603)
at com.mysql.jdbc.MySqlDBIO.checkErrorPacket(MySqlDBIO.java:3535)
at com.mysql.jdbc.MySqlDBIO.sendCommand(MySqlDBIO.java:1989)
at com.mysql.jdbc.MySqlDBIO.sqlQueryDirect(MySqlDBIO.java:2150)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:461)
at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:415)
at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:535)
at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:387)
at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1338)
at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:280)
at com.isomorphic.sql.SQLDataSource.executeRemove(SQLDataSource.java:236)
at com.isomorphic.datasource.DataSource.execute(DataSource.java:1052)
at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
at com.isomorphic.application.AppBase.execute(AppBase.java:491)
at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1443)
at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:173)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:130)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:95)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:54)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1097)
at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

[christoferjennings]

The "$values.userId" should be "$criteria.userId". I experimented with values because I saw it in the log, but it's not right. ... So it should read ...

<whereClause>User_Product.User_id = $criteria.userId AND User_Product.Product_id = $criteria.id</whereClause>

[christoferjennings]

OK. Never mind my last couple of posts. That attempt was way off.

My datasource now points to the association table 'User_Product' and pulls in 'Product' fields as flattened. Since both User_id and Product_id are marked as
primaryKey, both are used during the 'remove' action. (For the 'fetch' action, my code sends 'userId' as a criteria.)

<DataSource dbName="MySqlDB" tableName="User_Product" ID="UserProducts" serverType="sql">
<fields>
<field name="User_id" type="integer" primaryKey="true" hidden="true"/>
<field name="Product_id" type="integer" primaryKey="true" hidden="true"/>

<field name="description" length="100" type="text" tableName="Product"/>
<!-- other Product fields left out of this example -->
</fields>
<operationBindings>
<operationBinding operationType="fetch">
<tableClause>User_Product, Product</tableClause>
<whereClause>User_Product.Product_id = Product.id AND User_Product.User_id = $criteria.userId</whereClause>
</operationBinding>
</operationBindings>
</DataSource>