mercredi 4 mars 2020

Multiple WHERE clauses in DB::RAW in Laravel

I have a query as shown below.

I would want to introduce or select data as DB::raw('select SUM(review_details.param_value) / COUNT(review_headers.id) AS avgUserReview') and apply some WHERE clauses to this query alone.

$data = BusinessDetail::select(
    'business_details.id as businessId',  
    'business_details.name as description', 
    'cities.description as city',
    'countries.description as country',
    'sectors.description as sector'
)->join('sectors', 'business_details.sector_id', '=', 'sectors.id')
->join('countries', 'business_details.country_id', '=', 'countries.id')
->join('cities', 'business_details.city_id', '=', 'cities.id')
->leftJoin('review_headers', 'business_details.id', '=', 'review_headers.business_detail_id')
->leftJoin('review_details', 'review_headers.id', '=', 'review_details.review_header_id')
->leftJoin('rate_params', 'review_details.rate_param_id', '=', 'rate_params.id')
->leftJoin('param_types', 'rate_params.param_type_id', '=', 'param_types.id')
->groupBy('business_details.id');

I would want to introduce another SELECT statement as below and apply the WHERE clauses to only that STATEMENT

DB::raw('select SUM(review_details.param_value) / COUNT(review_headers.id) AS avgUserReview')

There WHERE clauses are

->where('review_details.param_value', '<>', NULL)
->where('review_details.param_value', '<>', '')
->whereIn('rate_param_id', [1, 3, 4, 5])
->where('review_headers.review_status', '=', 1)

I mean something like DB::raw('select SUM(review_details.param_value) / COUNT(review_headers.id) AS avgUserReview from review_details where conditions')

I have tried DB::raw('select SUM(review_details.param_value) / COUNT(review_headers.id) AS avgUserReview from review_details where review_headers.review_status = 1') but getting Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select SUM(review_details.param_value) / COUNT(review_headers.id) AS avgUserRevi'



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire