Use two columns auto increment ( laravel 4.1)

Question

I have these tables

Hotels:
id = auto increment
name_hotel = text
....

Rooms:
id = auto increment
id_room = auto increment (just in same hotel)
hotel_id = integer
...

In room table I have three columns (id = auto increment , id_room = I want to make this column auto increment but just in 1 hotel , every hotel add room the id_room will be 1 , 2 ,3 ,4 )

Example:

id            id_room              hotel_id
1             1                    4
2             2                    4
3             3                    4
4             1 (start again)      5(new hotel)
5             4                    4
6             2                    5

It possible to make it in laravel model Eloquent

Answer 1

Well, if you know the hotel ID when you insert your data, then you can set the id_room to be one more than the highest ID room of that hotel.

First, remove the auto increment from id_room. Auto increment is intended to be used with primary keys or on rows whose IDs should always increment. It is never a good idea to try and reset the counter and as far as I know Laravel does not give you the option. But by removing the auto_increment, we can create one ourselves that does what you need.

To do so, we can use Eloquent to select the max(id_room) where hotel_id=?:

$id = 4; // or whatever ID you are inserting
$current_id = DB::table('rooms')->where('hotel_id', $id)->max('id_room');

Then, just add one to the current_id when you insert your data

$row = DB::table('rooms')->insert(
    ['id_room' => $current_id + 1, 'hotel_id' => $id];
);