php - Laravel 连接查询返回 Null，而原始查询有值

Question

我遇到了一个非常愚蠢的查询。

我花了几天时间试图找到解决方案，但没有任何帮助，所以我找不到答案。

我想从表格中获取最新 react ，以显示用户名。

首先，这些是我的数据库方案:

comments(有模型Comment)

threads(有一个模型Thread)

所以我的原始查询如下所示:

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

$categorie->id 的 var_dump() 返回 int(3)，其中 3 所在位置为类别编号。

当我在 navicat 中执行查询(原始)时，它返回以下内容:

什么是好的，因为这是它需要返回的正确值。

但是，当我在“Laravel-eloquent-style”中重建此查询时，查询如下所示:

Comment::join('threads', 'comments.tid', '=', 'threads.tid')->where('threads.cid', '=', $categorie->id)->orderBy('comments.posted_at', 'DESC')->pluck('comments.username')

请注意，查询是在 foreach 循环中构建的，并且之前有一个 if 语句。但这将在稍后播种。

这不会返回任何内容。

当我检查元素时，我什么也没得到。

我尝试了DB::select(DB::raw('query'))，但这也不起作用。

我在 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);
    }

这工作正常， View 如下所示:

@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

最奇怪的部分是:

{{ \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') }}

正常工作。就像没有任何问题一样，当我调用该项目时，它返回正确的值。

我的模型很正常；

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

}

还有

<?php
use Illuminate\Database\Eloquent\SoftDeletingTrait;

class Thread extends Eloquent
{
    use SoftDeletingTrait;

    protected $dates = ['deleted_at'];

    protected $table = 'threads';

    public $timestamps = false;
}

希望有人能帮助我!

Answer 1

尝试这样的事情

Comment::with(['threads'=>function($query) use ($categorie){
                $query->where('threads.cid',$categorie->id)
                      ->whereNull('comments.deleted_at');
              }])
              ->orderBy('comments.posted_at', 'DESC')
              ->limit(1)
              ->pluck('comments.username');

如果您需要 - 'deleted_at' 不为空

->whereNotNull('comments.deleted_at');



DB::table('coments')
            ->join('threads', 'comments.tid', '=', 'threads.tid')
            ->where('threads.cid',$categorie->id)
            ->whereNull('comments.deleted_at');
            ->orderBy('comments.posted_at', 'DESC')
            ->limit(1)
            ->pluck('comments.username');