Laravel Db Select

Laravel has a great database mechanism called Eloquent, also a powerful Query Builder, but sometimes it makes sense to just use plain SQL, in the form of Raw Queries. In this article, I will show you the most common examples of this approach.

I have a DigitalOcean server provisioned by Laravel Forge and I have a standard Laravel 8 application deployed to it. I also setup a node.js based daemon using the Laravel Forge UI. Essentially, it listens to some web socket server and executes an ar. Laravel - Update Records - We can update the records using the DB facade with update method. The syntax of update method is as shown in the following table. Currently Lumen supports four database systems: MySQL, PostgreSQL, SQLite, and SQL Server. You may use the DB. configuration options in your. Env configuration file to configure your database settings, such as the driver, host, username, and password. Laravel includes the ability to seed your database with test data using seed classes. All seed classes are stored in the database/seeders directory. By default, a DatabaseSeeder class is defined for you.

We mostly required to get sum of amount, salary etc in laravel. We can also get sum of column using mysql SUM. We have two way to get sum of column value. First we can use laravel sum of query builder and another one we can use with directly with select statement using DB::raw.

1. Most Typical: selectRaw() with Avg/Sum/Count Calculations

If you need to perform groupBy() and then use some aggregation function from MySQL, like AVG() or COUNT(), it’s useful to perform a Raw Query for that specific section.

Example from Laravel documentation:

Another example:

Another example – we can even perform avg() and count() in the same statement.

2. Filtering YEARS: groupByRaw, orderByRaw and havingRaw

What if you want to add some SQL calculations inside of “group by” or “order by”?
We have methods like groupByRaw() and orderByRaw() for this. Also, we can use additional “where” statement after grouping, by “having” SQL statement with havingRaw().

For example, how to group by a YEAR of a certain date/time field?

3. Calculating one field with sub-query: selectRaw()

If you want to return one specific column as a calculation from other columns, and you want that calculation to happen in SQL query, here’s how it can look:

Another example – CASE statement of SQL:

4. Old SQL Query? Just use DB::select()

A pretty typical example is when you have an SQL statement from some older project, and you need to convert it to Eloquent or Query Builder.

Guess what, you don’t have to. DB::select() is a perfectly fine statement.

5. DB::statement() – Usually in Migrations

If you need to execute some SQL query, without processing any results, like INSERT or UPDATE without any parameters, you can use DB::statement().

In my experience, it’s often used in database migrations, when some table structure changes and old data needs to be updated with a new structure.

Also, DB::statement() can perform any SQL query with schema, outside of columns or values.

Warning: be careful with parameters, always validate them

Short final notice.

The biggest danger in Raw Queries is that they are not automatically secured, so if you are passing any parameters to the query, please triple-check and validate that they have correct values (like a number and not a string) and in a correct format. You can read more in this article.

  • Introduction
  • Running SQL Queries

Introduction

Almost every modern web application interacts with a database. Laravel makes interacting with databases extremely simple across a variety of supported databases using raw SQL, a fluent query builder, and the Eloquent ORM. Currently, Laravel provides first-party support for four databases:

  • MySQL 5.7+ (Version Policy)
  • PostgreSQL 9.6+ (Version Policy)
  • SQLite 3.8.8+
  • SQL Server 2017+ (Version Policy)

Configuration

Laravel db select

The configuration for Laravel's database services is located in your application's config/database.php configuration file. In this file, you may define all of your database connections, as well as specify which connection should be used by default. Most of the configuration options within this file are driven by the values of your application's environment variables. Examples for most of Laravel's supported database systems are provided in this file.

By default, Laravel's sample environment configuration is ready to use with Laravel Sail, which is a Docker configuration for developing Laravel applications on your local machine. However, you are free to modify your database configuration as needed for your local database.

SQLite Configuration

SQLite databases are contained within a single file on your filesystem. You can create a new SQLite database using the touch command in your terminal: touch database/database.sqlite. After the database has been created, you may easily configure your environment variables to point to this database by placing the absolute path to the database in the DB_DATABASE environment variable:

To enable foreign key constraints for SQLite connections, you should set the DB_FOREIGN_KEYS environment variable to true:

Microsoft SQL Server Configuration

To use a Microsoft SQL Server database, you should ensure that you have the sqlsrv and pdo_sqlsrv PHP extensions installed as well as any dependencies they may require such as the Microsoft SQL ODBC driver.

Configuration Using URLs

Typically, database connections are configured using multiple configuration values such as host, database, username, password, etc. Each of these configuration values has its own corresponding environment variable. This means that when configuring your database connection information on a production server, you need to manage several environment variables.

