Laravel Eloquent ORM y Query Builder

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

Laravel Utilizando Eloquent ORM

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

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

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

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

Para crear un registro.

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

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();

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

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);

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();

Actualizado .

(Visited 7.959 times, 48 visits today)
Puedes saltar al final y dejar una respuesta. Hacer ping no está permitido actualmente.