在laravel 5中计算所有类别的职位

Question

hello I have two tables one is categories another is ads_listings. In categories table there are 4 columns id, parent_category_id, category_slug, category_title. Here parent_category_id 0 indicates main category and rest of the thing indicates sub category. user post store in ads_listings table. Now I want to find post with specific categories like this

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

here the problem is my code found only subcategories which have posts. my controller code is

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

views code is:

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

my Category table is:

ads_listing table is:

Answer 1

I think the problem is that you are counting ad_listings when you primarily want to count categories and get the number of ad_listings, I would do something like:

Second shot :)

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

}