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

Eduardo Fuerte asked

on 2020-02-05

Laravel
PHP

Hi Experts

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

Accordingly to:

Actual Controller code:

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

Open in new window

It obtains data just for the table Vitrines.

What is needed is to obtain a sum() of column curtiu at the associated table User_vitrines.

In a way that the resultant query presents the actual data plus two other columns that are the sum(curtiu=0) and sum (curtiu=1)

The Model Vitrine code is:

<?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);
  }

}

Open in new window

I guess a new method must be coded here to have the desired sum()

Thanks in advance!

Comment

Watch Question

View Solution Only

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-05

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');
    }
}

Open 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.

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-05

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;
    }

Open in new window

And Controller:

        $vitrinesX = Vitrine
                ::curtidas();

Open 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 }}" />
....

Open 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.

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-05

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();

Open 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;

Open in new window

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-05

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();

Open in new window

So I need to use this also?

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

Open in new window

And something must to be adjusted....

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-05

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');
    }
}

Open 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.

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-05

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');
    }
}

Open 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();

Open in new window

Is that comprehension correct?

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-05

Hi Eduardo,

Pretty sure that won't work:

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

Open 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.

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

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();

    }				
}

Open 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();

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-06

Hey Eduardo,

Almost :)

On your Vitrine model, add the following:

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

Open 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;

Open in new window

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

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

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();

Open in new window

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

And thank you for the help by now!

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

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');
		}
//--------------------------------------------------------
}

Open in new window

And at HomeController.php

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

	->get();

Open in new window

I get this error:

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

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-06

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');
}

Open in new window

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

No problems!

Now this is working:

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

                ->get();

Open in new window

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

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-06

Excellent. Now we just need to pass in some extra details to the withCount() method. First off, make sure you've imported the Builder class. Add this to the top of your Home Controller:

use Illuminate\Database\Eloquent\Builder;

Open in new window

And now pass in an array to the withCount() method:

->withCount([
    'user_vitrines as likes' => function (Builder $query) { $query->where('curtiu', 1); },
    'user_vitrines as dislikes' => function (Builder $query) { $query->where('curtiu', 0); }
])
->get();

Open in new window

That will now add 2 new properties to your Vitrine - one called likes that counts the number of records where UserVitrine->curtiu = 1, and another called dislikes that counts the number of records where UserVitrine->curtiu = 0:

foreach ($vitrines as $vitrine):
    echo "Likes: " . $vitrine->likes;
    echo "Dislikes: " . $vitrine->dislikes;
endforeach;

Open in new window

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

After reading here:

You were pretty sure about (n x n)

I guess the user_vitrine would be the pivot_table...

Our messages crossed.

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

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.

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-06

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.

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-06

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

Open in new window

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-06

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.

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-07

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!

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-07

Chris

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

Chris StanyonWebDev

Trusted Expert

Most Valuable Expert 2018

Distinguished Expert 2019

Commented: 2020-02-07

Good news Eduardo.

Glad I could help :)

Eduardo FuerteDeveloper and Analyst

Author

Commented: 2020-02-07

I'm really learning a lot!

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

Premium Content

Premium Content

View Solution Only