Laravel Eloquent ORM y Query Builder

Índice de contenidos

Vamos a ver las diferentes formas de realizar consultas SQL con Laravel, utilizando Eloquent ORM y Query Builder.

Laravel Utilizando Eloquent ORM

Consultas básicas

Devuelve una colección de objetos de todos los usuarios.

// retorna object(Illuminate\Database\Eloquent\Collection)
$users = User::all();
$users = User::get();

Si buscamos un solo usuario, nos devuelve el modelo User.

// retorna object(App\User)
$user = User::find(10);
$user = User::all()->first();

Para crear un registro.

$user = new User;
$user->username = "user";
$user->save();
// save retorna un boolean, podrían usarlo así:
if( $user->save() ){
    var_dump($user->id);
}

Para actualizar un registro.

$user = User::find(10);
$user->username = "new user";
$user->save();

Para eliminar un registro.

$user = User::find(10);
$user->delete();

Usando where.

$user = User::where("estado","=",1)->find(10);

Para que nos devuelva una instancia de Illuminate\Pagination\LengthAwarePaginator y pagine cada 10 registros.

$users = User::where("estado","=",1)->paginate(10);
// En la vista
foreach ($users as $key => $user) {
  // $user es una Instancia de la clase User
}

Seleccionamos solo algunas columnas.

$users = User::where("estado","=",1)->select("id","username")->paginate(10);

Si queremos usar alguna función de mysql, podemos usar DB::raw().

$users = User::where("estado","=",1)
    ->select(DB::raw("id,username, DATE_FORMAT(created_at,'%d/%m/%Y %h:%i %p') AS fecha"))
    ->paginate(10);

Otras funciones

$users = User::where("estado","=",1)
    ->whereNotNull('updated_at')
    ->whereNull('email')
    ->whereIn('id', [1, 2, 3])
    ->whereBetween('edad', [1, 30])
    ->where('username','like','%ad%')
    ->orderBy('username')
    ->orderBy('created_at','desc')
    ->skip(10)->take(5)
    ->get();

Obtener las consultas generadas

Para ver la consulta SQL que genera usamos DB::getQueryLog() esta función devuelve un array con todas las consultas que se han generado.

En Laravel 4

// En Laravel 4
$log = DB::getQueryLog();
var_dump($log);

En Laravel 5

DB::enableQueryLog();
$log = DB::getQueryLog();
var_dump($log);
// Multiples conexiones
DB::connection('connection1')->enableQueryLog();
DB::connection('connection1')->getQueryLog();
DB::connection('connection2')->enableQueryLog();
DB::connection('connection2')->getQueryLog();

Para que los registros devuelvan en array o formato json.

$users = User::where("estado","=",1)->get()->toArray();
$users = User::where("estado","=",1)->get()->toJson();
$users = User::where("estado","=",1)->first()->toArray();

Si queremos usar un condicional al momento de generar las consultas.

$users = User::where("estado","=",1);
if($buscar)
    $users = $users->where('username','like','%ad%');
$users = $users->get();

Consultas con Join y Left Join

Usando Join y Left Join

$users = User::join("roles","users.roles_id","=","roles.id")
    ->where('users.estado','=',1)
    ->get();
 
$users = User::leftJoin("roles","users.roles_id","=","roles.id")
    ->where('users.estado','=',1)
    ->get();
 
$users = User::join("roles","users.roles_id","=","roles.id")
    ->leftJoin('posts',function($join){
        $join->on('users.posts_id','=','posts.id')->where('posts.estado','=',1);
    })
    ->where('users.estado','=',1)
    ->get();
 
$users = User::join("roles","users.roles_id","=","roles.id")
    ->leftJoin(DB::raw("(SELECT * FROM posts where posts.estado=1) as posts"),function($join){
        $join->on('users.posts_id','=','posts.id');
    })
    ->where('users.estado','=',1)
    ->get();

Laravel usando Query Builder para generar SQL

Consultas varias

DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('orders')
            ->whereRaw('orders.user_id = users.id');
    })
    ->get();
 
$users = DB::table('orders')
    ->select('department', DB::raw('SUM(price) as total_sales'))
    ->groupBy('department')
    ->havingRaw('SUM(price) > 2500')
    ->get();

Para insertar un registro.

DB::table('users')->insert(
    ['username' => 'editor', 'edad' => 20]
);

