1

I've got problem with selection from my database.

This is how my query should look like:

  SELECT `deads`.`id`, `graves`.*, `places`.`row`, `places`.`place`, `places`.`id_quarter`, `places`.`occupied` FROM `deads` LEFT JOIN `graves` ON `deads`.`id_grave` = `graves`.`id` LEFT JOIN `places` ON `graves`.`id_place` = `places`.`id` WHERE `places`.`id_quarter` = '1' AND `places`.`occupied` = '1'

My Dead model:

 public function graves()
        {
            return $this->belongsTo(Grave::class,'id_grave');
        }

My Grave model:

public function places()
{
    return $this->belongsTo(Place::class,'id_place');
}

public function deads()
{
    return $this->hasMany(Dead::class);
}

My Place model:

public function quarters()
    {
        return $this->belongsTo(Quarter::class,'id_quarter');
    }

    public function graves()
    {
        return $this->hasOne(Grave::class,'id_grave');
    }

I can't get the idea, how I can write this as eloquent select. I want to bypass future injection problems. Is there any way to get it smoothly or should I leave it as RAW SQL query? How to convert it to an array like I get it when I use eloquent?

wpcpremium80
  • 98
  • 6
  • 1
    how about Dead::with(['gaves.places'])->get(); if you want to filter the relation you can use whereHas etc. – Hamid Ali Dec 12 '20 at 11:04
  • 1
    Here is the link to docs for relations querying https://laravel.com/docs/8.x/eloquent-relationships#querying-relations – Hamid Ali Dec 12 '20 at 11:05
  • I want to select all deads, which grave is in quarter = 1. – wpcpremium80 Dec 12 '20 at 11:11
  • Dead::with(['graves.places'])->get() returns all data from these relations, how I can add to where that clause? – wpcpremium80 Dec 12 '20 at 11:12
  • 1
    have a look at this https://stackoverflow.com/a/36498701/5676711 – Hamid Ali Dec 12 '20 at 11:20
  • Something like this will be valid? $deads = Dead::with(['graves.places' => function($query){ $query->select('id_quarter')->where('id_quarter','=',1); }])->get(); But how to write variable instead int value in this select? It says that variable is undefined. – wpcpremium80 Dec 12 '20 at 11:27
  • 1
    $deads = Dead::with(['graves.places' => function($query) use($variable){ $query->select('id_quarter')->where('id_quarter', '=', $variable); }])->get(); – Hamid Ali Dec 12 '20 at 11:30
  • This query don't filter if it's in specific quarter :( – wpcpremium80 Dec 12 '20 at 11:34
  • It returns all items from Dead. – wpcpremium80 Dec 12 '20 at 11:35
  • What exactly you are trying to achieve? Return all deads from with graves which are located in a quarter? and can you share the DB table structure as well? – Hamid Ali Dec 12 '20 at 12:08
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/225862/discussion-between-hamid-ali-and-wpcpremium80). – Hamid Ali Dec 12 '20 at 12:09

0 Answers0