This does not necessarily apply only to the Laravel php framework as I believe my intended tasks can be processed on the MySQL server itself as well with appropriate SQL statements.
There are 3 tables as following:
users (>2 million rows)
- id
- name
- balance (decimal, 14, 2)
- package_id
packages (5 rows)
- id
- name
- value (decimal, 14, 2)
- percent (decimal, 5, 2) - stored as 0.10 instead of 10
transactions (log)
- id
- user_id
- description
- amount
All of the tables above are linked to the objects/models User, Package, and Transaction respectively. They contain timestamp columns and are inserted/updated automatically by Laravel.
The application is scheduled to update the column balance in table users based on the value of package (that is assigned to the user) multiplied by percent field plus the original balance value, every day at midnight 12:00am. A clearer expression would be:
users.balance = users.balance + (packages.value * packages.percent)
At the same time, a transaction log for each balance update should be inserted to the database:
user_id: [user's id]
description: Updated user [user's name] with new balance
amount: [amount added]
I have managed to achieve the first operation with the following SQL statements:
UPDATE users
INNER JOIN packages
ON users.package_id = packages.id
SET users.balance = users.balance + (packages.value * packages.percent);
I can't seem to find any solution to achieve both operations at the same time with highest efficiency and transaction-safe. I could possibly retrieve both users and packages and process them in the application then do UPDATEs and INSERTs, but it'd be a no-brainer thing to do as it's really inefficient.
I'd appreciate if someone could help me with the solution!
I don't see any possible way to update or insert on two different tables in mysql. I know of updating 2 different tables with one query but not insert and update on 2 different tables. I think you can use triggers to achieve what you want
CREATE TRIGGER insert_into_logs_when_user_balance_update
AFTER UPDATE ON users
FOR EACH ROW
IF NEW.balance <> OLD.balance
then
insert into logs(user_id, description, amount)
values(New.id, New.balance, New.balance)
END IF
This will update logs tables when users.balance changes/update.
Hope this helps.
Luke You want to update user record and concurrently insert log record both with same query. You should check Mysql duplicate key update. I suggest to check this link. https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html http://www.digimantra.com/tutorials/insert-update-single-mysql-query-duplicate-key/