Laravel 5.8 update mysql json column cast as array changes data stored format

Question

I have a table with a json column cast as array. The Schema creation has the column defined like this:

$table->json('row_ids');

In my class:

protected $casts = [
    'row_ids' => 'array',
];

I generate the data in this array / column by getting every row id from another table like this:

$id_array = DB::table($table->name)->pluck('api_id')->toArray();

TableChannelRow::create([
    'table_api_id' => $table->api_id,
    'channel_api_id' => $channel->api_id,
    'row_ids' => $id_array,
]);

When I dd a collection record I can see the columns in the target table OK, with one of the columns containing an array as expected:

#attributes: array:4 [▼
  "api_id" => 2
  "table_api_id" => 1
  "channel_api_id" => 6
  "row_ids" => "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42,  ▶"
]

When I check in MySQLWorkbench the data looks like this:

In another controller I want to add or remove entries from the array in this column like this:

$table_channel_row = TableChannelRow::where('table_api_id', '=', $rowId)
    ->where('channel_api_id', '=', $channelId)
    ->first();

$row_ids = $table_channel_row->row_ids;

if ($is_checked == 'yes') {

    // Add value to array if it does not already exist
    if (!in_array($row_id, $row_ids)) {
        array_push($row_ids, $row_id);
    }

} else {

    // Remove value from array
    $row_id_array[] = $row_id;
    $row_ids = array_diff($row_ids, $row_id_array);

}

$table_channel_row->update([
    'row_ids' => $row_ids,
]);

Now the data in MySQLWorkbench looks like this:

Why does it get stored looking like a PHP array in the first instance, then later on update it gets stored as a json object?

Additionally the remove PHP code is working, yet the add is not, though it does not trigger an exception (you can see the first value is removed in the second image, but I cannot find it in the object in MySQL when I trigger the code to add it)

What did I miss? Thanks!

I don't believe MySQL has support for Array column. So the `"[1, 2, 3, 4, 5,...]" is invalid. — Francinaldo Almeida
– Francinaldo Almeida, Commented Apr 17, 2019 at 13:55
Hmm thanks. Understand. By Laravel is casting the data in this format when I generate the array in the first place this way. Did I mess up how I first generate the data? It seems to use it OK on the second controller — TheRealPapa
– TheRealPapa, Commented Apr 17, 2019 at 13:58
When you first created TableChannelRow, you set row_ids to an array, you should have set it to a json encoded version of the array. — Francinaldo Almeida
– Francinaldo Almeida, Commented Apr 17, 2019 at 14:00
@Chin Leung hey! Thanks! See the third code snipped above. I pluck the IDs to array then store them in the column, which was created as json. — TheRealPapa
– TheRealPapa, Commented Apr 17, 2019 at 14:01

Chin Leung · Accepted Answer · 2019-04-17 14:26:09Z

1

The reason why it's saving differently is because array_diff returns an associative array whereas your initial data is an indexed array. Take the following for example:

$ids = [1, 2, 3, 4, 5];
$ids2 = [1, 2, 3];

Then if you perform an array_diff($ids, $ids2), it would return the following:

[
    3 => 4,
    4 => 5
]

So if you want to save as the same format as your initial one, you have to retrieve the values of the array using array_values:

$row_ids = array_values(array_diff($row_ids, $row_id_array));

edited Apr 17, 2019 at 14:26

answered Apr 17, 2019 at 14:12

Chin Leung

15k4 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TheRealPapa Over a year ago

You ROCK! Thank you! So now the stored format after removing a value is per first image (PHP array). But why is the push to add the value back not working do you think?

Chin Leung Over a year ago

@TheRealPapa Np! What's not working for the push to add?

TheRealPapa Over a year ago

I cannot see the value added back to the array that is stored in MySQL. Thanks for helping out! I learned quite a bit in the last two nights!

Chin Leung Over a year ago

@TheRealPapa Np! Did change back your initial value to ->toArray and was your initial data an indexed aray?

TheRealPapa Over a year ago

My initial array (stored) was [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,.... After removing id 1 it becomes [2, 3, 4, 5, 6, 7, 8, 9, 10, 11,... but then adding back value 1 (I am using a toggle with AJAX) the array does not contain value 1. Does this answer your q?

|

Collectives™ on Stack Overflow

Laravel 5.8 update mysql json column cast as array changes data stored format

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related