I'm setting up an API using the fatfree framework and want to use sqlite in development. I followed the docs (https://fatfreeframework.com/3.7/databases). At the bottom of that page, I learned about using a view containing joins instead of joining while querying. I thought that a very good idea. However, it seems to be working in mysql but not in sqlite.
$prefs = new Mapper($this->db, 'userpreferences');
$prefs->load(array('login_id=?', $userLoginId));
In mysql this works. In sqlite this yields the following error:
PDO: near "FROM": syntax error
However, if use the view in an exec() everything works in mysql and sqlite.
$prefs = $this->db->exec('SELECT * FROM userpreferences WHERE login_id=?', $userLoginId);
Is this a (known) bug or am I missing something? (php: 7.4, F3: 3.7.3, sqlite3: 3.32.2, mysql: 8.0.25)
I stumbled upon same issue and you can get around it by editing lib/db/sql.php file on line 342: https://github.com/jlazic/fatfree/commit/ad5a86819a60b51b124660fddd7ad5eec61ab48f
Issue seems to be that sqlite uses type=view for views, while f3 only queries for metadata where type=table.