Relationship with 3 tables in Standard

Question

I’m trying to make the relationship between three tables in the Latvian. With 2 tables I managed, thanks to the help of the forum, but I’m not able to make it work with 3 tables. the relationship is as follows: I have 3 tables:

News, Photos and Unit (school units)

tabela noticia -> id_noticias

tabela fotos-> id_fotos e id_noticias

tabela noticia_unidade

The relationship news with photos is ok: on the main page appear 2 news and a picture of each. Now I need it to be filtered by unit: On the college page appear only the news from unit 2, for example.

Follow the Models and Controllers:

Controller

public function index(){
    $not_faculdade = Noticia::with(['foto' => function($query){
        $query->get()->first();
        }])
    ->with(['unidade' => function($query2){
            $query2->where('id_unidade','2')->get();
        }])
    ->orderBy('id_noticias','DESC')
    ->take(2)
    ->get();
    // dd($not_faculdade);
    return view('pages_faculdade.noticia')->with('not_faculdade',$not_faculdade);
}

Models

class Noticia extends Model
{
    protected $table = 'noticias';
    protected $primaryKey = 'id_noticias';
    public $timestamps = false;
    protected $dates = ['data'];
    protected $fillable =[
            'texto',
            'titulo',
            'legenda',
            'pasta',
            'subtitulo',
            'evento',
            'titulo_evento'
    ]; 

    public function foto()
    {
        //return $this->hasMany(Foto::class); 
        return $this->hasMany('App\Foto','id_noticia','id_noticias'); 
    }

    public function unidade()
    {
        //return $this->hasMany(Foto::class); 
        return $this->belongsTo('App\Unidade','id_noticias','id_noticia'); 
    }    
}

class Unidade extends Model
{
    protected $table = 'noticia_unidade';
    //  protected $primaryKey = 'id_noticia, id_unidade';
    public $timestamps = false;
    protected $dates = ['deleted_at'];
    protected $fillable = [
        'id_unidade',
        'id_noticia'
    ];

    public function noticias()
    {
        return $this->hasMany('App\Noticia','id_noticias','id_noticia'); 
    }
}

View

@foreach ($not_faculdade as $key=> $not)
    <div class="col-md-6">
        <div class="panel-heading">
            <div class="painel_foto"><img src={{asset('public/'.$not->foto[0]->endereco)}}></div>
            <h4>{{ $not->titulo }}</h4>
            <p align="justify">
                <a href="#" class="noticia">
                {{$texto = substr($not->texto,0,150)." ..."}}
                </a>
            </p>
        </div>
    </div> 
@endforeach

As I said, the 2 news with the "cover" photo usually appear, but the filter does not work. If I give a dd($not_faculdade), is shown the apparently right relationships!

What could be wrong?

Follow relationship (I did it the way I could because I don’t know how to work with these tools)

1 news owns several photos belonging to a single news 1 news can appear in multiple units and each unit can contain various news

Answer 1

There are problems in your relationships through settings on Model of their classes, then, I will propose a minimum model so that I can do in your project, example:

Diagram:

Classes eloquent

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Noticia extends Model
{
    protected $fillable = ['titulo', 'texto'];
    protected $primaryKey = 'id_noticia';

    public function unidades()
    {
        return $this->belongsToMany(Unidade::class,
            'noticia_unidade',
            'id_noticia',
            'id_unidade');
    }
    //para trazer todas as fotos
    public function fotos()
    {
        return $this->hasMany(Foto::class, 'id_noticias', 'id_noticia');
    }
    // para trazer 1 foto
    public function foto()
    {
        return $this->hasOne(Foto::class, 'id_noticias', 'id_noticia');
    }
}

---

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Unidade extends Model
{
    protected $fillable = ['titulo'];
    protected $primaryKey = 'id_unidade';

    public function noticias()
    {
        return $this->belongsToMany(Noticia::class,
            'noticia_unidade',
            'id_unidade',
            'id_noticia');
    }
}

---

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Foto extends Model
{
    protected $fillable = ['id_noticias', 'foto'];
    protected $primaryKey = 'id_fotos';

    public function noticia()
    {
        return $this->belongsTo(Noticia::class, 'id_noticias', 'id_noticia');
    }
}

How to use?

 App\Noticia::with('foto')
     ->whereHas('unidades', function($q){
         $q->where('noticia_unidade.id_unidade',2);
     })
     ->get();

Upshot

=> Illuminate\Database\Eloquent\Collection {#724
     all: [
       App\Noticia {#734
         id_noticia: 1,
         titulo: "Noticia 1",
         texto: "Texto 1",
         foto: App\Foto {#746
           id_fotos: 1,
           id_noticias: 1,
           foto: "foto/0001-1.jpg",
         },
       },
       App\Noticia {#716
         id_noticia: 2,
         titulo: "Noticia 2",
         texto: "Texto 2",
         foto: App\Foto {#747
           id_fotos: 2,
           id_noticias: 2,
           foto: "foto/0002-1.jpg",
         },
       },
     ],
   }

Explanation: in the table the news number 3 is not part of unit 2 so it does not appear. Another point that in the class Noticia two methods were created foto and fotos, where respectively brings only one photo and the other all base photos, this is good to optimize the SQL.

---

Observing: check all fields, names, classes, etc.., this can cause errors due to incorrect configuration or configuration. Another point is that you should have used the standard nomenclature, but no problem if you don’t use it, only in this aspect you should configure, model for model.

Answer 2

I believe this will not work. You can use the whereHas in place of with.

$not_faculdade = Noticia::with(['foto' => function($query){
    $query->get()->first();
}])
->whereHas('unidade', function($query2){
    $query2->where('id_unidade','2');
})
->orderBy('id_noticias','DESC')
->take(2)
->get();