Eloquent model cannot select from MySQL view

Question

Has MySQL view 'histstat'.

In MySQL select * from histstat works fine.

Laravel model is very simple:

class HistStat extends Model
{
    use HasFactory;

    protected $table = 'histstat';

    protected $fillable = ['day', 'total', 'paid'];
}

Then I want to get first 14 records of hisstat:

$dynamic = HistStat::all()->slice(14);

... and execution ends with error SQLSTATE[42000]: Syntax error or access violation: 1055 (SQL: select * from 'histstat')

When I try to use table-based model ($dynamic = History::all()->slice(14);) - everything works fine.

So, the problem in MySQL view + Laravel.

How to use view-based model in Laravel ?

sorry don't get your problem. but you can't use laravel eloquent query in sql view. — Abdul Rehman, Sep 20 '21 at 05:36
Again, if you have a solution, post an answer. DO NOT edit it into your post. The question is for the question, _not_ answers — Zoe, Sep 20 '21 at 14:00

score 0 · Answer 1 · edited Sep 20 '21 at 13:28

0

For the SQL error,

In config\database.php in "mysql" array

Set 'strict' => false

I think this one will help Laravel: Syntax error or access violation: 1055 Error.

For the first 14 records, you can do it

HistStat::oldest()->take(14)->get();

better than all() because I do not recommend using it if you have a huge table that will be load on the server because all() get all records select * from "users"

edited Sep 20 '21 at 13:28

Zoe

27,060
21
118
148

answered Sep 20 '21 at 05:39

Mohamed Gamal Eldin

680
1
6
18

Sorry, `'strict' => false` do nothing. Error appeared again – Valery Bulash Sep 20 '21 at 07:17
clear cache please and try again. – Mohamed Gamal Eldin Sep 20 '21 at 07:42
Still no effect – Valery Bulash Sep 20 '21 at 09:32
1

`'strict' => false` is not enough to work safe with MySQL view. See update – Valery Bulash Sep 20 '21 at 13:18

score 0 · Answer 2 · answered Sep 20 '21 at 14:06

0

In config/datatable.php (mysql section):

Change strict to false
Add / change in options - PDO::ATTR_EMULATE_PREPARES => true

answered Sep 20 '21 at 14:06

Valery Bulash

445
4
22

Eloquent model cannot select from MySQL view

2 Answers2