SQLELOQUENT
SELECT column1,column2 FROM table;$data = Table::select(‘column1’, ‘column2’)->get();
or
$data = Table::pluck(‘column1’, ‘column2’);
SELECT * FROM table;$data = Table::all();
or
$data = Table::get();
SELECT column1, column2 FROM table
WHERE column1 = “yes”;		$data = Table::select(‘column1’, ‘column2’)->where(‘column1’, ‘yes’)->get();
or
$data = Table::where(‘column1’, ‘yes’)->pluck(‘column1’, ‘column2’);
or
$data = Table::where(‘column1’, ‘yes’)->where(‘column2′,’>’,10)->get();
SELECT DISTINCT column1 FROM table
WHERE column1 = “yes”;		$data = Table::select(‘column1’)->distinct()->where(‘column1’, ‘yes’)->get();
or
$data = Table::where(‘column1’, ‘yes’)->distinct()->pluck(‘column1’);
or
$data = Table::where(‘column1’, ‘yes’)->where(‘column2′,’>’,10)->distinct()->pluck(‘column1’);
SELECT column1, column2 FROM table
ORDER BY column1 ASC;		$data = Table::select(‘column1’, ‘column2’)->orderBy(‘column1’, ‘asc’)->get();
or
$data = Table::orderBy(‘column1’, ‘asc’)->get([‘column1’, ‘column2’]);
or
$data = Table::where(‘column1’, ‘yes’)->orderBy(‘column1’, ‘asc’)->get([‘column1’, ‘column2’]);
or
$data = Table::select(‘column1’, ‘column2’)->orderBy([‘column1’ => ‘asc’, ‘column2’ => ‘desc’])->get();
SELECT column1, column2 FROM table
ORDER BY column1
LIMIT 10 OFFSET;		$data = Table::select(‘column1’, ‘column2’)->orderBy(‘column1’)->take(10)->skip(0)->get();

or

$data = Table::orderBy(‘column1’)->limit(10)->offset(0)->get([‘column1’, ‘column2’]);

or

$data = Table::where(‘column1’, ‘yes’)->orderBy(‘column1’)->take(10)->skip(0)->get([‘column1’, ‘column2’]);

SELECT column1, aggregate(column2)
FROM table
GROUP BY column1;		$results = DB::table(‘table’)
->select(‘column1’, DB::raw(‘aggregate(column2)’))
->groupBy(‘column1’)
->get();

Powerful New

or

$results = TableModel::select(‘column1’, DB::raw(‘aggregate(column2)’))
->groupBy(‘column1’)
->get();

SELECT table1.id, table2.name, table1.date
FROM table1
INNER JOIN table2 ON table1.userId=table2.userId;		$results = Table1::select(‘table1.id’, ‘table2.name’, ‘table1.date’)
->join(‘table2’, ‘table1.userId’, ‘=’, ‘table2.userId’)
->get();

or

$results = Table1::with(‘table2:userId,name’)
->select(‘table1.id’, ‘table2.name’, ‘table1.date’)
->get();

SELECT table1.id, table2.name, table1.date
FROM table1
LEFT JOIN table2 ON table1.userId=table2.userId;		$results = Table1::select(‘table1.id’, ‘table2.name’, ‘table1.date’)
->leftJoin(‘table2’, ‘table1.userId’, ‘=’, ‘table2.userId’)
->get();

or

$results = Table1::with(‘table2:userId,name’)
->select(‘table1.id’, ‘table2.name’, ‘table1.date’)
->get();

SELECT table1.id, table2.name, table1.date
FROM table1
RIGHT JOIN table2 ON table1.userId=table2.userId;		$results = Table2::select(‘table1.id’, ‘table2.name’, ‘table1.date’)
->rightJoin(‘table1’, ‘table1.userId’, ‘=’, ‘table2.userId’)
->get();

or

$results = Table2::with(‘table1:userId,name’)
->select(‘table1.id’, ‘table2.name’, ‘table1.date’)
->get();

ELECT column1, column2
FROM table1
CROSS JOIN table2;		$results = DB::table(‘table1’)
->crossJoin(‘table2’)
->select(‘table1.column1’, ‘table2.column2’)
->get();

or

$results = DB::table(‘table1’)
->join(‘table2’, ‘cross’)
->select(‘table1.column1’, ‘table2.column2’)
->get();

INSERT INTO table1 (field1, field2) VALUES (value1, value2);$table1 = new Table1;
$table1->field1 = $value1;
$table1->field2 = $value2;
$table1->save();

or

Table1::create([‘field1’ => $value1, ‘field2’ => $value2]);

or

$records = [
[‘field1’ => $value1, ‘field2’ => $value2],
[‘field1’ => $value3, ‘field2’ => $value4],

];
DB::table(‘table1’)->insert($records);

DELETE FROM table1 WHERE conditionDB::table(‘table1’)->where(‘condition’, $value)->delete();

or

Table1::destroy($ids);

or

Table1::where(‘condition’, $value)->delete();

DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 =
table2.id2 AND condition		DB::table(‘table1’)
->join(‘table2’, ‘table1.id1’, ‘=’, ‘table2.id2’)
->where(‘condition’, $value)
->delete();

or

Table1::join(‘table2’, ‘table1.id1’, ‘=’, ‘table2.id2’)
->where(‘condition’, $value)
->delete();

UPDATE table1 SET field1=new_value1 WHERE condition;DB::table(‘table1’)
->where(‘condition’, $value)
->update([‘field1’ => $new_value1]);

or

Table1::where(‘condition’, $value)
->update([‘field1’ => $new_value1]);

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;$query = Customer::select(DB::raw(‘COUNT(CustomerID) as count’), ‘Country’)
->groupBy(‘Country’)
->get();

or

$query = Customer::selectRaw(‘COUNT(CustomerID) as count, Country’)
->groupBy(‘Country’)
->get();

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;		$query = Order::select(‘Orders.OrderID’, ‘Customers.CustomerName’, ‘Orders.OrderDate’)
->join(‘Customers’, ‘Orders.CustomerID’, ‘=’, ‘Customers.CustomerID’)
->get();

or

$query = DB::table(‘Orders’)
->select(‘Orders.OrderID’, ‘Customers.CustomerName’, ‘Orders.OrderDate’)
->join(‘Customers’, ‘Orders.CustomerID’, ‘=’, ‘Customers.CustomerID’)
->get();

CREATE TABLE addresses (
user_id int, — Both a primary and foreign key
street varchar(30) NOT NULL,
city varchar(30) NOT NULL,
state varchar(30) NOT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY (user_id)
REFERENCES users (id)
ON DELETE CASCADE
);		php artisan make:migration create_addresses_table

and

Schema::create(‘addresses’, function (Blueprint $table) {
$table->integer(‘user_id’);
$table->string(‘street’, 30)->notNull();
$table->string(‘city’, 30)->notNull();
$table->string(‘state’, 30)->notNull();
$table->primary(‘user_id’);
$table->foreign(‘user_id’)->references(‘id’)->on(‘users’)->onDelete(‘cascade’);
});

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Disclaimer:

As an Amazon Associate I earn from qualifying purchases. This post may contain affiliate links which means I may receive a commission for purchases made through links.