Getting the count of a constrained, joined query in Laravel's query builder
<p>I have a one-to-one relationship between <code>users</code> and <code>activities</code>, such that each user has a <code>last_activity_id</code> which maps to a particular row in <code>activities</code>.</p>

<p>I need to be able to filter a list of users based on the value of a column in their last activity.  To be able to do additional things like pagination, this must be done in the query itself, so I cannot fetch the results and then apply the filters after the fact.  Thus, I use a <code>select</code> along with a <code>leftJoin</code> to bring the related columns directly into my result set:</p>

<pre><code>$query = User::select(
            'users.*',
            'activities.description as last_activity_description')
         -&gt;leftJoin('activities', 'activities.id', '=', 'users.last_activity_id')
         -&gt;having('last_activity_description', 'like', "%blanchin%");
</code></pre>

<p>This seems to work fine, and I can call <code>$query-&gt;get()</code> to get the filtered result set.  It generates a query that looks like:</p>

<pre><code>select `users`.*, 
    `activities`.`description` as `last_activity_description` 
from `users` 
left join `activities` on `activities`.`id` = `users`.`last_activity_id`
having `last_activity_description` like '%blanchin%'
</code></pre>

<p>However, if I try to then get a <em>count</em> on this result set by calling <code>$query-&gt;count()</code>, Laravel generates this (failed) query:</p>

<pre><code>select count(*) as aggregate 
from `users` 
left join `activities` on `activities`.`id` = `users`.`last_activity_id` 
having `last_activity_description` like '%blanchin%'
</code></pre>

<p>It seems that calling <code>count()</code> has stripped out my original <code>select</code> clause and replaced it with <code>select count(*) as aggregate</code>, which means that my <code>having</code> clause fails because the <code>last_activity_description</code> alias is no longer defined.</p>

<p>How can I work around this?</p>