I want to use the OQL query statement of Data Set to delete duplicate objects in certain columns of the data grid and generate a reportGRID. How to write an OQL query statement?

0
I want to use the OQL query statement of Data Set to delete duplicate objects in certain columns of the data grid and generate a reportGRID. As shown in Figure 1, I want to delete all two rows with the same name, address, and difference attributes in the datagrid above and generate a reportGRID (below the datagrid). The already written oql statement is shown in Figure 2, but it will also display different classroomnames in the reportGRID (as shown in the blue circle in Figure 1). If I delete the classroomname after 'GROUP BY' in the statement, the oql statement will report an error (as shown in Figure 3). How can I write my OQL statement to delete all two rows in the datagrid that have the same name, address, and difference attributes? And the classroomname also needs to be displayed in the reportGRID. We look forward to your reply.
asked
2 answers
0

OQL doesn't support delete statements, so you have to build something else.
I would suggest that you create a oql with the right group by having condition, like "group by classroomname, name having count(*)>1"
this would result in a dataset of duplicate objects.
use the module OQL (or saferOQL) to create a list of duplicateresults on which you can retrieve  the duplicate items.
keep the head item and delete the remaining.(you can also build logic to associated objects to be reassociated with the head-item)

don't forget to add validation to prevent creating duplicate items.    

 

answered
1

Use a select distinct statement, see for example this forum post https://forum.mendix.com/link/space/studio-pro/questions/90820 (although different issue it will provide insight into how the query should be formulated)

answered