Mass Update/Delete

[Andy]

I have a simple table displaying shipment records. I want to add a column of checkboxes for the purpose of mass deletion. The checkboxes have the same name so the result variable will contain a list of selected shipment IDs.
For each shipment record I first need to update it's associated control table record and then only delete the selected shipment records.
Is there a way in CFWheels with models to do mass updates/deletions or would I have to loop the selected record IDs and load the associated control table model, update it, then load the selected shipment record and delete it?
Or would it be best to do an old school query to just mass update the control table records with the WHERE shipmentId IN (1,2,3...) and do a mass deletion (set deletedAt) WHERE id IN (1,2,3...) and do the same for its associated tables?
There has to be a better way.

[Chris Peters]

If you're deleting and updating a ton of records, you definitely don't want to be loading objects for each individual record. That will cause your request to potentially time out or hang for a while.

You can use the model's updateAll() or deleteAll() methods to run mass updates or deletions within single queries.

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfwheels.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Andy]

Perfect! Just what I was looking for.
Thanks!

[Andy]

I just tried the deleteAll() and it did indeed delete the correct records. However, it did not delete the associated records.

In my model I have the relationships defined:
hasMany(name = "invoices", dependent = "delete");
nestedProperties(associations = "invoices", allowDelete = true);

When I do a normal delete() instead of a deleteAll(), it deletes the associated records properly.

I also have a beforeDelete() in the model which calls a function to copy the username to the deletedBy field which doesn't get called when using deleteAll().
Do you know if these things can be done with the deleteAll() or would I loop and call for each record? The maximum deleted at a time is 100 records.

[Chris Peters]

Here are some key differences worth noting:

updateAll() and deleteAll()

- Does not register an object for each record (thus faster)

- Does not run callbacks

- Does not run dependency handling (i.e., nullifying and/or deleting dependent objects)

update() and delete()

- Registers an object for each record (slower when you have a lot of records)

- Runs callbacks and validations

- Runs dependency handling

The biggest factor in your case is speed. If it's only 100 records, you may be able to get away with instantiating them all as objects, looping over the collection, and running update or delete (or whatever) on each object. I recommend grabbing the records as an array of objects by using model("something").findAll(where="something=#something#", returnAs="objects"), that way you're only running one database query.

Another route (which I'd probably do myself) is to create your own method on the model component that calls updateAll() and deleteAll() as needed to manage the dependencies manually. Sure, it's a little rougher to have to do it manually, but at least you'd have it all encapsulated within your model.

[Andy Bellenie]

Depending on your db, you can also setup your foreign keys so that associated records are automatically deleted (CASCADE)

[Jonathan Smith]

Maybe along the same lines, but slightly different:

I have a table full of calculated values, of which a bunch ( 1000+ ) need to be updated every day, (with no table dependencies). 

First, I prep. a nested struct of all the new property values, for all the records that I want to change ( no issue there, a couple of fast fetching findAll's, and some math ). But, my current plan loops over this 1000+ element struct, updates them one at a time ( a brutal n+1 update query loop on it... )

model("ModelName").updateOne(where="id=#theIdToUpdate#",properties=propertiesToUpdateStruct,callbacks=false,reload=true)>

I would use updateAll() ... however each row has different data to be updated by PK, so not a mass-update like DeleteAll(where="color='red'"). Really want to avoid cfquery.

I mostly doesn't work in production reliably, even with the server at idle, it inevitably gets stuck.

java.net.SocketInputStream.socketRead0(null:???)[Native Method]

java.net.SocketInputStream.read(null:???)

java.net.SocketInputStream.read(null:???)

com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1782)

com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:4881)

 - locked <0xe45b9da> (a com.microsoft.sqlserver.jdbc.TDSReader)

com.microsoft.sqlserver.jdbc.TDSCommand.detach(IOBuffer.java:5762)

com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1704)

 - locked <0x3ac9e88> (a java.lang.Object)

com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:1761)

com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(SQLServerConnection.java:1964)

com.intergral.fusionreactor.jdbc.ConnectionSurrogate.rollback(ConnectionSurrogate.java:349)

railo.runtime.db.driver.ConnectionProxy.rollback(ConnectionProxy.java:228)

railo.runtime.db.DatasourceManagerImpl.rollback(DatasourceManagerImpl.java:153)

railo.runtime.tag.Transaction.doCatch(Transaction.java:120)

wheels.model.transactions_cfm$cf.udfCall(\wwwroot\wheels\model\transactions.cfm:64)

[Jacob]

This is probably more of a SQL challenge than a wheels change.  You may want to roll your own sql for this operation. I've has some luck, dealing with similar problems, by disabling transaction logging and indexing, running my updates, then reenabling. Another option is to insert the data into a temp table, then update from that table to your target table. Check out what these smarter guys have to say: http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/450/sql-server-optimizing-update-queries-for-large-data-volumes