php - 计算 laravel 5 中所有类别的帖子

Question

你好，我有两个表，一个是类别，另一个是 ads_listings。在类别表中有 4 列 id、parent_category_id、category_slug、category_title。这里 parent_category_id 0 表示主类别，其余部分表示子类别。 ads_listings 表中的用户帖子存储。现在我想找到像这样的特定类别的帖子

vehicles(3)
cars(2)
motorbike(1)
cycle(0)

这里的问题是我的代码只找到有帖子的子类别。我的 Controller 代码是

public function countListingsByCategories()
{
    return DB::table("ads_listings")
        ->select("categories.category_title",DB::raw("COUNT(ads_listings.category_id) as num_listings"))
        ->join("categories", "categories.id","=","ads_listings.category_id")
        ->groupBy("ads_listings.category_id")
        ->get();
}

View 代码是:

<ul class="row catelist">
  @if(isset($categoriesNumListings) && count($categoriesNumListings))      
     @foreach($categoriesNumListings as $categoriesNumListings)          
        <li class="col-md-12"><a href="{{ route('view.listings.by.category', $categoriesNumListings->category_title) }}" title="{{ $categoriesNumListings->category_title }} ads from {{ $categoriesNumListings->category_title }}">{{ $categoriesNumListings->category_title }} <span>({{ $categoriesNumListings->num_listings }}Listings )</span></a></li>
    @endforeach
  @endif
</ul>

我的类别表是:

ads_listing 表是:

Answer 1

我认为问题是当您主要想计算类别并获取 ad_listings 的数量时，您正在计算 ad_listings，我会这样做:

第二枪:)

public function countListingsByCategories()
{
    return DB::table("categories")
      ->select("categories.category_title",
        DB::raw("categories.title, 
          CASE 
            WHEN (categories.parent_id = 0) 
            THEN (SELECT count(ads_listings.category_id)  
              FROM ads_listings 
              WHERE ads_listings.category_id in 
                    (SELECT subcategory.id 
                    FROM categories subcategory
                    WHERE subcategory.parent_id = categories.id))
            ELSE (SELECT COUNT(ads_listings.category_id)  
                  FROM ads_listings 
                  WHERE ads_listings.category_id = categories.id)
            END as numberOfPosts, 
          CASE 
            WHEN (categories.parent_id = 0) 
            THEN (select 'Category') 
            ELSE (select 'subCategory')
            END as type 
        FROM categories"))
      ->get();

}