[ERROR] Laravel : Syntax error or access violation: 1055 Error (ok)

https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error

strict => falseph

As in

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

database\migrations\2022_08_15_034648_create_items_count_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateItemsCountTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items_count', function (Blueprint $table) {
            $table->id();
            $table->mediumInteger('id_item');
            $table->mediumInteger('price');
            $table->mediumInteger('quantity');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('items_count');
    }
}

database\factories\ItemsCountFactory.php

<?php

namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;
use App\Models\ItemsCount;
class ItemsCountFactory extends Factory
{
    protected $model = ItemsCount::class;
    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'id_item'            => $this->faker->numberBetween(1, 10),
            'price'            => $this->faker->numberBetween(200, 300),
            'quantity' => $this->faker->numberBetween(1, 10)
        ];
    }
}

database\seeders\ItemsCountSeeder.php

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use App\Models\ItemsCount;
class ItemsCountSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        ItemsCount::factory()->count(30)->create();
    }
}

app\Models\ItemsCount.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class ItemsCount extends Model
{
    use HasFactory;
    protected $table = 'items_count';
}

database\migrations\2022_08_15_033307_create_items_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->string('description');
            $table->mediumInteger('min_quantity');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('items');
    }
}

database\seeders\ItemSeeder.php

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use App\Models\Item;
class ItemSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        Item::factory()->count(30)->create();
    }
}

database\factories\ItemFactory.php

<?php

namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;
use App\Models\Item;
class ItemFactory extends Factory
{
    protected $model = Item::class;
    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'title'            => $this->faker->text,
            'description'            => $this->faker->text,
            'min_quantity' => $this->faker->numberBetween(1, 10)
        ];
    }
}

app\Models\Item.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Item extends Model
{
    use HasFactory;
}

Ví dụ 1

app\Http\Controllers\HomeController.php

<?php
namespace App\Http\Controllers;
use App\Models\User;
use App\Models\Message;
use DB;
use Illuminate\Http\Request;
class HomeController extends Controller {
  /**
   * Create a new controller instance.
   *
   * @return void
   */
  public function __construct() {
    // $this->middleware('auth');
  }
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Contracts\Support\Renderable
   */
  public function index() {
    return view('home');
  }
  public function test() {
    $tests = DB::table("items")
    ->select("*",DB::raw('SUM(items_count.quantity) as total_quantity'))
    ->join("items_count","items_count.id_item","=","items.id")
    ->groupBy("items.id")
    ->having("total_quantity","<",DB::raw("items.min_quantity"))
    ->get();
    return view('tests')->with(compact('tests'));
  }
}
// select *, SUM(items_count.quantity) as total_quantity from `items` inner join `items_count` on `items_count`.`id_item` = `items`.`id` group by `items`.`id` having `total_quantity` < items.min_quantity

resources\views\tests.blade.php

@extends('layouts.app')
@section('content')
<div class="container">
  <div class="row justify-content-center">
    <div class="col-md-8">
      <div class="card">
        <div class="card-header">Dashboard</div>
        <div class="card-body">
          @foreach ($tests as $test)
            {{ $test->title }}  <hr/>
          @endforeach
        </div>
      </div>
    </div>
  </div>
</div>
@endsection

Ví dụ 2

app\Http\Controllers\HomeController.php

<?php
namespace App\Http\Controllers;
use App\Models\User;
use App\Models\Message;
use DB;
use Illuminate\Http\Request;
class HomeController extends Controller {
  /**
   * Create a new controller instance.
   *
   * @return void
   */
  public function __construct() {
    // $this->middleware('auth');
  }
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Contracts\Support\Renderable
   */
  public function index() {
    return view('home');
  }
  public function test() {
    // $tests = DB::table("items")
    // ->select("*",DB::raw('SUM(items_count.quantity) as total_quantity'))
    // ->join("items_count","items_count.id_item","=","items.id")
    // ->groupBy("items.id")
    // ->having("total_quantity","<",DB::raw("items.min_quantity"))
    // ->get();
    $tests = DB::table("items_count")
    ->select('items_count.*',DB::raw("SUM(items_count.quantity) as total_quantity"))
    ->groupBy('items_count.id_item','items_count.price')
    ->having("total_quantity","<",2)
    ->get();
    return view('tests')->with(compact('tests'));
  }
}
// select `items_count`.*, SUM(items_count.quantity) as total_quantity from `items_count` group by `items_count`.`id_item`, `items_count`.`price` having `total_quantity` < 2

resources\views\tests.blade.php

@extends('layouts.app')
@section('content')
<div class="container">
  <div class="row justify-content-center">
    <div class="col-md-8">
      <div class="card">
        <div class="card-header">Dashboard</div>
        <div class="card-body">
          @foreach ($tests as $test)
            {{ $test->id }}  <hr/>
          @endforeach
        </div>
      </div>
    </div>
  </div>
</div>
@endsection