aylara's avatar

How do I implement "insert into ... on duplicate key update...' on a many-to-many pivot table

So I have a many-to-many relationship from a model to another. The underlying (pivot) MySQL table has a counter attribute (column) along with the 2 foreign keys. The table also has a “unique” index on the 2 foreign keys.

I want to be able to add an association (record) on this table but where it exists, increment the counter by 1. I have raw SQL queries that do this from an old app but want to achieve same with Eloquent. Having read about “attach” and “sync” here, I’m still not clear how to go about this.

Any help would be appreciated.

0 likes
4 replies
aylara's avatar

@vincent15000: Below, "consolidated" is the pivot table.

INSERT INTO consolidated (`date`,salesman_id,product_id,amount,total_amount) VALUES ('2021-01-01', 4,10,15000,60000) ON DUPLICATE KEY UPDATE consolidated.amount = consolidated.amount + VALUES(amount), consolidated.total_amount = consolidated.total_amount + VALUES(total_amount);
2 likes
vincent15000's avatar

@aylara I don't see the counter field in your query. Can you show the table structure please ?

aylara's avatar

@vincent15000 Please take the "amount" field to be the counter field. You will notice it's incremented in the SQL code above (incremented by itself rather than 1)?

In posting my question, I thought to make it as simple as possible to focus on the core challenge. Whatever "insert ... on duplicate key update" logic solves the increment by 1 will also solve the actual situation above.

If there is no Eloquent way, I will use Query Builder "upsert". Just preferred to use Eloquent.

Please or to participate in this conversation.