Core\Database
The Core\Database is a database connector and a facade for Illuminate\Database\Capsule\Manager, the one that have been used in Laravel. Will return the database manager (the Capsule) upon calling the method Core\Database::connect().
This is an example on how to connect to the database and do a database query:
//you can either do this
$user = Database::table('users')->where('id',1)->get();
//or do this
$database = Core\Database::connect();
$user = $database->table('users')->where('id',1)->get();More of it are covered in this wiki page.
| No | Title |
|---|---|
| 1 | Configure the Database |
| 2 | Database Queries (select, insert, update, delete) |
| 3 | Query Builder |
| 4 | Selects |
| 5 | Joins |
| 6 | Models (the ORM) |
Since we use Laravel's database manager, you can head to this page to read their documentations as well.
The config\database.php contains all the configuration need to connect to the database.
return
[
'enabled' => false,
'settings' =>
[
'host' => 'localhost',
'driver' => 'mysql',
'database' => 'dss',
'username' => 'root',
'password' => 'root',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
]
];
- enabled - choose whether you want to connect the database on boot
- host - your database server
- driver - the database driver: mysql, sqlsrv (Microsoft SQL), sqlite
- database - the database name
- username - the username
- password - the password
- charset - database character set
- collation - database collation
- prefix - the database prefix
This will guide you on how to run raw queries on the database using the Database facade.
Make sure that on the very beginning of the file, you use the Core\Database
use Core\Database;Running a select statement (returns rows from the select statement):
$result = Database::select('SELECT * from users');
// array of rowsor with named bindings:
$result = Database::select('SELECT * from users where id = :id', ['id' => 1]);
// array of rowsRunning an insert statement (returns number of rows inserted):
$inserted = Database::insert('insert into users (username) values ("Dayle")');
// return 1or with named bindings:
$inserted = Database::insert('insert into users (id, username) values (?, ?)', [2, 'Dayle']);
// return 1Running an update statement (returns number of rows affected):
$updated = Database::update('update users set votes = 100 where username = "John"');
// return 1or with named bindings:
$updated = Database::update('update users set votes = :votes where username = :username', ['votes' => 200, 'username' => 'John']);
// return 1Running a delete statement (returns number of rows affected):
$deleted = Database::delete('delete from users where username = "John"');
// return 1or with named bindings:
$deleted = Database::delete('delete from users where username = ?', ['John']);
// return 1The database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application, and works on all supported database systems.
You can also the Query Builder by accessing the database in the App container.
Make sure that on the very beginning of the file, you use the Core\Database
use Core\Database;Like raw queries, the get method returns an array of results where each result is an instance of the object. You may access each column's value by accessing the column as a property of the object:
$users = Database::table('users')->get();
foreach($users as $user){
echo $user->username;
echo $user->votes;
}$users = Database::table('shops');
$shop = $users->where('location' => 'Kuala Lumpur, Malaysia')->first();
echo $shop->name;You can perform a chain of where Clauses together like this:
$users = Database::table('users')->where('username', 'John')->get();
$shops = Database::table('users')->where('enabled',true)->where('votes', '>', '100')->get();The query builder also provides a variety of aggregate methods, such as count, max, min, avg, and sum. You may call any of these methods after constructing your query:
$users = Database::table('users')->count();
$price = Database::table('orders')->max('price');Of course, you may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query:
$users = Database::table('users')->select('name', 'email')->get();You can also alias the table columns:
$users = Database::table('users')->select('name', 'email as user_email')->get();Or use where clause altogether:
$users = Database::table('users')->select('name', 'email')->where('name', 'John')->first();You may not want to retrieve duplicates, so you can force to return distinct values:
$users = Database::table('users')->select('name', 'email')->distinct()->get();The first argument for the join method is the table name that you want to join to, while the rest is the join constraints.
You can perform an Inner Join by doing this:
$users = Database::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();You can also use Left Join instead of the Inner Join, by using the leftJoin method.
$users = Database::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();For advanced Joins, you can refer to the Laravel documentation here.
The Eloquent ORM developed by Laravel provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding "Model" which is used to interact with that table.
All models are placed in the app/Model directory.
For example, if you have a table called "posts", you will create a model file named Post.php. To create a model for posts:
<?php
namespace Model;
use Illuminate\Database\Eloquent\Model as Eloquent;
class Post extends Eloquent
{
// Really. Nothing's here...
}Save it in the app\Model folder as Post.php and then reconfigure the autoloader by performing a composer dump-autoload command in the Command Prompt / Terminal.
You can then use this model anywhere in the project:
$posts = User::where('id', 1)->first();
echo $posts->title;You can also perform various queries same like raw queries and query builder above.
You may not want to map the Post model with the posts table in the database. So you can also tell Eloquent the exact table name:
<?php
namespace Model;
use Illuminate\Database\Eloquent\Model as Eloquent;
class Post extends Eloquent
{
/**
* The table associated with the model.
*
* @var string
*/
protected $table = 'blog_posts';
}Eloquent will also assume that each table has a primary key column named id. You may define a $primaryKey property to override this convention.
<?php
namespace Model;
use Illuminate\Database\Eloquent\Model as Eloquent;
class Post extends Eloquent
{
/**
* The table associated with the model.
*
* @var string
*/
protected $primary_keys = 'post_id';
}To insert a record to the database:
$posts = new Model\Post;
$posts->title = 'New Title';
$posts->save();
To update a record in the database, you can do this:
$posts = Model\Post::find(1);
$posts->title = 'New Title';
$posts->save();To delete a record:
$posts = Model\Post::where('id',2)->delete();A one-to-one relationship is a very basic relation. For example, a User model might be associated with one Phone. To define this relationship, we place a phone method on the User model. The phone method should return the results of the hasOne method on the base Eloquent model class:
<?php
namespace Model;
use Illuminate\Database\Eloquent\Model as Eloquent;
class User extends Eloquent
{
/**
* Get the phone record associated with the user.
*/
public function phone()
{
return $this->hasOne('Model\Phone');
}
}Which will allow you to access the phone
$phone = User::find(1)->phone;Eloquent assumes the foreign key of the relationship based on the model name. In this case, the Phone model is automatically assumed to have a user_id foreign key. If you wish to override this convention, you may pass a second argument to the hasOne method:
return $this->hasOne('Model\Phone', 'foreign_key');A "one-to-many" relationship is used to define relationships where a single model owns any amount of other models. For example, a blog post may have an infinite number of comments. Like all other Eloquent relationships, one-to-many relationships are defined by placing a function on your Eloquent model:
<?php
namespace Model;
use Illuminate\Database\Eloquent\Model as Eloquent;
class Post extends Eloquent
{
/**
* Get the comments for the blog post.
*/
public function comments()
{
return $this->hasMany('App\Comment');
}
}Which then allows you to do this:
//find a post with id = 1
$comments = Model\Post::find(1)->comments;
//access each Model\App's property
foreach($comments as $comment){
echo $comment->content;
}Like the hasOne method, you may also override the foreign and local keys by passing additional arguments to the hasMany method:
return $this->hasMany('Model\Comment', 'foreign_key');
return $this->hasMany('Model\Comment', 'foreign_key', 'local_key');Many-to-many relations are slightly more complicated than hasOne and hasMany relationships. An example of such a relationship is a user with many roles, where the roles are also shared by other users. For example, many users may have the role of "Admin". To define this relationship, three database tables are needed: users, roles, and role_user. The role_user table is derived from the alphabetical order of the related model names, and contains the user_id and role_id columns.
Many-to-many relationships are defined by writing a method that calls the belongsToMany method on the base Eloquent class. For example, let's define the roles method on our User model:
<?php
namespace Model;
use Illuminate\Database\Eloquent\Model as Eloquent;
class User extends Eloquent
{
/**
* The roles that belong to the user.
*/
public function roles()
{
return $this->belongsToMany('Model\Role');
}
}Once the relationship is defined, you may access the user's roles using the roles dynamic property:
$user = App\User::find(1);
foreach ($user->roles as $role) {
//
}
