如何根据外键每行状态不同的状态从父子表中获取数据 [英] 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

表 Case

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 表，其中状态为治疗

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屋！