Para actualizar registros.

DB::table('users')
    ->where('id', 1)
    ->update(['edad' => 20]);

Para eliminar registros.

DB::table('users')->delete();
DB::table('users')->where('edad', '<', 20)->delete();

Usando Join y Left Join.

$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();
 
$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

Actualizar registros entre dos tablas

DB::table('posts')
    ->join('users', 'posts.users_id', '=', 'users.id')
    ->where('estado','=',1)
    ->update(array('posts.order'=>1));

Transacciones

DB::beginTransaction();
$user = new User;
$user->username = "user";
if($user->save())
    DB::commit();
else
    DB::rollback();

Procedimientos almacenados

$sql = "call my_procedure(?,?)";
DB::select($sql,array(1,20)); // retorna un array de objetos.

$db = DB::connection();
$stmt = $db->getPdo()->prepare("CALL my_procedure(?,?)");
$stmt->execute(['buscar',5]);
$result = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

Otras formas de ejecutar consultas en Laravel

$sql = "SELECT * FROM users WHERE estado=? AND edad<?";
DB::select($sql,array(1,20));
 
$sql = "SELECT * FROM users WHERE estado=? AND edad<?";
DB::statement($sql,array(1,20));
 
$sql = "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS `users`;DROP TABLE IF EXISTS `roles`;";
DB::unprepared($sql);
 
$file_sql = './data/consultas.sql';
$sql = file_get_contents($file_sql);
DB::unprepared($sql);
 
// Si pierdes la conexión, ejemplo cuando eliminas y vuelves a crear la base de datos, esta función te permite reconectar y seguir trabajando.
DB::reconnect();

Relaciones en Laravel

Convenciones de Eloquent Model

La tabla de un Modelo se le agrega la s, en plural.
La tabla de la clase Post sería posts.
La tabla de la clase ProductImage sería product_images.
El campo primary key de una tabla debe tener el nombre de id, con el atributo AUTO_INCREMENT.

Estructura básica de la clase Post

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
    //
}

Dentro de la clase podemos definir diferente a lo predeterminado

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
    // cambiar el nombre de la tabla
    protected $table = 'my_post';
 
    // Por defecto id es la clave primaria, podemos definir un campo diferente con la propiedad primaryKey
    protected $primaryKey = 'id_post';
 
    // Todo modelo debe tener 2 campos created_at y updated_at, si no usamos esos campos en nuestra tabla
    // definimos de la siguiente manera
    public $timestamps = false;
 
    // Definir un nombre de conexión diferente
    protected $connection = 'connection-name';
 
    // Atributos que pueden ser asignados en App\Post::create(['name' => 'Article 1']);
    protected $fillable = ['name'];
 
    // Todos los atributos se pueden asignar excepto price
    protected $guarded = ['price'];
}

Uno a Uno

Ejemplo User y Phone estan relacionados uno a uno

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
    /**
     * Get the phone record associated with the user.
     */
    public function phone()
    {
        return $this->hasOne('App\Phone');
        // Si el id tienen diferentes nombres
        return $this->hasOne('App\Phone', 'user_id', 'local_key');
    }
}

Definimos la otra clase

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Phone extends Model
{
    /**
     * Get the user that owns the phone.
     */
    public function user()
    {
        return $this->belongsTo('App\User');
        // Si el id tienen diferentes nombres
        return $this->belongsTo('App\User', 'user_id', 'local_key');
    }
}

Uno a Muchos

Un Post tiene muchos Comentarios

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
    /**
     * Get the comments for the blog post.
     */
    public function comments()
    {
        return $this->hasMany('App\Comment');
        // Si el id tienen diferentes nombres
        return $this->hasMany('App\Comment', 'post_id', 'local_key');
    }
}

Un Comentario pertenece a un Post

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Comment extends Model
{
    /**
     * Get the post that owns the comment.
     */
    public function post()
    {
        return $this->belongsTo('App\Post');
    }
}

Muchos a Muchos

Un Usuario tiene varios Roles

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
    /**
     * The roles that belong to the user.
     */
    public function roles()
    {
        return $this->belongsToMany('App\Role');
        // Si el nombre de la tabla es diferente a lo predeterminado o el ID de la tabla tiene otro nombre.
        return $this->belongsToMany('App\Role', 'user_roles', 'user_id', 'role_id');
    }
}

