Relationship with 3 tables in laravel

Question

Relationship with 3 tables in laravel

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I'm trying to make the relationship between 3 tables in laravel. With 2 tables I got it, thanks to the help of the forum, but I can not 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 news relationship with photos is ok: on the main page there are 2 news items and a photo of each. Now I need to be filtered by unit: In the page of the faculty only appears the news of the unit 2, for example.

Following 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 items with the "cover" photo appear normally, but the filter does not work. If I give dd($not_faculdade) , the seemingly right relationships are shown!

What could be wrong?

Follow relationship (I did it the way I could because I do not know how to play with these tools)

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

laravel relacionamento

asked by anonymous 10.08.2017 / 13:03

2 answers

Differentiate CPF and CNPJ that are in the same column of MySQL database How to use joblib in Python for parallelism?

score 1 · Answer 1

There are problems in your relationships with settings in Model of your classes , so I'll propose a minimum template so you 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();

Result

=> 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 item number 3 is not part of unit 2 so it does not appear. Another point in the Noticia class was to create two methods foto and fotos , where you only have one photo and the other all the photos from the base, this is good for optimizing SQL .

Note: Check all fields, names, classes , etc. errors due to a lack of configuration or configuration. Another point is that you should have used the standard nomenclature, but no problem if you do not use it, but in this regard you should set model to model .

score 0 · Answer 2

I believe this will not work. You can use whereHas instead 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();