$vitrinesX = Vitrine
::vigente()
->ativa()
->aprovada()
->global(Auth::user()->tipoparticipante_id)
->orderBy('id','desc')
->get();
<?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);
}
}
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;
}
$vitrinesX = Vitrine
::curtidas();
<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 }}" />
....
$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();
This assumes your Vitrine model has defined a 1-to-many relationship called likes.foreach ($vitrines as $vitrine) :
echo $vitrine->like_count;
echo $vitrine->dislike_count;
endforeach;
// 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();
public function Users() {
return $this->belongsToMany('App\User_vitrine')->withPivot('curtiu');
}
class Vitrine {
public function likes() {
return $this->hasMany('App\Like');
}
}
class Like {
public function vitrine() {
return $this->belongsTo('App\Vitrine');
}
}
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:class Vitrine {
public function user_vitrines() {
return $this->hasMany('App\user_vitrines');
}
}
class user_vitrines {
public function vitrine() {
return $this->belongsTo('App\Vitrine');
}
}
$vitrinesX = Vitrine
::vigente()
->ativa()
->aprovada()
->global(Auth::user()->tipoparticipante_id)
->orderBy('id','desc')
//------------------------------------------------------------
->withCount('curtiu')->get();
//------------------------------------------------------------
->get();
public function user_vitrines() {
return $this->hasMany('App\user_vitrines');
}
That code assumes you have a Model in you App folder called user_vitrines.<?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();
}
}
public function user_vitrines() {
return $this->hasMany('App\UserVitrine');
}
That adds a relationship to your Vitrine model so you have access to all of the related UserVitrines.foreach ($vitrines as $vitrine):
echo $vitrine->user_vitrines_count;
endforeach;
Once you've got that bit working, we can move on to splitting the likes and dislikes
$vitrines = Vitrine
::vigente()
->ativa()
->aprovada()
->global(Auth::user()->tipoparticipante_id)
->orderBy('id','desc')
->withCount('user_vitrines')
->get();
<?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');
}
//--------------------------------------------------------
}
$vitrinesX = Vitrine
::vigente()
->ativa()
->aprovada()
->global(Auth::user()->tipoparticipante_id)
->orderBy('id','desc')
->withCount('user_vitrines')
->get();
public function user_vitrines() {
return $this->hasMany('App\Models\UserVitrine');
}
$vitrinesX = Vitrine
::vigente()
->ativa()
->aprovada()
->global(Auth::user()->tipoparticipante_id)
->orderBy('id','desc')
->withCount('user_vitrines')
->get();
<!--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
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
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.