在Laravel中按计数排序

Question

In my Laravel application, I have implemented Tagging of articles and events. This is accomplished by having two tables: tags and taggables. In my tag controller, I have an index method that grabs all the tags, a count of the tags and the same logic to count used tags.

It looks like this:

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $tags = Tag::orderByDesc('name')->get();    
    $tagCount = count($tags);
    $usedTags = Tag::has('articles')->orHas('events')->orderBy('name')->get();
    $usedTagsCount = count($usedTags);

    return view('pages.tags.index', compact('tags', 'usedTags', 'tagCount', 'usedTagsCount'));
}

I pass all this data to my view which looks like this:

<div class="container">
    <div class="row">

        <!-- Search results -->
        <div class="col-md-8">
            <div class="padded-content-box">
                <div class="header">
                    <div class="row">
                        <div class="col-xs-12 col-md-12">
                            <h1 class="heading">Tags</h1>
                        </div>
                    </div>
                </div>
            </div>

            <div class="padded-content-box">
                    <div class="header">
                        <div class="row">
                            <div class="col-xs-12 col-md-12">
                                <h2 class="sub-heading">
                                    All Tags ({{ $tagCount }})
                                </h2>
                            </div>
                        </div>
                    </div>

                    <div class="content">
                        <div class="tag-row">
                            <div class="tag-words-list">
                                @foreach($tags as $tag)
                                    <a class="tag-word" title="{{ $tag->name }}" href="{{ URL::action('TagController@show', $tag->slug) }}">{{ $tag->name }}</a>
                                @endforeach
                            </div>
                        </div>
                    </div>
                </div>

            <div class="padded-content-box">
                    <div class="header">
                        <div class="row">
                            <div class="col-xs-12 col-md-12">
                                <h2 class="sub-heading">
                                    Tags currently in use ({{ $usedTagsCount }})
                                </h2>
                            </div>
                        </div>
                    </div>

                    <div class="content">
                        @foreach($usedTags as $tag)
                        <p>
                            <a class="tag-word" title="{{ $tag->name }}" href="{{ URL::action('TagController@show', $tag->slug) }}">{{ $tag->name }}</a> 
                            x {{  count($tag->articles) + count($tag->events) }}
                        </p>
                        @endforeach
                    </div>
                </div>
        </div>

        <div class="col-md-4"></div>
    </div>
</div>

As you can see, to show a count of tags used I just add the articles and events that have a given tag.

How would I count how many times each tag is used in both events and articles, then order them by the count. Essentially if a tag has been used the most it would be at the top of a list.

Update

As suggested I have changed my controller method to look like this:

public function index()
{
    $tags = Tag::orderBy('name')->get();

    $tagCount = count($tags);

    $usedTags = Tag::has('articles')->orHas('events')->withCount('articles', 'events')->orderByRaw('articles_count + events_count DESC')->orderBy('name')->get();

    $usedTagsCount = count($usedTags);

    return view('pages.tags.index', compact('tags', 'usedTags', 'tagCount', 'usedTagsCount'));
}

This ensures that the tags are in count and alphabetical order.

Then in my view I changed the count code to this:

<p>
    <a class="tag-word" title="{{ $tag->name }}" href="{{ URL::action('TagController@show', $tag->slug) }}">{{ $tag->name }}</a> x {{  $tag->articles_count + $tag->events_count }} 
</p>

This way I get to remove some minor logic from the view.

Answer 1

Use withCount():

$tags = Tag::withCount('articles', 'events')
    ->orderByRaw('articles_count + events_count DESC')
    ->get();