How to query this

I am working in a project.for now lets say i have 3 tables "districts", "crimes" , "cases". Cases table holds all the registered cases.

Case.php

class Case extends Model
{
        protected $table = 'cases';
        protected $fillable=['person_name','crime_id ,'district_id','crime_date'];

         public function crime()
    {
          return $this->belongsTo('App\Crime');
     }

}

District.php

class District extends Model
{
        protected $table = 'districts';
        protected $fillable=['name'];//name of districts like "A district","B district" etc

     public function cases()
    {
                return $this->hasMany('App\Case');
      }
    


}

Crime.php

class Crime extends Model
{
        protected $table = 'crimes';
        protected $fillable=['name'];//name of different types of crime like rape,murder etc

}

My output at the end of month should look like below in report.blade.php

  District name   |total crime    | crime
   A                           3                   rape
                                                            murder
                                                attempt to murder

   B                         1                    rape

  C                        0                    No crime reported

ReportController.php

public function index(){
     
      $districts = District::with(['cases.crime' => function ($query) {
        $query->whereMonth('crime_date', date('m')) 
            ->whereYear('crime_date', date('y'));
    }])->get();

     return $districts;

        return view('case', compact('districts'));
 
}

I think group by district_id can give number of crime in that district Id I need to fetch district with total crimes reported and types of crime for a month Please help me

tykus

8 years ago

Level 104

You are not showing relationships on any of your models, e.g.

a district has many cases

public function cases()
{
    return $this->hasMany(Case::class)
}

a case belongs to a crime

public function crime()
{
    return $this->belongsTo(Crime::class)
}

In the controller, you can eager load the cases and crimes with the districts:

public function index(){
    $districts = District::with(['case.crime' => function ($query) {
        $query->whereMonth('crime_date', date('m')) 
            ->whereYear('crime_date', date('y'));
    }])->get();

        return view('case.report', compact('districts'));
}

// view template

@foreach($districts as $district)
    <tr>
        <td>{{ $district->name }}</td>
        <td>{{ $district->cases->count() }}</td>
        <td>
            @foreach($district->cases as $case)
                {{ $case->crime->name }} <br>
            @endforeach     
        </td>
    </tr>
@endforeach

1 like

david001

8 years ago

Level 5

@tykus I have updated according to yours but i got error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'crime_date' in 'where clause' (SQL: select * from `crimes` where `crimes`.`id` in (1, 2, 3, 4) and month(`crime_date`) = 12 and year(`crime_date`) = 17)```


its looking ```crime_date ```in ```crimes``` table, i  have ```crime_date``` in ```cases``` table

tykus

8 years ago

Level 104

Given this a try instead:

 $districts = District::with([
    'cases' => function ($query) {
            $query->whereMonth('crime_date', date('m')) 
                    ->whereYear('crime_date', date('y'));
        },
    'cases.crime'
])->get();

david001

8 years ago

Level 5

doing this i got only name of district with zero crime ,although i have some crime in every district

 $districts = District::with([
    'cases' => function ($query) {
            $query->whereMonth('crime_date', date('m')) 
                    ->whereYear('crime_date', date('y'));
        },
    'cases.crime'
])->get();

i got this in controller writing above code

[
{
id: 1,
name: "A",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00",
cases: [ ]
},
{
id: 2,
name: "B",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00",
cases: [ ]
}
]

But writing this

 $districts =  District::with('cases.crime')->get();
return $districts;

i got this

[
{
id: 1,
name: "A",//name of district
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00",
cases: [   //registered cases
    {
    id: 1,
    crime_id: 1,
    district_id: 1,
    crime_date: "2017-12-31 00:00:00",
    created_at: "-0001-11-30 00:00:00",
    updated_at: "-0001-11-30 00:00:00",
crime: {//name of crime from crimes table
id: 1,
name: "Rape",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00"
}
},
{
id: 3,
crime_id: 4,
district_id: 1,
crime_date: "2017-12-31 00:00:00",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00",
crime: {
id: 4,
name: "Women trafficking",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00"
}
},
{
id: 4,
crime_id: 3,
district_id: 1,
crime_date: "2017-12-31 00:00:00",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00",
crime: {
id: 3,
name: "Murder",
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00"
}
}
]
},
{
id: 2,
name: "B",//name of district from districts table
created_at: "-0001-11-30 00:00:00",
updated_at: "-0001-11-30 00:00:00",
    cases: [   
    {
    id: 2,
    crime_id: 1,
    district_id: 2,
    crime_date: "2017-12-31 00:00:00",
    created_at: "-0001-11-30 00:00:00",
    updated_at:  "-0001-11-30 00:00:00",
    crime: {
    id: 1,
    name: "Rape",
    created_at: "-0001-11-30 00:00:00",
    updated_at: "-0001-11-30 00:00:00"
}
}
]
}
]

I want to show total crime and name of crime on particular district at the end of month For eg:->total crime in "A" district was 3 and name of crime are rape,murder,traficking on month december ->total crime in "B" district was 1 and name of crime are "rape" on month december //etc for 12 month in each district

Simillarly total crime in "A" district was 4 and name of crime are rape,murder,traficking,violence on month january

Please or to participate in this conversation.