[Question / Sugestion] Is it possible to append/query to an Array DataType?

[tiagoalves83]

Example:

A doctor can have none or many specialties:

• general practitioner
• orthopedist
• pediatrician
• obstetrician

$doctor1->setMeta('specialities', 'general practitioner')
$doctor1->appendToMeta('specialities','obstetrician') // How to append ?

$doctor2->setMeta('specialities', 'pediatrician')
$doctor2->appendToMeta('specialities','obstetrician') // How to append ?

How to query for all 'obstetrician' if the meta key 'specialities' is an Array converted to a json in the database ?

[frasmage]

Hi @tiagoalves83,

// How to append ?

As you noted, arrays get encoded to json, so in order to append to one, you would need to load up the array, modify it, then write it back to the database.

$array = $doctor1->getMeta('specialities');
$array[] = 'obstetrician';
$doctor1->setMeta('specialties', $array);

How to query for all 'obstetrician'

The Metable package is reasonably well optimized to filter queries by meta keys. However, querying by meta value does not scale particularly well, particularly so for trying to partial match on complex data types like arrays or objects that are serialized in the database (e.g. if you have thousands of doctors, MySQL would need to scan every 'specialties' row for every operation).

Generally speaking, if you are dealing with a many-to-many relationship where you need to query the data by the values of both sides of the equation, then Metable is likely the wrong tool for the job. For this I would recommend a proper pivot table between two entities.

[tiagoalves83]

@frasmage thanks.