Laravel动态键值查询改写求助：原生SQL转Laravel查询构建器（Capsule优先）

Hey there! As someone who’s been in your shoes as a Laravel newbie, let’s turn those raw SQL functions into clean, secure Capsule ORM code—perfect for standalone projects or Laravel apps alike.

First: Set Up Capsule (If You Haven’t Already) #

If you’re using Capsule outside a Laravel app, you need to initialize it with your database credentials first. Here’s a quick, reusable setup snippet:

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;

// Add your database connection details
$capsule->addConnection([
    'driver'    => 'mysql', // Swap for pgsql, sqlite, or sqlsrv if needed
    'host'      => 'localhost',
    'database'  => 'your_database_name',
    'username'  => 'your_db_user',
    'password'  => 'your_db_password',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '', // Add a prefix here if your tables use one (e.g., 'app_')
]);

// Make Capsule available globally (so you can use static methods like Capsule::table())
$capsule->setAsGlobal();

// Boot Eloquent if you plan to use model classes later (optional but recommended)
$capsule->bootEloquent();

1. Convert selectTableData (Dynamic Condition Query) #

Let’s assume your original raw SQL function looks like this (a common dynamic select setup):

// Example raw SQL select function
function selectTableData($table, $conditions = []) {
    $sql = "SELECT * FROM $table";
    $params = [];
    
    if (!empty($conditions)) {
        $sql .= " WHERE ";
        $clauses = [];
        foreach ($conditions as $col => $val) {
            $clauses[] = "$col = ?";
            $params[] = $val;
        }
        $sql .= implode(" AND ", $clauses);
    }
    
    // Execute query and return results...
}

Here’s the clean Capsule version—it automatically handles SQL injection protection and is way more readable:

function selectTableData($table, $conditions = []) {
    // Start building the query
    $query = Capsule::table($table);
    
    // Add dynamic conditions (supports all common operators!)
    foreach ($conditions as $column => $value) {
        // Default: equality check (column = value)
        $query->where($column, $value);
        
        // Need a different operator? Swap it out:
        // $query->where($column, '>', $value); // Greater than
        // $query->where($column, 'LIKE', "%$value%"); // Partial text search
        // $query->whereIn($column, $value); // Use if $value is an array for IN clauses
    }
    
    // Return results based on your needs:
    // - get(): Returns a collection of all matching rows (use toArray() for a plain array)
    // - first(): Returns only the first matching row
    // - pluck('column_name'): Returns an array of just that column's values
    return $query->get();
}

2. Convert insertTableData (Dynamic Data Insert) #

Your original raw insert function might look like this:

// Example raw SQL insert function
function insertTableData($table, $data) {
    $columns = implode(", ", array_keys($data));
    $placeholders = implode(", ", array_fill(0, count($data), "?"));
    $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
    
    // Execute query and return insert ID...
}

Capsule makes this trivial—no need to mess with column lists or placeholders manually:

function insertTableData($table, $data) {
    // Insert data and get the last inserted ID (great for auto-incrementing primary keys)
    $insertedId = Capsule::table($table)->insertGetId($data);
    
    // If you don't need the ID, just use insert() instead:
    // Capsule::table($table)->insert($data);
    
    // For bulk inserts (multiple rows at once), pass an array of arrays:
    // Capsule::table($table)->insert([
    //     ['name' => 'Alice', 'email' => 'alice@example.com'],
    //     ['name' => 'Bob', 'email' => 'bob@example.com']
    // ]);
    
    return $insertedId;
}

Quick Tips for New Laravel Users #

• Security First: Capsule automatically binds parameters, so you don’t have to worry about SQL injection—way safer than manually building raw queries.
• Laravel App Users: If you’re working inside a Laravel project, replace Capsule::table() with DB::table()—the syntax is identical!
• Eloquent Models: Once you’re comfortable with Capsule, consider creating Eloquent models (e.g., User for the users table). Then you can do things like User::where('email', 'john@example.com')->first() or User::create($data) for even cleaner, more maintainable code.

内容的提问来源于stack exchange，提问作者Zub