Should we avoid using raw statements/functions in Eloquent/DB?
I have a number of Eloquent repositories in my Laravel project. For the most part, I use Eloquent to query my database, but in a few places I use the Query Builder, and when I really need optimization, I use raw queries (through DB::statement()). Of course, this might defeat Eloquent's flexibility, since raw statements can change from MySQL to SQLite, etc.
Is it therefore good practice to keep this all in one repo? Should I create a new repository instead and if so what should it be called (QueryBuilderMySQLEmployeeRepository sounds way too cumbersome)?
Sounds like you're using the raw queries exactly for what they should be used for - something that can't be done well enough by Eloquent that you're happy with (performance would be the number 1 reason for this imo).
If you are using repositories already, I would make different implementations for different drivers (as that's half of their intention, no?). That way you can switch it out (maybe use env files or something to control which class the repo interface binds to at runtime).
If you're not using repositories and don't actually see changing your db system then you will likely get away just fine with coding it in directly in one place. All a matter of taste / deciding how much you need to code for the future (that may or may not happen).
My question is more about whether or not it's a good idea to mix Eloquent and raw SQL queries (using the Query Builder) in the same repo, and if not, what to call the new DB Query Builder repo. It seems like too much work creating an entirely new repo just for a single raw query, but at the same time, it doesn't feel right either mixing raw queries with Eloquent in the same repo class. What's the best way to handle this from an architecture standpoint?
To answer your other question, I know they are converted to regular queries, but they are converted into "portable" SQL depending on your driver. But if you do something like DB::statement("SELECT * FROM employees WHERE creation_timestamp > UTC_TIMESTAMP()"), then this is MySQL specific, since UTC_TIMESTAMP() doesn't occur in SQLite, so it's arguable that you'd need to break this out into a different class. Although this doesn't feel right either.
I was just wondering what other people do and what is the accepted way to handle this.
The normal OO way is to have an abstract class for hand made queries, and subclass for each specific database you expect to use. Then have a factory method that instantiates the correct subclass based on an environment/configuration variable.
The way I've done this, is to use Eloquent where possible, and if I want to use a raw DB query for, say MySQL, then I'll check for which driver I'm using. If I'm using MySQL, then I'll use the raw DB query fine tuned for MySQL. Otherwise, I'll let Eloquent take over.
This way, I get both Database Agnosticism, and optimized queries for certain drivers.
@tylernathanreed , i liked this approach, but how and when you switch from different database drivers? You use different classes within a factory like @ColBatGuano says?
I don't have sure about that, but i think something like this will be good solution:
Implement a RepositoryInterface.
Create a concrete EloquentRepository class.
Create a concrete MySQLRepository and a concrete SQLiteRepository, both extending EloquentRepository and implementing RepositoryInterface.
Provide a RepositoryFactory to bind the correct class into the container.
