2

I think there are very good way to find entity data from MySQL through view-entity. But here I am in some situations that I need to use very complex query from MySQL databases what likes max function with group by and order by criterias. Is there any way to use complex query in Moqui?

Vy Do
  • 46,709
  • 59
  • 215
  • 313
naib khan
  • 928
  • 9
  • 16

3 Answers3

3

Use a script tag and in it you can obtain a connection to the database through the ExecutionContext getEntity and then getConnection. 

ExecutionContext ec = context.ec
Connection con = ec.getEntity().getConnection(groupName)
def statement = con.createStatement()
def queryResult = statement.executeQuery(query)

Then just process the results as described in java.sql documentation and set it to a previously defined field tag.

lombardo2
  • 141
  • 1
  • 3
  • 6
  • This is just general concept to create DB connection and execute query. But I am looking for something like if Moqui Framwork has its own way to execute raw sql query or not. – naib khan Mar 05 '20 at 07:14
  • Well, if you get the connection you should be able to run a raw sql query – zardilior Aug 06 '20 at 15:19
0

You can achieve pretty complex queries with View Entities, indeed. Have a look to entity-definition-2.1.xsd file for further details. You can use aggregate functions with <alias function="... and the GROUP BY clause will be generated automatically. <order-by> elements are supported too.

Besides, you can take advantage of any SQL function and complex expressions by nesting <complex-alias> elements. Remember that you can create INNER JOIN entity combinations with <member-entity join-from-alias="... and OUTER JOIN with <member-entity join-from-alias="..." join-optional="true".

Try to stick to SQL standards whenever possible to keep the view entity being database engine independent.

Vy Do
  • 46,709
  • 59
  • 215
  • 313
Blurbiness
  • 1
0

To elaborate on the solution from @lombardo2:

<script>
    org.moqui.context.ExecutionContext ec = context.ec
    java.sql.Connection con = ec.getEntity().getConnection('transactional')
    def statement = con.createStatement()
    def result = statement.executeQuery("select PAYMENT_ID, PAYMENT_METHOD_ID from PAYMENT where PAYMENT_ID = '$paymentId';")
    result.next()
</script>
<log message="======result1: ${result.getString(1)}"/>
<log message="======result2: ${result.getString(2)}"/>
Hans Bakker
  • 33
  • 6