通过
搜索
搜索nantaburi / mongodb-sql-model
Mongodb使用SQL风格
1.0.30
2021-08-21 13:50 UTC
Requires
- php: >=7.2.0
- mongodb/mongodb: ^1.6
This package is auto-updated.
Last update: 2024-09-26 13:23:03 UTC
README
安装
composer require nantaburi/mongodb-sql-model
Mongodb使用SQL风格
- 配置：在laravel的config/database.php中添加设置
- 授权：默认数据库为admin，如果没有值[options][authen_db]
'mongodb' => [
'driver' => 'mongodb',
'host' => env('MONGO_DB_HOST', '127.0.0.1'),
'port' => env('MONGO_DB_PORT', 27017),
'database' => env('MONGO_DB_DATABASE', 'marcompany'),
'username' => env('MONGO_DB_USERNAME', 'maradmin'),
'password' => env('MONGO_DB_PASSWORD', 'password'),
'options' => [
'authen_db' => env('DB_AUTHENTICATION_DATABASE', 'admin'),
],
],
独立运行Nantabury/Mongodb，无需laravel
- 如果您没有安装laravel，则仅基于composer包创建文件
.env在项目目录下，然后将以下内容全部复制到.env文件中
# Nantaburi .env file
MONGO_DB_HOST='127.0.0.1'
MONGO_DB_PORT='27017'
MONGO_DB_DATABASE='products'
MONGO_DB_USERNAME='root'
MONGO_DB_PASSWORD='password'
- 使用Laravel的SQL查询风格示例：
- 创建模型 - 在laravel根项目中使用命令
php artisan make:model UserDbModel，并在顶部插入use Nantaburi\Mongodb\MongoNativeDriver\Model- 以前用于
protected $fillable = ["useid","username","lastname","password"]现在将替换为$ schema，如下所示 - 示例：
protected $schema ["userscollection", ["useid","username","lastname","password"]] - 以两种方式创建索引
- 第一种方式：如果您想添加唯一选项，请添加具有键[ Index => true ]的数组。只需添加更多键[ 'Index' => true , 'Unique' => true ]即可
- 第二种方式：使用与集合同一级别的创建键
"$__MULTIPLE_INDEX_01"进行多个键。更改末尾的_01为其他值，当您有多个多个键时，您可以使用_02甚至_AB支持
- 通过添加
'AutoInc' => true ,创建计数器集合以自动增加数字，并且还有选项'AutoInc' => true , 'AutoIncStartwith' => 10,默认数据类型为double，最大数字可达到2^1023 - 索引和计数器的自动创建将有效，一旦您运行第一次插入，或者您可以运行[NameModel]::InitIndexAutoInc()来执行
- 示例：在Laravel控制器中运行一次或更改模式
UserModel::InitIndexAutoInc()
- 示例：在Laravel控制器中运行一次或更改模式
- 以前用于
- 要保护更新选项，请添加
'UpdateProtected'=>true一旦插入，并且您想保护更新，如价格、身份证号码，您可以将选项示例idcard => [ 'UpdateProtected'=>true ]放入该字段的选项中
- 创建模型 - 在laravel根项目中使用命令
<?php
namespace App;
use Nantaburi\Mongodb\MongoNativeDriver\Model as NanModel ;
class UserModel extends NanModel
{
/*
* @override $collection to all stack extends back to -> Class Model -> Class Connection( Using)
*
*/
protected $collection = "users" ;
protected $database = "customer" ;
/*
* @override
* $fillable migrated to under $schema
*
*/
protected $collection = "users" ; // prepare for default collection you can use mode of Model::collection("change new collection later")
protected $database = "companydata" ;
protected $timezone = "Asia/Bangkok" ; // default UTC offset + 0:00 list support timezone https://php.ac.cn/manual/en/timezones.php
//Schema Datatype Double , Decimal128 , Integer32 , String , Date , Auto
// DataType Date support "now"
/*
* protected $fillable = [ "username","email","first_name","last_name","password",
* "plan","services","server-reference","client-address",
* "server-req-time"
* ];
*/
protected $schema = [ 'users' => [ "userid" => [ 'AutoInc' => true ] , "username","email","first_name",
"last_name","password",
"plan","services","server-reference",
"client-address","server-req-time"],
'services' => ['sid'=>[
'AutoInc' => true ,
'AutoIncStartwith' => 10,
'Index' => true,
'Unique' => true ,
'DataType => 'Double'
],
'productid'=>[
'AutoInc' => true ,
'AutoIncStartwith' => 1000001,
'Index' => true,
'Unique' => false,
'DataType => 'Double'
],
'service_name',
'price' =>[
'UpdateProtected'=>true
],
'description' => [ 'DataType => 'String'] ,
'$__MULTIPLE_INDEX_01'=>[
'name' => 'indexSidPid',
'key' => [ 'sid' => 1 , 'productid' => 1 ],
'unique' => true
],
'$__MULTIPLE_INDEX_02'=>[
'name' => 'indexSnameDesc',
'key' => [ 'service_name' => 1 , 'description' => 1 ],
'unique' => true
]
]
];
}
- 示例：在插入后获取创建的魔法索引和每个集合的计数器
- 运行mongo shell中的命令
db.services.getIndexes()以运行魔法创建计数器集合
- 运行mongo shell中的命令
$mongo
>db.services.getIndexes() ;
[
{
"v" : 2.0,
"key" : {
"_id" : 1.0
},
"name" : "_id_",
"ns" : "companydata.services"
},
{
"v" : 2.0,
"key" : {
"sid" : 1.0
},
"name" : "$__INDEX_SID_",
"ns" : "companydata.services"
},
{
"v" : 2.0,
"key" : {
"productid" : 1.0
},
"name" : "$__INDEX_PRODUCTID_",
"ns" : "companydata.services"
},
{
"v" : 2.0,
"unique" : true,
"key" : {
"sid" : 1.0,
"productid" : 1.0
},
"name" : "indexSidPid",
"ns" : "companydata.services"
},
{
"v" : 2.0,
"unique" : true,
"key" : {
"service_name" : 1.0,
"description" : 1.0
},
"name" : "indexSnameDesc",
"ns" : "companydata.services"
}
]>db.companydata_counters.find() ;
{
"_id" : "userid",
"collection" : "users",
"sequence_value" : "0"
}
{
"_id" : "sid",
"collection" : "services",
"sequence_value" : "BLM10"
}
{
"_id" : "productid",
"collection" : "services",
"sequence_value" : "PIDTH1000001"
}
- 头部函数
- 中间函数
- 结束函数
- 比较运算符的位置
- 要比较匹配字段，请使用函数格式 ->where( $field , $Operator , $value)，有关$Operator列表，请参阅下表
- select()函数上的特殊命令
- 创建Laravel控制器
- 在laravel根项目中使用命令
php artisan make:controller --model=UserDbModel - 然后编辑并插入基本的SQL示例：
select * from user where username like 'suphacha%' and age > 18 or mooban = 'Pangpoi' ; - 使用SQL转换到mongodb显示如下
- 在laravel根项目中使用命令
use App\UserDbModel ;
$users= UserDbModel::query()
->where("username" , "like" , "suphacha%" )
->andwhere("age" ,">", 18)
->orwhere("mooban" ,"=" ,"Pangpoi" )
->get() ;
return view('userlist')->with("users",$users) ;
更改数据库和集合：如果您有用户和密码的多个数据库的权限，您可以在文件./config/database.php中更改不同的数据库
- 示例：更改数据库DataBaseModel::database("shopping") .... ，更改数据库和集合DataBaseModel::database("shopping","products") ...
$prods = CompanyDB::database("shopping")
->select("id as prod_id","name as prod_name")
->get()
$users = CompanyDB::database("authentication","users")->select("id"," name as prod_name")
->get()
- 切换集合不需要重新创建其他模型文件
- 请将开始于 ->collection('[Collection Name]') 放在下面示例中
<?php
namespace App\Http\Controllers;
use App\CompanyModel;
$users = CompanyModel::collection("users")
->where( "username" ,"=" , "suppachai")
->get();
$products = CompanyModel::collection("products")
->where( "pid" ,"=" , "101")
->get();
// Laravel's blade view to displays
return view("usermanage" )->with('users',$users)
->with('products',$products);
}
- 切换集合不需要重新创建其他模型文件
- 请将开始于 ->collection('[Collection Name]') 放在下面示例中
- 以及许多获取数据的方法
<?php
namespace App\Http\Controllers;
use App\CompanyModel;
$users = CompanyModel::collection("users")
->where( "username" ,"=" , "shppachai")
->get();
$products = CompanyModel::all() ;
$login = CompanyModel::collection("products")->where( "id" ,"=" , 101)->first();
// Laravel's blade view to dispale
return view("usermanage" )->with('users',$users)
->with('products',$products);
}
- 控制器
- 以下为连接集合的代码示例
- 一旦使用 groupby() 请求，则需要 select() 所有在 groupby() 中具有相同字段的字段。如果所选字段不是 groupby() 中字段的成员，则所选字段将显示为空数据。
- 如果您想使用
->groupby( )的快捷方式，使其与->select("field1 as fl1","field2")中的所有字段相同，您可以通过在->groupby('$selected')中添加一个运算符'$selected'来创建快捷方式，同时自动删除as运算符。
$users = CompanyModel::collection("users")
->select( "users.username as uname","services.sid as service_id" )
->leftjoin("services","users.services_id","services.sid")
->where( "users.username" ,"=" , "supachai")
->get() ;
$users = CompanyModel::collection('products')
->select('products.id as prod_id ','products.name','products_type.description_th','products_group.description')
->leftjoin('products_type','products.type_id','products_type.type_id')
->leftjoin('products_group','products.type_groupid','products_group.type_groupid')
->where("products.name",'like',"%phone%")
->orwhere("products.id",'>',400)
->andwhere("products.description",'like','%the%')
->groupby('products.id','products.name','products_type.description_th','products_group.description')
->orderby('products.name','asc')
->limit(10,2)
->get();
// Example do reduce shortcut operator ->groupby()
$users = CompanyModel::collection('products')
->select('products.id as prod_id ','products.name','products_type.description_th','products_group.description')
->leftjoin('products_type','products.type_id','products_type.type_id')
->leftjoin('products_group','products.type_groupid','products_group.type_groupid')
->where("products.name",'like',"%phone%")
->orwhere("products.id",'>',400)
->andwhere("products.description",'like','%the%')
->groupby('$selected') //@@ do shortcut same as ->select()
->orderby('products.name','asc')
->limit(10,2)
->get();
- Getgroup
- 支持侧边栏或下拉菜单的功能
- 请求连接两个表
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Shopping ;
class Tester extends Controller
{
public function index() {
$test = Shopping::collection('products_group')->select('products_group.cat_id as pgid','products_group.description as gdesc','productstype.type_id as tid','products_type.description as type_desc_en')
->where('products_type.type_id','!=',null)
->leftjoin('products_type','products_group.cat_id','products_type.type_groupid')
->orderby('products_group.description','ASC')
->groupby('$selected')
->getgroup() ;
return view('test')->with('test',$test) ;
}
}
在 Laravel blade 视图文件 test.blode.php 中执行 getgroup()，如下所示
<!DOCTYPE html>
<html">
<body>
<h2>==test list by group==</h2>
@foreach( $test as $key => $value )
{{$key}} =>
group id {{ $value['gid']}} :
{{ $value['pdesc'] }} <br>
@foreach($value['products_type'] as $value )
--------- >>>> type ID {{ $value['tid']}} :
type descripton {{ $value['typeDesc_en']}}
<br>
@endforeach
@endforeach
</body>
</html>
// Display outcome on browser example :
==test list by group==
0 => group id 17 : Travel and Sport
--------- >>>> type ID 42 : type descripton Sport
1 => group id 8 : Transportation
--------- >>>> type ID 3 : type descripton Forklifts
--------- >>>> type ID 21 : type descripton trailer
2 => group id 5 : Home
--------- >>>> type ID 26 : type descripton Kitchen ware
--------- >>>> type ID 152 : type descripton Cleaning
3 => group id 10 : Gadgets
--------- >>>> type ID 34 : type descripton Computer
--------- >>>> type ID 22 : type descripton Electronics
--------- >>>> type ID 41 : type descripton Network Device
--------- >>>> type ID 36 : type descripton In-Ear
--------- >>>> type ID 35 : type descripton Speaker
--------- >>>> type ID 31 : type descripton Smartphone
4 => group id 2 : Fashion
--------- >>>> type ID 2 : type descripton shoes
--------- >>>> type ID 137 : type descripton Lather
--------- >>>> type ID 4 : type descripton Man
--------- >>>> type ID 1 : type descripton shirt
5 => group id 13 : Factory
--------- >>>> type ID 39 : type descripton PHARMACEUTICAL & COSMETIC MACHINERY
6 => group id 1 : Electric
--------- >>>> type ID 20 : type descripton Electric Tools
--------- >>>> type ID 38 : type descripton Liquid Cooling
--------- >>>> type ID 25 : type descripton Cooling Tower
--------- >>>> type ID 37 : type descripton Air-condition
7 => group id 20 : Accessory
--------- >>>> type ID 133 : type descripton 3D Printer & Scanner
- 分页
- 分页显示数据，并按每页数据量进行拆分
- 如果已将 limit() 命令添加到排序中，则 paginate 将忽略 function limit()
- 数据输出将位于属性 ->items 中，同时如下所示
- 页码链接数据为 ->link，并通过使用方法 ->link() 绘制 HTML 代码
- 例如，切换到第 2 页：[http://127.0.0.1/?page=2](http://127.0.0.1/?page=2)
$users = CompanyModel::collection("users")
->select( "users.username as u_name" , "users.password as pwd" , "address.city_name as live " )
->leftjoin("services","users.city_id","address.city_id")
->where( "users.username" ,"!=" , "supachai")
->groupby("users.username" , "users.password" ,"address.city_id" )
->orderby("users.username")
->limit(4,4) // @@ if do pagination will don't care the limit function
// don't you need to add limit() in process line the module will ignore
->paginate(10);
// Laravel view example //
return view("usermanage" )->with('users',$users)
// example get values in blade file resource/views/usermanage.blade.php
<?php
// ceate example file usermanage.blade.php
<div> total users : {{$users->total}} </div>
<div>
@foreach($users->items as $key => $values)
<a href="?page={{$values['page']}}" > {{ $values['icon']}} </a>
@endforeach
</div>
?>
- 通过 Controller 插入
- 以下为插入准备代码示例
- $fillable 已被移除，用 $schema 替换，fillable 将在 $schema 之后运行
- 如果收集到的集合和字段数据不在 schema 成员中，则插入将被拒绝并出现错误
- 以下示例展示了如何使用内置函数
getModifySequence()自动增加数字：您必须在模型文件中创建 schema 并设置'AutoInc' => true , 'AutoIncStartwith' => 101,。
$prepairinsertServices["username"] = $request->input('username') ;
$prepairinsertServices["email"] = $request->input('email') ;
$prepairinsertServices["first_name"] = $request->input('first_name') ;
$prepairinsertServices["last_name"] = $request->input('last_name') ;
$prepairinsertServices["password"] = $request->input('psswd') ;
$prepairinsertServices["plan"] = $request->input('radioplan') ;
$prepairinsertServices["services"] = [ ] ;
// Get data from Check box
if ( null != $request->input('service-ecom') )
array_push ( $prepairinsertServices["services"] ,[ "service-ecom" , $request->input('service-ecom') ]) ;
if ( null != $request->input('service-chat') )
array_push ( $prepairinsertServices['services'], ["service-chat", $request->input('service-chat')]);
if ( null != $request->input('service-email') )
array_push ( $prepairinsertServices['services'],["service-email" , $request->input('service-emai)') ]);
$prepairinsertServices["server-reference"] = $_SERVER['HTTP_REFERER'] ;
$prepairinsertServices["client-address"] = $_SERVER['REMOTE_ADDR'] ;
$prepairinsertServices["server-req-time"] = $_SERVER['REQUEST_TIME'] ;
$resultInsert = UserModel::insert( $prepairinsertServices ) ; // using default $collection in model
$resultInsertOtherone = UserModel::database()->collection("services")
->insert(['sid'=> UserModel::database()->collection("services")->getModifySequence('sid') ,
'service_name'=>'Gold' ,
'description'=>'VIP sevice top level'
]) ;
// Handle insert error !
if ( $resultInsert[0] == 0 ) {
return redirect()->back() ->with('alert', $resultInsert[1] );
}else { sleep(1) ; }
$users = UserModel::all() ;
return view('usermanage',compact('users') ) ;
}
- 更新
- 为了防止更新 schema 中的字段，请使用
idcard => [ 'UpdateProtected'=>true ] - 更新可以有多种样式，以下为示例
- 为了防止更新 schema 中的字段，请使用
$updateResult = ShoppingModel::collection("products")
->where('id',"=",454) // update mltiple style
->andupdate( [ "name" => "PHONE UPDATE 11.0 v3" , 'price' => 25200 ]) // Array style
->andupdate("description","=","Iphone version Thai v3") // Equaly style
->update("description_th","New iPhone 11.0 v3th ") ; // Two values style
$updateResult = ShoppingModel::collection("products")
->where('id',"=",454)
->update( [ "description_th" => "New iPhone 11.0 v3th " ]) ; // Array style
$updateResult = ShoppingModel::collection("products")
->where('id',"=",454)
->update( "description_th" , "New iPhone 11.0 v3th " ) ; // Two values style
- 删除文档
- 请求以 where 语句开始
$deleteresult = ShoppingModel::collection("products")
->where("id" ,"=",444)
->andwhere("id" ,"=",442)
->delete("id",">",440);
$deleteresult = ShoppingModel::collection("products")
->delete("id","=",440);
- 在视图中处理插入错误
- 将以下脚本添加到您的 view.file.blade.php 文件中
<script>
var msg = '{{Session::get('alert')}}';
var exist = '{{Session::has('alert')}}';
if(exist){
alert(msg);
}
</script>