Delete multiple rows in table
The table component in the ToolJet has the option for bulk selection of rows that can have various use cases such as updating or deleting records. However, the datasources does not support bulk delete or bulk update operations.
In this guide, we will learn how we can delete multiple rows in a table. We have assumed that you have successfully connected the data source. For this guide, we will be using the PostgreSQL data source as an example database, currently, this workaround can be used only for PostgreSQL and MySQL.
1. Create a query to fetch the data from the database
Create a new query, name it getRecords
and use SQL mode:
SELECT * FROM tooljet // replace tooljet with your table name
Enable the Run the query on application load? option. This will ensure that the query is executed when the application is loaded.
2. Load the data on the table
Now, we will load the data on the table. For this, we will use the getRecords
query that we created in the previous step. Drag the table component from the right sidebar and drop it on the canvas.
On table properties, go to the table data property and set the value to {{queries.getRecords.data}}
. This will load the data from the getRecords
query on the table.
Run the query and you should see the data loaded on the table.
3. Enable bulk row selection on table
Now, we will enable the bulk row selection on the table. For this, go to the table properties and enable the Bulk selection option. Enabling this option will allow you to select multiple rows on the table. This option is disabled by default.
4. Create a custom javascript query
Now, we will create a custom javascript query that will generate a SQL statement to delete the selected rows from the table component based on a list of selected IDs, assuming the IDs are stored in the id column and that the name of the table component is table1. The actual database name should be replaced with tooljet as indicated in the SQL statemnent in the code below:
const uniqueIdentifier = "id";
const idsToDelete = Object.values(components.table1.selectedRows).map(dataUpdate => dataUpdate[uniqueIdentifier]);
const idsString = idsToDelete.map(id => `'${id}'`).join(', ');
const SQL = `DELETE FROM tooljet WHERE ${uniqueIdentifier} IN (${idsString});`;
return SQL;
If you click on the Preview button, you should see the SQL statement generated by the query:
Now, let's select a few rows on the table and then preview the SQL query generated by the javascript query: