如何根据外键每行具有不同状态的状态从父表和子表中获取数据 [英] How to get data from parent and child table on the basis of status where foreign key have different status for every row

问题描述

我有 2 个具有外键关系的表.情况是我有一个 case 和一个 case 有很多 revision 并且每个 revision 都有自己的 状态.如果仅更改外键表状态的特定行，我想获取父表数据和子数据

I have 2 tables with foreign key relationship. Situation is I have a case and a case have many revisions and every revision have its own status. I want to get parent table data and child data if only specific row of foreign key table status is changed

表格案例

id      case_no    patient_name  age
1       12564        abc         78
2       1256         lkj         63
3       125          bdhf        23

表格Case_Revisons

    id      case_id    revison         status
    1         1           0           assesemnt
    2         1           1           assesment
    3         1           2           treatment
    4         2           2           assesment
    5         3           1           assesment

我想要的是来自 Case 和 Case Revisions 表的所有数据，其中状态为 treatment

What I want is all data all data from Case and Case Revisions table where status is treatment

我尝试了什么:

$data['treatment_setup_cases'] = MedicalPrimaryCases::with('primaryCaseNo')
            ->where('impression_type', 1)
            ->where('status', 'treatment-setup')
            ->get();


 public function primaryCaseNo()
    {
        return $this->belongsTo(PrimaryCaseNo::class, 'primary_medical_case_id');
    }

推荐答案

在此处使用连接:

$the_data_you_want = Case::join('case_revisions', 'case.id', '=', 'case_revisions.case_id')
    ->where('status', 'treatment')
    ->get();

PrimaryCaseNo 和 impression_type 在问题的解释中没有提到，所以我忽略了它们，如果需要，你可以改变它.

PrimaryCaseNo and impression_type were not mentioned in the explanation of the question so I ignored them, you can change this if necessary.

希望对你有帮助.

这篇关于如何根据外键每行具有不同状态的状态从父表和子表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！