Some managed database providers such as AWS and Heroku provide a single database 'URL' that contains all of the connection information for the database in a single string. An example database URL may look something like the following:

These URLs typically follow a standard schema convention:

For convenience, Laravel supports these URLs as an alternative to configuring your database with multiple configuration options. If the url (or corresponding DATABASE_URL environment variable) configuration option is present, it will be used to extract the database connection and credential information.

Read & Write Connections

Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements. Laravel makes this a breeze, and the proper connections will always be used whether you are using raw queries, the query builder, or the Eloquent ORM.

To see how read / write connections should be configured, let's look at this example:

Note that three keys have been added to the configuration array: read, write and sticky. The read and write keys have array values containing a single key: host. The rest of the database options for the read and write connections will be merged from the main mysql configuration array.

You only need to place items in the read and write arrays if you wish to override the values from the main mysql array. So, in this case, 192.168.1.1 will be used as the host for the 'read' connection, while 192.168.1.3 will be used for the 'write' connection. The database credentials, prefix, character set, and all other options in the main mysql array will be shared across both connections. When multiple values exist in the host configuration array, a database host will be randomly chosen for each request.

Laravel Db Select First

The sticky Option

The sticky option is an optional value that can be used to allow the immediate reading of records that have been written to the database during the current request cycle. If the sticky option is enabled and a 'write' operation has been performed against the database during the current request cycle, any further 'read' operations will use the 'write' connection. This ensures that any data written during the request cycle can be immediately read back from the database during that same request. It is up to you to decide if this is the desired behavior for your application.

Running SQL Queries

Once you have configured your database connection, you may run queries using the DB facade. The DB facade provides methods for each type of query: select, update, insert, delete, and statement.

Running A Select Query

Array

To run a basic SELECT query, you may use the select method on the DB facade:

The first argument passed to the select method is the SQL query, while the second argument is any parameter bindings that need to be bound to the query. Typically, these are the values of the where clause constraints. Parameter binding provides protection against SQL injection.

The select method will always return an array of results. Each result within the array will be a PHP stdClass object representing a record from the database:

Using Named Bindings

Instead of using ? to represent your parameter bindings, you may execute a query using named bindings:

Running An Insert Statement

To execute an insert statement, you may use the insert method on the DB facade. Like select, this method accepts the SQL query as its first argument and bindings as its second argument:

Running An Update Statement

The update method should be used to update existing records in the database. The number of rows affected by the statement is returned by the method:

Running A Delete Statement

The delete method should be used to delete records from the database. Like update, the number of rows affected will be returned by the method:

Running A General Statement

Some database statements do not return any value. For these types of operations, you may use the statement method on the DB facade:

Running An Unprepared Statement

Sometimes you may want to execute an SQL statement without binding any values. You may use the DB facade's unprepared method to accomplish this:

{note} Since unprepared statements do not bind parameters, they may be vulnerable to SQL injection. You should never allow user controlled values within an unprepared statement.

Implicit Commits

When using the DB facade's statement and unprepared methods within transactions you must be careful to avoid statements that cause implicit commits. These statements will cause the database engine to indirectly commit the entire transaction, leaving Laravel unaware of the database's transaction level. An example of such a statement is creating a database table:

Db::select In Laravel

Please refer to the MySQL manual for a list of all statements that trigger implicit commits.

Using Multiple Database Connections

If your application defines multiple connections in your config/database.php configuration file, you may access each connection via the connection method provided by the DB facade. The connection name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file or configured at runtime using the config helper:

You may access the raw, underlying PDO instance of a connection using the getPdo method on a connection instance:

Laravel db select array

Listening For Query Events

If you would like to specify a closure that is invoked for each SQL query executed by your application, you may use the DB facade's listen method. This method can be useful for logging queries or debugging. You may register your query listener closure in the boot method of a service provider:

Database Transactions

You may use the transaction method provided by the DB facade to run a set of operations within a database transaction. If an exception is thrown within the transaction closure, the transaction will automatically be rolled back. If the closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method:

Handling Deadlocks

The transaction method accepts an optional second argument which defines the number of times a transaction should be retried when a deadlock occurs. Once these attempts have been exhausted, an exception will be thrown:

Manually Using Transactions

If you would like to begin a transaction manually and have complete control over rollbacks and commits, you may use the beginTransaction method provided by the DB facade:

You can rollback the transaction via the rollBack method:

Lastly, you can commit a transaction via the commit method:

{tip} The DB facade's transaction methods control the transactions for both the query builder and Eloquent ORM.

Connecting To The Database CLI

If you would like to connect to your database's CLI, you may use the db Artisan command:

If needed, you may specify a database connection name to connect to a database connection that is not the default connection: