Could you point how to adjust this Laravel Controller/ Model code to correctly obtains data from these 02 tables?

Hey Eduardo,

I can't quite understand your relationships, so we may need to dig into that a little.

From your Schema diagram, it looks like a many-to-many relationship, so you normally add a method to you controller that returns a belongsToMany relationship.

Because you've added extra colu,ns to your relationship table, you should probably specify that they're returned in the realtionship as well

class Vitrine extends Model
{
    public function Users() {
        return $this->belongsToMany('App\User')->withPivot('curtiu');
    }
}

Select allOpen in new window

That set's up the relationship one way and you should add a relationship the other way (add a Vitrines method to your User model).

Now you should be able to access the Users from within the Vitrine model and include the additional pivot column.

I'm a little confused about how you want to sum. You say you want the sum() of column curtiu, but then you say you want the sum(curtiu=0). Surely if the value is 0, then the sum will also be 0.

Hi Chris

What I need here is a derivation from the last question we had interact, so I guess is simpler.

As we previously saved the likes (curtiu)  and dislike (naocurtiu) at DB, then when another user opens the page the likes and dislikes to every notice must to be shown.

Sorry, what I need is count()  - count of likes and of dislikes.

The relationship is 1-n    so:  01 notice  (vitrine table)  x   n (likes or dislikes) - (user_vitrines table)

