Storing a dynamic array and string

Question

I have a website where users can transform a text document and save it to what I call a field. One text document can have a dynamic amount of fields, thus making it hard for me to create a relational database design.

Consider below setup.

My model Field.php have below fields table.

id | name       | type
-----------------------
1  | invoice_no | text
2  | addresses  | table

I also have a table where I save the field result for a specific document:

On model Result I have a $casts type:

protected $casts = [
    'content' => 'array'
];

This is my results table:

id | field_id | document_id | content
-------------------------
1  | 1        |  32         | #81724
2  | 2        |  32         | [{"0": "Parkway", "1": "Broadway"}, {"0": "Avenue St.", "1": "Main St."}]

The data from the table is simply being read from a .json file.

Now ultimately, when all fields for a specific document (in this case 32) is entered in my database, I would like to send a webhook with the field data, such as:

{
  "invoice_no":"#81724",
  "addresses": [{
       "0": "Parkway",
       "1": "Broadway"
   },{
       "0": "Avenue St.",
       "1": "Main St."
   }]
}

So above is what I have visioned - but I am not sure if this is the best approach?

The $casts on my Model returns the content as an array - but as you can see for results.content, it can be both a string and an array.
How should I store the content in my database? I cannot make the column as a JSON type, if I also want to store strings.

Another approach?

Would it make more sense to have two columns on the results table for the content, so it becomes:

id | field_id | document_id | text    | array
----------------------------------------------
1  | 1        |  32         | #81724  | NULL
2  | 2        |  32         | NULL    | [{"0": "Parkway", "1": "Broadway"}, {"0": "Avenue St.", "1": "Main St."}]

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

The first approach is fine, you need to override the getCastType method as mentioned as an answer for the following question Laravel 5 Eloquent, How to set cast attribute dynamically. So you can adopt different types. For the field data type depends on the maximum expected length, so it may be either a Varchar or Text (assuming that all data types you store in this field are text based)

Update:

assuming that you expecting two types string and table, and you want the table type to be cast to array, you may override the getCastType method as following:

protected function getCastType($key) {
      if ($key == 'content' && !empty($this->field->type)) {
        switch($this->field->type) {
            case 'table':
                return 'array';
            break;
            case 'string':
                return 'string';
            break;
            default:
                return parent::getCastType($key);
        }
      } else {
        return parent::getCastType($key);
      }
    }

and to be able to get access to the field type using $this->field->type, you need to implement the relationship in the result model as following:

public function field() {
    return $this->belongsTo('App\Field');
}

All the Best

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 11, 2019 at 5:34

Omer Abdelmajeed

3641 silver badge10 bronze badges

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

2 Comments

oliverbj Over a year ago

Thank you for that link - was not aware that you can set the cast type dynamically. Would it be an option to say that if the $field->type === 'table' then the cast should be array, and if not, it should be a string?

Omer Abdelmajeed Over a year ago

my pleasure, yes you can say $field->type === 'table' then the cast should be array

AmigoJack · Accepted Answer · 2019-07-10 14:33:00Z

0

By far the easiest and ugliest way to do this is to add a BLOB column to your database (tables) and just store the JSON there.

What you could do is send the results from the table as arrays to the backend and process it there using [] on the names of your input fields.

Please post how you get the data from the table so I can assist you further and someone will probably tell you how you can get this working.

edited Jul 10, 2019 at 14:33

AmigoJack

6,4232 gold badges19 silver badges36 bronze badges

answered Jul 10, 2019 at 12:20

Teun Vos

5845 silver badges9 bronze badges

1 Comment

oliverbj Over a year ago

Updated my question - the data (both string and text) is coming from .json files. So for each field, I read the content of a json file and saves the content to my database. If needed, I can change the structure/content of the json files.

Collectives™ on Stack Overflow

Storing a dynamic array and string

Another approach?

2 Answers 2

Update:

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Another approach?

2 Answers 2

Update:

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related