How to store address in database?

302
April 12, 2017, at 3:40 PM

I have laravel application where user can select address

area
region
city
locality
street

Every field is simple integer (foreign key to table which contains all addresses with their parent-child relationship). For example, area test has 8 regions and 2 cities.

I want to store this info in mysql database, but there are some problems.

There is a lot of code which works fine and process all updates to all models (stores info about what was changed with diffs of updates).

If I store these fields if 5 different columns, then I have to add some code in different parts of application to process it (select, update, show), because this code relies upon field type (text, integer, select, etc., there is separate config which describes these columns).

I want to store it in json column like this

{"area": 10, "city": 335}

Then I can easily add new columns, but what about speed of this solution, because these fields will be used in filter and there will be at least 50k rows in table.

Answer 1

Use the Laravel $casts parameter on your model see here:

protected $casts = [
    'address' => 'array',
];
$record = SomeModel::find(1);
// $address is an array...
$address = $record->options;
// address is automatically serialized back to JSON...
$user->address = ["area" => 10, "city" => 335];
Answer 2

If you have filters over your address then its HIGHLY recommended to make them in separate columns, even if they require modification in various places in your application.

And by filter i mean filtering the result while making the query like you want to get rows where area is 10 and so on.

Although if you are at liberty of filtering data after retrieval from database then you can very well use datatype text to store json string in database and after retrieving it from database, make json_decode and filter it out.

This last case is actually beneficial when you are taking out result in pagination without showing total number of found results.

In that case you can query your database for every 10 rows( or whatever number of results you wanna show per page), filter them after retrieval and then fetch more if it doesnt meet the limit. Keep track of offset for further queries.

Rent Charter Buses Company
READ ALSO
Real time notifications in Laravel 5

Real time notifications in Laravel 5

I'm currently working on notification system in Laravel,Can anyone suggest me best tutorials or documentation for real time Notifications in Laravel

363
incorrect parameter in FORMAT function

incorrect parameter in FORMAT function

i'm trying to use this query in mysql database:

445
Better way to get count in single column based on two conditions in MySql

Better way to get count in single column based on two conditions in MySql

I am trying to write a query for following problem statementI want to write a query where I can find Valid and Invalid visa

235