What I tryed at Model  (but is not coherent with existent methods):

    public function scopeCurtidas($query){
    	$query=DB::select('select
                                v.id,
                                v.titulo,
                                v.conteudo,
                                v.url,
                                v.tipoparticipante_id,
                                v.ativo,
                                v.aprovada,
                                v.criador_id,
                                v.aprovador_id,
                                v.vigencia_inicio,
                                v.vigencia_termino,
                                v.created_at,
                                v.updated_at,
                                v.subtitulo,
                                count(if(uv.curtiu=1, 1, 0)) AS curtiu,
                                count(IF(uv.curtiu=0, 1, 0)) as naocurtiu
                                from vitrines v
                                inner join user_vitrines uv
                                on v.id = uv.vitrine_id
                                group by v.id');
                        
         return $query;
    }

Select allOpen in new window

And Controller:

        $vitrinesX = Vitrine
                ::curtidas();

Select allOpen in new window

The return of  scopeCurtidas  returns an array and this causes problems ahead.

To be exactly: here is where an array doesn't works:

<section class='row'>

	<div class="col-md-12 titulo">Instagaz</div>
	

    @if($vitrinesX->count()>0)
		 <div class="col-md-12">
         
			<img src="{{ $vitrinesX[0]->url }}" />
....
 

Select allOpen in new window

So when the user opens the page will receive the data (likes and dislikes) to every notice.

My dificulty is how to get this by Eloquent and apply to the context.

OK. Laravel has an method called withCount, so you could do something like the following:

$vitrines = Vitrine::withCount([
    'likes as like_count' => function (Builder $query) {
        $query->where('curtiu', 1);
    }.
    'likes as dislike_count' => function (Builder $query) {
        $query->where('curtiu', 0);
    }.
])->get();

Select allOpen in new window

This assumes your Vitrine model has defined a 1-to-many relationship called likes.

Now each of your $vitrine objects will have 2 properties called like_count and dislike_count:

foreach ($vitrines as $vitrine) :
    echo $vitrine->like_count;
    echo $vitrine->dislike_count;
endforeach;

Select allOpen in new window

Not clear to me,

The Controller code:

// Previous
$vitrinesX = Vitrine
	::vigente()
	->ativa()
	->aprovada()
	->global(Auth::user()->tipoparticipante_id)
	->orderBy('id','desc')
	->get();

// What I adapted
 $vitrinesX = Vitrine
	::curtidas();
	
// Your code - Does it have a relation with user_vitrines where stays the "curtiu" column...?

  $vitrinesX = Vitrine::withCount([
	'likes as like_count' => function (Builder $query) {
			  $query->where('curtiu', 1)
		}
	'likes as dislike_count' => function (Builder $query) {
		 $query->where('curtiu', 0)
	 }
	])->get();   

Select allOpen in new window

So I need to use this also?

  public function Users() {
        return $this->belongsToMany('App\User_vitrine')->withPivot('curtiu');
    }

Select allOpen in new window

And something must to be adjusted....

Hey Eduardo,

Normally, when you setup your models, you define the relationships as methods on your models. You say you have a 1 to many relationship between Vitrines and Like, so normally you'd set that up like so:

class Vitrine {
    public function likes() {
        return $this->hasMany('App\Like');
    }
}

class Like {
    public function vitrine() {
        return $this->belongsTo('App\Vitrine');
    }
}

Select allOpen in new window

Now your Vitrine model can access all of the related Likes and your Likes can access the parent Vitrine. That's how relationships are defined within Laravel. With this set up correctly, you can then run the withCount() method:

App\Vitrine::withCount('likes')->get();

Each of the returned Vitrine objects will have a property called likes_count

Like I said earlier though - your table schema looks more like a many-to-many realtionship, so the user_vitrines table acts as a pivot table between Vitrines and Users. Are you absolutely sure that you only have a one-to-many realtionship, because what you have doens't look like the normal way of doing things.

Hi Chris

Maybe what's causing confusion is that it doesn't exists the table "Likes" just the tables  vitrines and user_vitrines  liked showed at the diagram.

If an user clicks like or dislike at site's page for a certain photo - that represents a line of vitrines table (1)  a new line is created  at table user_vitrines(n)  with one of this values on the column "curtiu":
1 - if likes  
0 - if dislike.

No worries with the user_id that was already obatined before at another process.  

So the relation is 1 (vitrines)  - n (user_vitrines).

So, based on your previous code I'm going to adapt  and include the methods:

class Vitrine {
    public function user_vitrines() {
        return $this->hasMany('App\user_vitrines');
    }
}

class user_vitrines {
    public function vitrine() {
        return $this->belongsTo('App\Vitrine');
    }
}

Select allOpen in new window

And then, at the Controller change to:

  $vitrinesX = Vitrine
		::vigente()
		->ativa()
		->aprovada()
		 ->global(Auth::user()->tipoparticipante_id)
		->orderBy('id','desc')
//------------------------------------------------------------
		->withCount('curtiu')->get();
//------------------------------------------------------------
		->get();

Select allOpen in new window

Is that comprehension correct?

Hi Eduardo,

Pretty sure that won't work:

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

Select allOpen in new window

That code assumes you have a Model in you App folder called user_vitrines.

->withCount('curtiu')->get();

Is assuming you have a relationship on your Vitrine model called curtiu, which you don't.

It seems that you may have your Models/Tables set up in a non-standard way, which I don't really understand.

Laravel is generally easier to work with if you stick with the standrd way of doing things. If you're not too far into this project, then I would consider setting up a Like model and creating the relationships in the normal way.

Hi Chris


I certainly misconcepted. (I must to study a little more Eloquent to get more experient and to make our interaction more productive).

But, based on your last interaction: I have a Model called : UserVitrine, the relationship between the 02 tables is between the columns.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;

use Debugbar;
use DB;

class UserVitrine extends Model
{
    public function obter($user_id, $vitrine_id){
    
        return UserVitrine
            ::where('user_id', \DB::raw($user_id))
            ->where('vitrine_id', \DB::raw($vitrine_id))
            ->first();
    }
    
    public function obterCurtidasPorVitrine($vitrine_id, $curtiu){

        return UserVitrine
            ::where('vitrine_id', \DB::raw($vitrine_id))
            ->where('curtiu', \DB::raw($curtiu))
            ->count();

    }				
}

Select allOpen in new window

Vitrine (id)  x UserVitrine (vitrine_id)  so, I guess the correct way is:

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

->withCount('vitrine_id')->get();

Hey Eduardo,

Almost :)

On your Vitrine model, add the following:

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

