I'm stuck with a really stupid query.
I tried to find the solution for like days, but nothing helped and so I don't find the answer.
I want to get the newest reaction from a table, to display the username.
First of all, these are my Database schemes:
comments (Has a model Comment)
threads (Has a model Thread)
So My raw query looks like this:
select `comments`.`username` from `comments` inner join `threads` on `comments`.`tid` = `threads`.`tid` where `comments`.`deleted_at` is null and `threads`.`cid` = '$categorie->id' order by `comments`.`posted_at` desc limit 1
A var_dump() of $categorie->id returns int(3) where the 3 stands for the categorie number.
When I execute the query (raw) in navicat, it returns this:
What is good, since that is the corract value it needs to return.
However when I rebuild this query in "Laravel-eloquent-style", the query looks like this:
Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->pluck('comments.username')
Be aware of that the query is build in a foreach loop and has an if-statement before. But that will be sown later on.
This does return nothing.
When I inspect elements, I just got nothing.
I tried the DB::select(DB::raw('query')), but that doesn't work either.
I render my page in the ForumController:
public function index()
{
$forums = Forums::orderBy('disp_order', 'asc')->get();
$categories = Categorie::orderBy('disp_order', 'asc')->get();
return View::make('index')->with('forums', $forums)->with('categories', $categories);
}
This works fine, and the view looks like this:
@foreach($forums as $forum)
<div class="panel-group col-sm-12">
<div class="panel panel-default">
<div class="panel-heading" style="background-color: {{ $forum->color }};">
<i class="fa fa-folder-open-o fa-fw"></i>
<strong><a href="Forum-{{ Str::slug($forum->name) }}">{{ $forum->name }}</a></strong>
</div>
<div id="forum4" class="panel-collapse collapse in">
<div class="table-responsive">
<table class="table table-hover">
<thead>
<tr>
<th></th>
<th>Forum naam</th>
<th class="text-right">Topics</th>
<th class="">Laatste post info</th>
</tr>
</thead>
<tbody>
<tr>
@foreach($categories as $categorie)
@if($categorie->fid == $forum->fid)
<td class="topic-marker-forum">
<i class="fa fa-comments fa-3x"></i>
</td>
<td class="col-md-6 col-sm-6">
<div><a href="Categorie-{{ Str::slug($categorie->name) }}" title="{{ $categorie->name }}"><strong>{{ $categorie->name }}</strong></a></div>
<div class=""><em>{{ $categorie->description }}</em></div>
</td>
<td class="col-md-1 col-sm-1 text-right"><span class="badge">{{ Thread::where('cid', '=', $categorie->id)->remember(15)->count() }}</span></td>
<td class="col-md-4 col-sm-4 ">
<div>
@if(Thread::where('cid', '=', $categorie->id)->exists() && Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->exists())
<a href="{{ Config::get('app.url') }}/Thread-{{ Thread::where('cid', '=', $categorie->id)->orderBy('date_posted', 'DESC')->pluck('slug') }}">{{ Helper::HTMLFilter(Thread::where('cid', '=', $categorie->id)->orderBy('date_posted', 'DESC')->pluck('title')) }}</a><br>
<i class="fa fa-clock-o"></i> {{ \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->pluck('posted_at'))->format('d/m/Y H:i') }}<br>
<i class="fa fa-user"></i> <a href="{{ Config::get('app.url') }}/User-{{ Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->pluck('comments.username') }}">{{ Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->pluck('comments.username') }}</a>
@else
<b>-</b>
@endif
</div>
</td>
</tr>
@endif
@endforeach
</tbody>
</table></div>
</div>
</div>
</div>
@endforeach
The strangest part is that this:
{{ \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->pluck('posted_at'))->format('d/m/Y H:i') }}
Just works. Like nothing is wrong with that, when I call that item, it returns the correct value.
My Models are just normal;
<?php
use Illuminate\Database\Eloquent\SoftDeletingTrait;
class Comment extends Eloquent
{
use SoftDeletingTrait;
protected $dates = ['deleted_at'];
protected $table = 'comments';
public $timestamps = false;
public function user()
{
return $this->belongsTo('User', 'uid');
}
}
And
<?php
use Illuminate\Database\Eloquent\SoftDeletingTrait;
class Thread extends Eloquent
{
use SoftDeletingTrait;
protected $dates = ['deleted_at'];
protected $table = 'threads';
public $timestamps = false;
}
Hope someone can help me out!
var_dump(DB::getQueryLog())after you run the query, we can see the difference in raw queries