Invalid text representation: 7 ERROR: invalid input syntax for type bigint - Laravel API

Question

I'm writing API using Laravel 8. I try to get some books following the query like this: This is my model Book.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use App\Models\Review;
use App\Models\Discount;
use App\Models\Category;
use App\Models\Author;
use Illuminate\Support\Facades\DB;

class Book extends Model
{
    use HasFactory;

    protected $hidden = ['id', 'category_id', 'author_id'];
    public $timestamps = false;
    public function reviews()
    {
        return $this->hasMany(Review::class);
    }

    public function discount()
    {
        return $this->hasOne(Discount::class);
    }

    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function author()
    {
        return $this->belongsTo(Author::class);
    }

    public function scopeGetDiscountPrice($query)
    {
        return $query->addSelect([
            'discount_price' => Discount::select('discount_price')
                ->whereColumn('book_id', 'books.id')
                ->whereBetween(now(), ['discount_start_date', 'discount_end_date'])
                ->orWhere(function ($query) {
                    $query->whereDate('discount_end_date', '>=', now())
                        ->WhereNull('discount_end_date');
                })
        ]);
    }


    public function scopeGetBookOnSale($query)
    {
        return $query->getDiscountPrice()
            ->orderByRaw('book_price - discount_price DESC NULLS LAST');
    }

Here is my BookController

/**
     * Display the specified resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function showOnSale(){
        return Book::getBookOnSale()->get();
    }
     

And here is my migrate of Book:

<?php

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

class CreateBooksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->id();
            $table->foreignId('category_id')->constrained('categories');
            $table->foreignId('author_id')->constrained('authors');
            $table->tinyText('book_title');
            $table->text('book_summary');
            $table->decimal('book_price', 5, 2, true);
            $table->string('book_cover_photo', 20)->nullable();
        });
    }

When I test in postman, i get the error: Postman Error

Illuminate\Database\QueryException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: "onsale" (SQL: select * from "books" where "books"."id" = onsale limit 1) in file C:\Users\bnm78\Desktop\bookworm-app\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 692

#0 C:\Users\bnm78\Desktop\bookworm-app\vendor\laravel\framework\src\Illuminate\Database\Connection.php(652): Illuminate\Database\Connection->runQueryCallback('select * from "...', Array, Object(Closure)) #1 C:\Users\bnm78\Desktop\bookworm-app\vendor\laravel\framework\src\Illuminate\Database\Connection.php(360): Illuminate\Database\Connection->run('select * from "...', Array, Object(Closure))

I dont know what is error and how to fix it.. Please help me!

Answer 1

I got this error because I had manually made a "sessions" table for a Session model I had planned to use. I made the ID column the standard bigInt, but Laravel's database session driver looks to this table by default and uses a string for the ID, thus the error.

The fix is either to:

1. Not make your own sessions table,
2. Make it based on what the driver expects from the Laravel docs, or
3. Update config/session.php to use a different table (and then make that one properly).