Select allOpen in new window

That adds a relationship to your Vitrine model so you have access to all of the related UserVitrines.

Now when you want the count, you can do this:

$vitrines = App\Vitrine::withCount('user_vitrines')->get();

You'll see that we're passing in the name of the relationship property (user_vitrines)

Each of your Vitrine models returned will now have a property called user_vitrines_count

foreach ($vitrines as $vitrine):
    echo $vitrine->user_vitrines_count;
endforeach;

Select allOpen in new window

Once you've got that bit working, we can move on to splitting the likes and dislikes

Good!

I'm not certain  if it could be applied that way:

           $vitrines = Vitrine
                ::vigente()
                ->ativa()
                ->aprovada()
                ->global(Auth::user()->tipoparticipante_id)
                ->orderBy('id','desc')
                
                 ->withCount('user_vitrines')

                ->get();

Select allOpen in new window

Just going to the office now... retuning ASAP.

And thank you for the help by now!

So, what I did:

app\Models\Vitrine.php

	
<?php

	namespace App\Models;

	use Illuminate\Database\Eloquent\Model;
	use App\User;
	use Carbon\Carbon;

	class Vitrine extends Model
	{
	  public function tipoparticipante(){
		  return $this->belongsTo('App\Models\Tipoparticipante');
	  }

	  public function criador(){
		return $this->belongsTo('App\User', 'criador_id', 'id');
	  }

	  public function aprovador(){
		return $this->belongsTo('App\User','aprovador_id');
	  }

	  public function scopeTitulo($query,$value){
		if ($value===''){
		  return $query;
		} else {
		  return $query->where('titulo','like','%'.$value.'%');
		}
	  }

	  public function scopeConteudo($query,$value){
		if ($value===''){
		  return $query;
		} else {
		  return $query->where('conteudo','like','%'.$value.'%');
		}
	  }

	  public function scopeStatus($query,$value){
		if ($value==''){
		  return $query;
		} else {
		  return $query->where('ativo', $value);
		}
	  }

	  public function scopeAtiva($query){
		return $query->where('ativo', 1);
	  }

	  public function scopeAprovada($query){
		return $query->where('aprovada', 1);
	  }

	  public function scopeGlobal($query,$value){
		if ($value===''){
		  return $query->whereNull('tipoParticipante_id');
		} else {
		  return $query->whereNull('tipoParticipante_id')->orWhere('tipoParticipante_id',$value);
		}
	  }

	  public function scopePerfil($query,$value){
		if ($value==='0'){
		  return $query->whereNull('tipoParticipante_id');
		} elseif ($value=='') {
		  return $query;
		} else {
		  return $query->where('tipoParticipante_id',$value);
		}
	  }

	  public function scopeAprovacao($query,$value){
		if($value===''){
		  return $query;
		} else {
		  return $query->where('aprovada',$value);
		}
	  }

	  public function scopeCriador($query,$value){
		if($value===''){
		  return $query;
		} else {
		  return $query->where('criador_id',$value);
		}
	  }

	  public function scopeAprovador($query,$value){
		if($value===''){
		  return $query;
		} else {
		  return $query->where('aprovador_id',$value);
		}
	  }

	  public function scopeVigente($query){
		$agora = Carbon::now();
		$agora = Carbon::create($agora->year, $agora->month, $agora->day,23,59,59);
		return $query->where('vigencia_inicio','<=',$agora)->where('vigencia_termino','>=',$agora);
	  }


//---- METHOD ADDED---------------------------------------
		public function user_vitrines() {
			return $this->hasMany('App\UserVitrine');
		}
//--------------------------------------------------------
}

Select allOpen in new window

And at HomeController.php

$vitrinesX = Vitrine
	::vigente()
	->ativa()
	->aprovada()
	->global(Auth::user()->tipoparticipante_id)
	->orderBy('id','desc')
	
	 ->withCount('user_vitrines')

	->get();

Select allOpen in new window

I get this error:

