Get old column values of different associated rows dynamically(in laravel)

Question

i have a vehicle table. Vehicles are transferable. When a vehicle is transferred to the new owner, another row is added to the table with different details. Old vehicle's vehicles_transfer column is filled with the id of that new row.

Vehicles can be transferred multiple times. Here's the vehicle table:

+--------------------+------------------+------+-----+---------------------+----------------+
| Field              | Type             | Null | Key | Default             | Extra          |
+--------------------+------------------+------+-----+---------------------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| vehicles_vin       | varchar(255)     | NO   | UNI | NULL                |                |
| vehicles_model_id  | int(11)          | NO   |     | NULL                |                |
| vehicles_transfer  | int(11)          | YES  |     | NULL                |                |
+--------------------+------------------+------+-----+---------------------+-----------------

Now i'm stuck at a point. I have a vehicle which is transferred 5 times.

When it was registered for the first time, its id was 5 and it was transferred to a new owner having vehicle table id 7. So vehicles_transfer column was filled with 7. vehicles_transfer column says that this vehicle is transferred to the filled id.

Then vehicle id 7 was transferred further to id 12, So, its vehicles_transfer column was filled with 12.

This process has happened 3 more time.

Now i have the id of the current vehicle and i want to know how many times this vehicle has been transferred previously. i have also made a function getOldVehicleId() which takes id of the vehicle and provides the vehicles_transfer value.

But i'm not getting how to get all old vehicles_transfer values?

Edit: Basically i want to make this dynamic

$oldVehicle1 = Vehicle::withTrashed()->where('vehicles_transfer', $id)->first();

if($oldVehicle1){
    $oldVehicleIds[] = $oldVehicle1->id;
    $oldVehicle2 = Vehicle::withTrashed()->where('vehicles_transfer', $oldVehicle1->id)->first();

    if($oldVehicle2){
        $oldVehicleIds[] = $oldVehicle2->id;
        $oldVehicle3 = Vehicle::withTrashed()->where('vehicles_transfer', $oldVehicle2->id)->first();

        if($oldVehicle3){
            $oldVehicleId[]s = $oldVehicle3->id;
            $oldVehicle4 = Vehicle::withTrashed()->where('vehicles_transfer', $oldVehicle3->id)->first();
        }else return $oldVehicleIds;
    }else return $oldVehicleIds;
}

If a row is added everytime when the vehicle is transfered, counting the rows will give the transfer count?. Wont it? — Jithin Shaji
It wont. All vehicles are present in the vehicle table(including all other vehicles). — Kanav

lukaszwierz lukaszwierz · Accepted Answer · 2014-08-14T06:43:19

from all you said there is no such way in Your design. There is no info about who had this car in past. In my opinion this design is not very good. You are duplicating vehicles data over and over again and in the same time You are loosing info about its original ID. You should keep data that are not changed unique. My best idea would be table of ownership where you will keep user_id and vehicle_id and dates of ownership. From this point you can either in this table have some status column and date of ending ownership or you can create table with ownership_history (depending on how large tables will be in future).

OK. So in Your design to count transfers You would have to get present vehicel id. Look for vehicle_transfer with this id, take this vehicles id and look for it in transfer column, and so on. But only if there would be no duplicated transfer values. So like I said , Your design is not good.

If You persist on going Your way and want to go with Your desing. Use recursive function. soemtihn like this:

function getOldVehiclesIds(&$oldVehicleIds,$id)
{
    $oldVehicle = Vehicle::withTrashed()->where('vehicles_transfer', $id)->first();
    if($oldVehicle){
        $oldVehicleIds[] = $oldVehicle->id;
        getOldVehiclesIds($oldVehicleIds,$oldVehicle->id);
    }
    else
    {
        return $oldVehicleIds;
    }
}

$oldVehicleIds = array();
$oldVehicleIds = getOldVehiclesIds($oldVehicleIds,$id);

You might need to tweek it a bit. But i hope You will get the concept.

Get old column values of different associated rows dynamically(in laravel)

1 Answers