1064 You have an error in your SQL syntax; check the manual … for the right syntax to use near ‘WHERE reviews.yes_no = yes

I want to optimise the queries on my project and after many tentatives of using eager loading with datatables i just give up because there was no query reducing at all.

Anyway now Im trying to achieve that using join, but the problem is that using WHERE clause in DB::RAW it gives me the following error:

"Exception Message:nnSQLSTATE[42000]: 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 'WHERE reviews.na_ja in ja, count(reviews.category_id) WHERE...

This is how my code looks like:

$categories = DB::table('categories')
                    ->join('reviews', 'reviews.category_id', '=', 'categories.id')
                    ->select([
                        'categories.id', 
                        'categories.slug', 
                        'categories.title', 
                        'categories.status', 
                        'categories.created_at',
                        DB::raw('count(reviews.category_id) AS all_reviews'),
                        DB::raw('count(reviews.category_id) WHERE reviews.yes_no = yes AS all_yes_reviews'),
                        DB::raw('count(reviews.category_id) WHERE reviews.yes_no = na AS all_no_reviews'),
                    ])
                    ->where('categories.deleted_at', '=', NULL)
                    ->groupBy(['reviews.category_id', 'categories.title']);

return DataTables::of($categories)
                   ->addColumn('action', function ($category) {
                         return view('admin/categories/category_actions', compact('category'));
                     })->make(true);

I have tried many suggestion that i found searching for a solution but none worked. Can anyone tell how to achieve this using join?

EDIT: changed the order and putted WHERE before GROUP BY and error is still there. The error is on DB::raw‘s

Answer

As is the tradition now in stackoverflow I had to answer to my own question. (In 6 question I answered to myself on 5… Im going to set a record or get a badge for that in future)

Anyway after I tried many ways of solving what I wanted to achieve I decided to use ‘denormalisation’ of db. I added 3 columns on categories table respectively total_review, total_yes, total_no and made the calculation using BD::raw like this:

$categories = DB::table('categories')
                ->select([
                    'categories.*',
                    DB::raw('round((total_yes/total_reviews)*100) as yes_percentage'),

                    DB::raw('round((total_no/total_reviews)*100) as no_percentage'),
                ])
                ->where('categories.deleted_at', '=', NULL);

This is a good example of using ‘denormalisation’ of db but this method should be used only in specific cases like this one.