1

I have a table with invoices. Each invoice belongs to a user.

So the table table has id, user_id columns.

I want a third column, for example invoice_id that is autoincrement based on the user.

So for example user 1 will have invoices with id 1,2,3,4,5,6,7,8... And user 2 will also have invoices with id 1,2,3,4,5,6,7,8...

I know I can make a function that checks the last value for a specific user and assign the next but is there no other "easier" more proper way to do this with cakephp or mysql?

Fazal Rasel
  • 4,446
  • 2
  • 20
  • 31
user3504209
  • 41
  • 1
  • 4
  • Maybe this is what you are looking for? http://stackoverflow.com/questions/979709/what-is-the-equivalent-to-getlastinsertid-in-cakephp – Duikboot Apr 07 '14 at 07:50
  • Depending on the database engine you're using, you might be able to take avantage of a composite primary key with autoincrement - http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html - though I believe this is only available with MyISAM and BDB engines for MySQL.... You don't specify which database you're using, so I'm guessing MySQL – Mark Baker Apr 07 '14 at 08:06
  • I saw this method as well, but I prefer to not use a method that is specific for only MyISAM – user3504209 Apr 07 '14 at 08:30
  • There is no reason whatsoever, except aesthetic one, where you want to have "nice" sequential numbering *based on user*. However, since people are people and we often like useless things - you perform this using AFTER INSERT triggers on the invoice table. As for how, there are tons of examples on how to use MySQL triggers. However, believe me when I talk from experience - you don't need nor want that. – N.B. Apr 07 '14 at 09:12
  • Well, it is needed, because each user can view their invoice and invoice id. And I do not want the user to see ids like 1,4, 9,16.. – user3504209 Apr 07 '14 at 09:53
  • There you go, there's no use except aesthetic one. Whether the ID is 1 or 1021031231 is absolutely irrelevant - it's a number, it works the same in the end except you *think* that it looks nicer to the user. Implementing this **safely** is a completely another thing and since you have virtually no gains and you impose faults (it's actually not that simple to implement it safely as you might think at first, I learned the hard way). – N.B. Apr 07 '14 at 10:41

2 Answers2

0

you have to do it in a beforeSave callback in your Invoice Model

something like:

public function beforeSave($options = array())
{

    if(!$this->id)
    {
        $user_id = $this->data['Invoice']['user_id'];
        $invoice = $this->find(
            'first', 
            array
            (
                'fields' => array('MAX(invoice_id) AS invoice_id'),
                'conditions' => array('Invoice.user_id' => $user_id )
            )
        );
        $invoice_id = $invoice [0]['invoice_id'] + 1 ;


        $this->data['Invoice']['invoice_id'] = $invoice_id ;
    }
    return true;
}

The above code should work. Anyway somehow I'm not sure this is the best way to achieve that, so keep lookink for other solutions.

arilia
  • 9,373
  • 2
  • 20
  • 44
-1

In your Schema add

$t->addColumn('id', [
    'type' => 'integer',
    'autoIncrement' => true,
])
user3505541
  • 25
  • 2