php - 从逗号分隔的 JSON mysql 字段查询报告

Question

这是我的第一个问题。如果我不符合要求的规则，我深表歉意。我已经尽力了

问题--我对正在进行的项目有疑问。它是基于医学的。我有这三个表需要从中获取报告数据库结构--

diseaseTable which has columns
diseaseID | diseaseName
diseaseID stores an integer i.e 1, 3, 4,..
dieseaseName stores name of disease e.g Hypertension, Malaria, ..e.tc

我还有 patientsTable，其中包含这些关键列

patientID - which is an autoincrement int
patientName - name of patient
created_at - date and time of registration of the patient

最后是治疗表，其中包含

treatmentID -autoincrement int
patient - contains the id of the patient which is a foreing key of patientsTable
created_at and updated_at for timestamps
diseaseTreated (which is my column of interest) Stores values in json comma separated fields. 

我已经检查了 https://stackoverflow.com/search?q=mysql+comma+separated+fields 的所有结果但似乎没有一个能解决我的问题。我已经检查了重构 mysql 模式的方法，但是我会有太多的表来存储每个病人诊断出的疾病。此外，一名患者可能被诊断出患有多种疾病，并且可以随时添加一种疾病。我可能有将近 100 个。如果高血压的 ID 为 1，关节炎的 ID 为 2，则同时患有这两种疾病的患者将具有
diseaseTreated 字段，治疗表中有“1”、“2”


预期的解决方案--我需要第一次或第二次治疗某种特定疾病的报告，例如再次治疗同一疾病(如果我想知道有多少高血压病例是第一次，以及如何许多高血压病例是反复发作的)

示例 - 新案例报告

Disease | New Cases
Hypertension | 4

示例 - 复发病例报告

Disease | New Cases
Hypertension | 1
Arthritis    | 2

我正在使用 Laravel 我已经尝试了很多解决方案，但我发现这太接近我需要的了:他们因为他们没有产生任何重要的东西。

$diseaseID
$countedDiseaseCases = DB::select('select * from treatmentTable where JSON_CONTAINS(diseases, \'[\"'.$diseaseID.'\"]\')')

这将使我获得具有特定疾病的行，并且通过在结果上使用 php count() 我得到一个 int as 3，这没问题，但我不必像我期望的那样把它过滤并区分新的和复发病例。

谢谢，任何建议将不胜感激

Answer 1

如果 deseaseTreated 列是 json，你不能添加一个 isNew 键，当病人来第二次就诊时，将该键设置为 false 或类似的东西吗？

现在当你想做报告时，你可以得到所有这些的计数。

//example deseaseTreated value 
{
  "deseases": [
    {
      "deseaseID":"Value", 
      "isNew":Boolean,
      ...
    }, 
    {...}
  ]
}
$records = DB::select('select * from treatment');
$diseases = DB::select('select * from diseases');
foreach ($diseases as $disease) {
    $disease['count_new'] = 0;
}
foreach ($records as $record) {
    $json = json_decode($record['diseaseTreated']);
    foreach ($json['diseases'] as disease_index) {
    if ($disease_index['isNew']) {
        foreach ($diseases as &$d) {
            if ($d['diseaseID'] == $disease_index['diseaseID']) {
                $d['count_new']++;
            }
        }
    }
}