it looks it couldn't found the Model  App\UserVitrine....  (project\app\Models\UserVitrine.php)

Ahhh. Sorry - didn't realise you'd stored your Models in a sub folder. Change your method to this:

public function user_vitrines() {
    return $this->hasMany('App\Models\UserVitrine');
}

Select allOpen in new window

No problems!

Now this is working:

           $vitrinesX = Vitrine
                ::vigente()
                ->ativa()
                ->aprovada()
                ->global(Auth::user()->tipoparticipante_id)
                ->orderBy('id','desc')
                
                 ->withCount('user_vitrines')

                ->get();

Select allOpen in new window

So, could you ´post how to deal with the count of likes/ dislikes?

After reading here:

You were pretty sure about (n x n)


I guess the user_vitrine would be the pivot_table...

Our messages crossed.

Your code really works out!

 

The likes/ dislikes must to be for the picture.

The relation with the user table is:   An user could like/ dislike just one time for each photo.

Excellent news.

Glad we got it working.

Looks lilke you do have a meny-to-many realtionship, so yes - the user_vitrine table acts as the pivot.

Yes.

So, this many to many relation is still considered, ok?

Since an user could like/ dislike just one time for each photo, it must to be considered at method save.

Amazingly the properties like/ dislike obtained at Controller coudn't be at the view:

	<!--input type="hidden" id="hdnCurtiu" value="{{ $curtiu }}"-->


  	  	<!--div class="col-xs-12 col-sm-4 col-md-4"-->
        <!--div class="col-md-10 col-sm-12"-->
        
        <div class="col-md-12 col-sm-12">
        
		    <p class="titResumoNoticia">Arquivo de Fotos</p>
		   
           <?php
                // EF 2020
                //file_put_contents('debug.log', $vitrinesX , FILE_APPEND); 
                //dd($vitrinesX);
                foreach ($vitrinesX as $vitrine):
                    echo "vitrine_id". $vitrine->id . '---';
                    echo "Likes: " . $vitrine->likes . '---';
                    echo "Dislikes: " . $vitrine->dislikes. '---';
                endforeach;
                
     
           ?> 
            
            @foreach($vitrinesX as $vitrine)

                <section class="row">

                               
                        <div class="col-sm-12 col-md-4">
                            <div class="box-noticias">
                               	<a href="javascript:void(0);" onclick='hotsite.vitrine.abrirVitrine({{$vitrine->id}});' >
        	    				{{$vitrine->titulo}}
        	    			    </a>                    
                            </div> 
                            
                            	<p id="like" class="likes" onclick="hotsite.vitrine.salvarEscolha({{ $vitrine->id }}, 1)">
				                    <i class="fa fa-thumbs-up"></i> <span id="qtdeCurtiu_{{ $vitrine->id }}">{{ $qtdeCurtiu }}</span>
				               	</p>   
                                    
           						<p id="dislike" class="likes" onclick="hotsite.vitrine.salvarEscolha({{ $vitrine->id }}, 0)">
				                    <i class="fa fa-thumbs-down"></i> <span id="qtdeNaoCurtiu_{{ $vitrine->id }}">{{ $qtdeNaoCurtiu }}</span>
						       </p>       
                        </div>           
              
                       
                        @if($loop->last)
                            @break
                        @else
                            @continue 
                        @endif      
                              

                </section>
            
  
            @endforeach
			</div>

	</div>
@endsection

Select allOpen in new window

Hey Eduardo,

Yes - you still have a many-to-many relationship - 1 User can have many Pictures - 1 Picture can have many Users.

Not sure why your View i not displaying the Like/Dislikes. What do you see if your un-comment the dd($vitrinesX) line.

May need to see your Controller method in full.

Hi Chris

Sorry.

After revision I found that I had to actualize the same collection at another controller (VitrineController.php) to make likes/ dislikes to be presented.

Everything is fine now!

Chris

Thank you for your patience and dedication on another excellent solution!!!

Good news Eduardo.

Glad I could help :)

I'm really learning a lot!