How to select count and use where with Laravel's fluent query builder?

247
December 27, 2016, at 01:27 AM

in my case

$products = $products->select(array('products.*', \DB::raw('COUNT(extra_fields.price) as price_count')))->leftJoin('extra_fields', 'products.id', '=', 'extra_fields.item_id')
                    ->where('item_type', 'App\Models\Product')
                    ->whereRaw("price = '0'")
                    ->whereRaw("price_count = 1")
                    ->groupBy('item_id')
                    ->groupBy('price')
                ;

when i want to use { price_count } in where or whereRaw i got :

Column not found: 1054 Unknown column 'price_count'

price_count just only work on this way :

->orderBy('price_count')

how can i use price_count on where or whereRaw ?

any advice ?

Answer 1

You can use havingRaw as:

->havingRaw('price_count = 1')

OR

->having('price_count', '=', 1)

OR

The havingRaw method may be used to set a raw string as the value of the having clause.

->havingRaw('COUNT(extra_fields.price) = 1')
Answer 2

You can't use summary function value as you want, instead of "where" you can use in "having".

Hope this will help you.

READ ALSO
Fill Text field with second jquery and php

Fill Text field with second jquery and php

I need help with the code belowI am trying to populate a text field with the data from the row set, but I want to use another field within the results

234
Matching users using Node.js on my website

Matching users using Node.js on my website

I am currently developing a "dating" website as a school projectI'm using Node

160