Un Rol tiene varios Usuarios

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Role extends Model
{
    /**
     * The users that belong to the role.
     */
    public function users()
    {
        return $this->belongsToMany('App\User');
        // Si el nombre de la tabla es diferente a lo predeterminado o el ID de la tabla tiene otro nombre.
        return $this->belongsToMany('App\User', 'user_roles', 'role_id', 'user_id');
    }
}

Recuperando columnas de tablas intermedias

Definimos en la función withPivot los campos de la tabla intermedia

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany('App\Role', 'user_roles')
                    ->withPivot('create', 'read','update', 'delete');
    }
}

Accedemos al campo de la siguiente manera.

$user = App\User::find(1);
foreach ($user->roles as $role) {
    echo $role->pivot->create;
}

Diferencias en el retorno del resultado

// retorna App\Phone
App\User::first()->phone;
// retorna Illuminate\Database\Eloquent\Relations\BelongsToMany
App\User::first()->roles();
// retorna Illuminate\Database\Eloquent\Collection
App\User::first()->roles;

Usando Alias para las tablas

$users = User::from('users as a')
    ->join('roles as b',function($join){
        $join->on('a.roles_id','=','b.id')->where('b.estado','=',1);
    })    
    ->select("a.*","b.name")
    ->where('a.estado','=',1)
    ->get();

Ejemplo: Buscar un término

$search = 'Palabra a buscar';
$posts = Post::from('posts as a')
    ->where(function ($query) use ($search) {
      $query = $query->orWhere('a.titulo','like',"%$search%");
      $query = $query->orWhere('a.descripcion','like',"%$search%");
      $query = $query->orWhere('a.tags','like',"%$search%");
    });
$posts = $posts->where('a.estado','=',1)
    ->get();

Ejemplo: Buscando varias palabras en varias columnas

$columns = ['titulo','descripcion','tags'];
$term = 'Palabras a buscar';
$words_search = explode(" ",$term);
$posts = Post::from('posts as a')
    ->where(function ($query) use ($columns,$words_search) {
        foreach ($words_search as $word) {
            $query = $query->where(function ($query) use ($columns,$word) {
                foreach ($columns as $column) {
                    $query->orWhere($column,'like',"%$word%");
                }
            });
        }
    });
$posts = $posts->where('a.estado','=',1)
    ->get();

Consultas con prefijo de tabla

En Laravel podemos configurar un prefijo a todas las tablas de nuestra Base de Datos, ese cambio lo realizamos en el archivo config/database.php, en la opción prefix.
Si ponemos prefijo_ las tablas se llamarían prefijo_users

'mysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'database'  => env('DB_DATABASE', 'database'),
    'username'  => env('DB_USERNAME', 'root'),
    'password'  => env('DB_PASSWORD', ''),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => 'prefijo_',
    'strict'    => false,
    'options' => array(),
],

Si realizamos consultas simples no habría ningún problema con este cambio, pero cuando realizamos consultas complejas usando la función DB::raw es donde tenemos que obtener el prefijo y agregarlo a nuestra consulta.
Con esta función obtenemos el prefijo.

$db_prefix = DB::getTablePrefix();

Ahora dentro de la función DB::raw ingresamos el prefijo.

$users = App\User::join("roles","users.roles_id","=","roles.id")
  ->leftJoin(DB::raw("(SELECT * FROM {$db_prefix}posts where {$db_prefix}posts.estado=1) as posts"),
    function($join){
      $join->on('users.id','=','posts.users_id');
    }
  )
  ->select(DB::raw("{$db_prefix}users.*"))
  ->addSelect(DB::raw("DATE_FORMAT({$db_prefix}posts.created_at,'%d/%m/%Y %h:%i %p') AS posts_creado"))
  ->where('users.estado','=',1)
  ->where('posts.comments_count','>',0)
  ->whereRaw(DB::raw("{$db_prefix}posts.tags !=''"))
  ->get();

También tenemos que agregar el prefijo de las tablas cuando usamos consultas SQL puras.

$sql = "SELECT * FROM {$db_prefix}users WHERE {$db_prefix}users.estado = ? AND {$db_prefix}users.edad < ?";
DB::select($sql,array(1,20));

Consultas con SubQuery

$sub = Post::select(['id','titulo'])->where('estado','=',1);
$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery()) 
    ->count();