Laravel Access Violation Error QueryException 42000

Question

Database name :- 'hdwallpapers'

Table :- 'images'

Table :- 'categories'

Whenrever I run the following query it generates output shown in the image.

select categories.cid, categories.category_name, COUNT(images.category) as "Count" from categories LEFT JOIN images on categories.cid=images.category GROUP BY categories.category_name ORDER BY categories.category_name

Output

I've created Laravel App called "HDWallpapers" in Laravel version 5.8 and I'm using XAMPP and database is in MySQL and I'm using Visual Studio Code Editor

I've created separate models 'Categories' for table 'categories' and "Images" for table 'images'.

The Problem is that, that mysql query gives me the output I wanted but whenever I try that query in Laravel it generates Syntax Error or Access Violation.

Here is the laravel query that I tried but I am getting the same Syntax Error or Access Violation error

1)  $categoryData=DB::select("SELECT categories.cid, categories.category_name, count(images.category) from 'categories' left join 'images' on categories.cid=images.category GROUP BY categories.category_name order by categories.category_name ASC");


2)  $categoryData=DB::table('categories')->leftjoin('images','categories.cid','=','images.category')->select('categories.cid','categories.category_name', DB::raw("COUNT(images.category) as count"))->groupby('categories.category_name')->get();


3)  $categoryData=Categories::leftjoin('images','categories.cid','=','images.category')->select('categories.cid','categories.category_name', DB::raw("count(images.category) as count"))->groupby('categories.category_name')->get();

I tried searching on many websites but because I'm new to laravel I couldn't understand what should I do, so please help me!!!

score 1 · Answer 1 · answered Jun 11 '20 at 06:29

I don't recommend it for scalable projects, but you can disable ONLY_FULL_GROUP_BY mode (which is a constrain for MySQL to execute only queries which contain non ambiguous GROUP BY statements) from database configuration.

Go to /app/database.php at connections -> mysql, and append the following code to the end:

'mysql' => [
   'driver' => 'mysql',
   ....

   // append the following:
   'modes' => [
       //'ONLY_FULL_GROUP_BY',
       'STRICT_TRANS_TABLES',
       'NO_ZERO_IN_DATE',
       'NO_ZERO_DATE',
       'ERROR_FOR_DIVISION_BY_ZERO',
       'NO_AUTO_CREATE_USER',
       'NO_ENGINE_SUBSTITUTION',
   ]
]

Then execute php artisan config:cache

score 0 · Accepted Answer · answered Jun 11 '20 at 06:36

just group by all the columns you have selected ...

2)

$categoryData=DB::table('categories')->leftjoin('images','categories.cid','=','images.category')->select('categories.cid','categories.category_name',DB::raw("COUNT(images.category) as count"))->

groupby('categories.cid','categories.category_name')->get();

score 0 · Answer 3 · answered Jun 11 '20 at 06:44

0

In config\database.php --> "mysql" array

Set

'strict' => false

Details is here

answered Jun 11 '20 at 06:44

Mr. Perfectionist

2,605
2
24
35

Laravel Access Violation Error QueryException 42000

3 Answers3