Good day. I am building an API, in which I want to return some data. I have three tables

1. counsels
2. counsel_cases
3. analysis_sc

A section of the counsels table is shown below

A section of the counsel_cases is also shown below

Finally, a section of the analysic_sc is shown below.

I want that when a counsel is selected, through the counsel_id, I can fetch the cases belonging to the counsel from the counsel_cases, and then with that information, I want to be able to fetch the number of cases of such a counsel belonging to a legal head (area of practice) on the third table as shown in the design below.

How will that be possible. I have a relationship between counsel and counsel_cases though. Also, I have tried using a foreach loop as shown below, but I am unable to get the unique values of the legal_head.

    public function getCounselPracticeAreas(Request $request)
    {
        $counsel_id = $request->route('counsel_id');
        $cases = CounselCase::select('suit_number')->where('counsel_id', $counsel_id)->get();

        $data = [];
        foreach ($cases as $case) {
            $values = AnalysisSc::select('legal_head')->where('suitno', $case->suit_number)->first(); 
            array_push($data, $values);
        }

        return response()->json([
            "message" => "successful",
            "data" => $data
        ]);
    }
However, this is the value I get
[![enter image description here][5]][5]

I want to get something like this:

$data : [
            "legal_head" : [
                "name" : "Criminal Law",
                "count" : 2
             ]   
       ]

Please, is this possible I know this is quite long. And I hope I explained myself well. Thanks

CodePudding user response：

you need to use SQL Joins to create a relationship between the tables and fetch the data

CodePudding user response：

It looks like you're skipping using relationships in your models. Once you have your models set up, retrieving the data you need will be a lot easier with Laravel. You may want to separate your models/tables a little more and add relationships, like the following:

Models/tables:

Counsel (counsels)

• id
• name

LegalHead (legal_heads)

• id
• name

Case (cases)

• id
• suit_number
• year
• subject_matter
• legal_head_id

case_counsels (This is not a model, just a relationship table)

• id
• case_id
• counsel_id
• appearing_for
• role

Note: I wasn't sure how your data is structured. You can use this as start and adjust as necessary.

Relationships

The Many-Many relationship is suitable for the case counsels and you'll be able to add the role as a pivot(extra field) for the relationship.

https://laravel.com/docs/8.x/eloquent-relationships#many-to-many

Case

• legal_head: belongsTo
• counsels: belongsToMany with pivot for role

Counsel

• cases: belongsToMany with pivot for role

Retrieving the Objects

When that is set up, you won't have to do as much query work. You can do this to get case data with counsels, legal_head and their role for each case:

$cases = Case::with('counsels','legal_head')->get();

And this to get the Legal Head names with the number of cases:

$legal_heads = LegalHead::withCount('cases')->get();