CODEWITHSUNDEEP
phpmysqllaravel
oliverbj
oliverbj

Reputation: 6062

Storing a dynamic array and string

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?

  1. 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.
  2. 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."}]

Upvotes: 0

Views: 238

Answers (2)

Omer Abdelmajeed
Omer Abdelmajeed

Reputation: 364

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

Upvotes: 1

Teun Vos
Teun Vos

Reputation: 584

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.

Upvotes: 0

Related Questions