DB connector SELECT with an association

Good afternoon everyone, I have an association of two entities in Mendix as follows:  Now I would like to retrieve all these variables from a MySQL query using the database connector:  'SELECT Notification_ID, Triggering, Value, Variable, Severity, Device_Type, Num_Samples, TimeStamp_Creation, TimeStamp_last_update FROM Notifications t1 INNER JOIN Rules t2 ON t1.Rule_ID=t2.Rule_ID WHERE t1.Notification_Status = "ACTIVE" ORDER BY TimeStamp_Creation ASC'   The problem is that every Query returns a List of objects (list of notifications, in this case), but it gives me an error because the attributes “Value”, “Variable”, “Triggering”, “Device_Type” and “Severity” are attributes of the Entity “Rule”, not “Notification”. Advanced stacktrace:     at com.mendix.modules.microflowengine.MicroflowUtil.processException(MicroflowUtil.java:152) Caused by: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: java.lang.RuntimeException: The entity type 'MyFirstModule.Notification' does not contain the primitive 'Device_Type' as specified in the query.     at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:156)   Is there any way I can modify the query to return a list of Notifications, each of them containing a Rule (or vice versa) filled with those attributes?   Thank you!  
4 answers

Hello Fernando

Try This Module: https://marketplace.mendix.com/link/component/160 

It’s Working Fine All join concepts are also possible.


I don't think it is possible to do this, since the database connector only allows 1 return object. Try to return all selected values in 1 non persistant object and then process that list into your persisted / associated entities within Mendix.



Ruud is right, if you want data of both the Rule and Notification object returned, you need to run the query and after the query returns a resulting dataset use this data to instantiate this new non persistent entity with all needed attributes combined. This can, to my knowlegde, only be done by adding a simle Java action for the SQL + creation of the object and this new object in your domain model. See for instance:


Also, why don't you use the association table? I would write the query like:

SELECT n.Notification_ID
FROM MyDomainModel$Notification n
INNER JOIN MyDomainModel$Notification_Rule n_r on n_r.MyDomainModel$Notificiationid = n.id
INNER JOIN MyDomainModel$Rule r ON r.id = n_r.MyDomainModel$Ruleid
WHERE n.Notification_Status = "ACTIVE" 
ORDER BY n.TimeStamp_Creation ASC

Should work!


Hola Fernando!


Como resolviste este problema?

Tengo un caso parecido