Laravel query builder, use custom select when calling count()

Question

I have three tables, one defining a many to many relationship between the other two.

table : collections

table : collection_question

table : questions

I am running this query to return the number of questions a collection has

$results = DB::table('collections')
    ->select(array(
        DB::raw('count(collection_question.question_id) as question_count'),
        'name',
        'description'
    ))->leftJoin(
        'collection_question', 
        'collections.id', 
        '=', 
        'collection_question.collection_id'
    )->where('question_count', '>', 1)
    ->orderBy('question_count', 'asc')
    ->get();

This works fine as the select query is not tampered with by the query builder.

When I swap out get() for count() however the query builder replaces my select clause with select count(*) as aggregate which is understandable, however I loose the binding to question_count in the process and a SQL exception is thrown.

I've looked through the source code for Illuminate\Database\Query\Builder to try and find a solution but other than manually executing the raw query with a custom count(*) alongside my other select clauses I'm at a bit of a loss.

Can anyone spot a solution to this?

David Barker · Accepted Answer · 2014-06-05 08:28:38Z

1

Instead of calling count() on a Builder object, I've resorted to creating my own count expression in addition to any other select clauses on the query rather than replacing them.

// This is in a class that uses Illuminate\Database\Query\Expression the clone
// isn't necessary in most instances but for my case I needed to take a snapshot
// of the query in its current state and then manipulate it further.
$query = clone($query);

$query->addSelect(new Expression("count(*) as aggregate"));

$count = (int) $query->first()->aggregate;

edited Jun 5, 2014 at 8:28

answered May 24, 2014 at 10:33

David Barker

14.6k3 gold badges51 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel query builder, use custom select when calling count()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related