Converting MS Access Queries to Laravel Query Builder

Just use normal sql with getPdo ().

DB::select

I appreciate you responding, but I am trying to use Eloquent whenever it is at all possible. Thanks.

Sometimes, you can spend a great deal of time trying to make a query work "the eloquent way". Don't waste time. First, eloquent can't do everything. It's an ORM and not a drop in replacement for everything. There are many times you have to do raw queries since eloquent doesn't even have all of the SQL language implemented. Like if you want to do CASE statements, or REPLACE statements, you have to do it raw. Eloquent doesn't implement those. Eloquent imlements probably the most widely used sql functions, but it's probably only about 5% of what's available. ORMs are mainly for rapid prototyping. ORMs will always be slower than raw sql (and take more memory/resources), as php has to first build up the query that the ORM generates in order to send it to the db. Knowing how to actually use sql is far more important in coding than knowing how to use an ORM.

The main thing you have to do is make sure the query is safe from sql injections, mainly by binding parameters (ESPECIALLY IF SOMETHING IS COMING FROM USER INPUT). You can always do things like:

$users = DB::select('select * from users where active = ?', [1]);

or use placeholders instead of ?'s

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Examples above taken from the user guide. Here's more: https://laravel.com/docs/5.6/queries#raw-expressions

Most of my complex reports use pure sql.

And for complex stuff I go directly to getPdo here is sample usage https://laracasts.com/discuss/channels/guides/getpdo-usage

Also eloquent it's not a language rather a shortcut and is converted to normal regular PDO queries at runtime.

Also many folks don't have msaccess, I do. For many complex queries I have a duplicate database on local where I can use an odbc connection to mysql, then I use the query designer in access, get the generated query (sql) and just tweak (convert as needed) to the proper pdo query. Meaning add the necessary bindings and such.

But msaccess makes it so much easier. It just seems to add so many parenthesis that's un-needed in the pdo query.

@jlrdw, that's a good idea, to use Access that way. Though, I just tried it with my full query and received an error that doesn't tell me much:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near... with my SQL code...

I believe it has to do with the fact that my Access db has a bunch of field names with spaces and other characters.

UPDATE: I removed the spaces and characters, as well as the extra table names and brackets from the Access SQL and it runs fine now.

Hi @wallyj how do you connect MS Access db (mdb) in laravel? BTW I can't use the "Mysql way". Do I have to create my own connection object and etc?

An odbc data source.

@jlrdw but laravel didn't support odbc out of the box. which plugin have you installed? Is this one https://github.com/StevenBock/odbc-driver or this one https://github.com/techscope/laravel-sqlserver ?

Thanks.

I was referring to odbc to open mysql data in access, not laravel. I dump data to local and via odbc and run monthly reports in access, has a great report builder.

thanks. @jlrdw true access has a much more powerful UI IDE/designer.