CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5.5
Katerou22
Katerou22

Reputation: 807

Auto Increment in pivot not by rows count

I have a pivot table named order_user and I want to set an auto increment in this table, but I want to update this column by each user_id, and not by the row count in the table.

Something like this:

user_id |order_id|count|
--------|--------|-----|-----
    1   |   2    | 1   |
    2   |   4    | 1   | <---
    5   |   2    | 1   |
    1   |   6    | 2   | <---
        |        |     |

I want Laravel or MySQL to do this for me, not by custom query attempt!

Upvotes: 1

Views: 316

Answers (1)

Mauricio Florez
Mauricio Florez

Reputation: 1132

You need 2 session variables to control the flow of the query, one to know if your user change and two for counting the orders.
You need to change the table name "orderbyuser"

SELECT   
  user_id,
  order_id,
  orderCount
FROM
  (SELECT 
    user_id,
    order_id,
    IF(@userId<>user_id, @countId:=1, @countId:=@countId+1) orderCount,
    @userId:=user_id
  FROM 
    orderbyuser, #change table name
    (SELECT @countId:=0, @userId:=null) c
  ORDER BY
    user_id,
    order_id) data

Upvotes: 1

Related Questions