nantaburi/mongodb-sql-model
Composer 安装命令:
composer require nantaburi/mongodb-sql-model
包简介
Mongodb using SQL style
README 文档
README
Installing
composer require nantaburi/mongodb-sql-model
Mongodb using SQL style
- Configuraton add setting in config/database.php of laravel
- Authorization Database default as admin if no value [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'),
],
],
Run Nantabury/Mongodb standalone without laravel :
- if you don't have laravel wasn't installed you have only base on composer package go to create file
.envunder project's directory and then copy all below to .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'
- Using Laravel for SQL query style example below
- Create Model - using command
php artisan make:model UserDbModelat laravel root project and insertuse Nantaburi\Mongodb\MongoNativeDriver\Modelon top- ex-fillable used to be
protected $fillable = ["useid","username","lastname","password"]will replace with $schema as example below - Example :
protected $schema [ "userscollection" , ["useid","username","lastname","password"] ] - Magic create index with two ways
- first way : add array with key [ Index => true ] if you want to add option Unique just add more key [ 'Index' => true , 'Unique' => true ]
- secound way : do multiple keys with create key
"$__MULTIPLE_INDEX_01"on same level of collection change at the end_01to be other once you have more one multiple keys you can use_02even_ABsupport as well
- Magic create collection for counters auto increasement number by add
'AutoInc' => true ,and also have option'AutoInc' => true , 'AutoIncStartwith' => 10,default datatype as double max number can be 2^1023 - Magic creation of Index and Magic counter will auto create affective once you run first insert or you can do [NameModel]::InitIndexAutoInc()
- Example : run at Laravel controller just first time or do change schema
UserModel::InitIndexAutoInc()
- Example : run at Laravel controller just first time or do change schema
- ex-fillable used to be
- to protect update option add
'UpdateProtected'=>trueonce you inserted and do you want to protect update such as price , ID Card number , you can put option exampleidcard => [ 'UpdateProtected'=>true ]into option of that field
- Create Model - using command
<?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://www.php.net/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
]
]
];
}
- Example get created magic index and the counters of each collection after run insert
- Magic create counter collection run command with mongo shell
db.services.getIndexes()
- Magic create counter collection run command with 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"
}
- Head functions
| Functional | Head function | Example |
|---|---|---|
| set database |
|
|
- Middle functions
| Functional | Middle function | Example |
|---|---|---|
| display documents |
|
|
| where |
|
|
| where in |
|
|
| where or where |
|
|
| where and where |
|
|
| join collection |
|
|
| leftjoin collection |
|
|
| Group by |
|
|
| sort order by |
|
|
| Limit display output |
|
|
- End functions
| Functional | End function | Example |
|---|---|---|
| get all documents static functon:: |
|
|
| get documents |
|
|
| getgroup documents to group request join two collections |
|
|
| get one first document |
|
->first() |
random documents*request function groupby() |
|
|
| Pagination |
|
->paginate(10) |
| Update document # request where match |
|
|
| Delete document |
|
|
| Insert document |
|
|
| Insert document and get ID |
|
|
- Where comparison operation
- to comparison macthing field to use function format ->where( $field , $Operator , $value) for $Operator list see below table
| Operator | description | Example |
|---|---|---|
| "=" |
|
|
| ">" |
|
|
| ">=" |
|
|
| "in" |
|
|
| "nin" |
|
|
| "<" |
|
|
| "<=" |
|
|
| "!=" |
|
|
| "like" |
|
|
- spacial command on function select()
| Operator | description | Example |
|---|---|---|
| count(*) |
|
|
| class function() | description | Example |
|---|---|---|
| [ClassName]::getSequence([Auto Increase field],[Collection]) |
|
|
- Create Laravel controller
- using command
php artisan make:controller --model=UserDbModelat laravel root project - then edit and insert basic SQL example :
select * from user where username like 'suphacha%' and age > 18 or mooban = 'Pangpoi' ; - using SQL transform to mongodb showing below :
- using command
use App\UserDbModel ;
$users= UserDbModel::query()
->where("username" , "like" , "suphacha%" )
->andwhere("age" ,">", 18)
->orwhere("mooban" ,"=" ,"Pangpoi" )
->get() ;
return view('userlist')->with("users",$users) ;
Changing database and collection if you have permission of user and password on the same multiple databases you can change database difference in file ./config/database.php
- examples change just database DataBaseModel::database("shopping") .... , change database and collection 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()
- switch collection don't need to re-create new other Model file
- put begin with ->collection('[Collection Name]') see example below
<?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);
}
- switch collection don't need to re-create new other Model file
- put begin with ->collection('[Collection Name]') see example below
- and many way to get data
<?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);
}
- Controller
- join collectios code example below
- once you use groupby() request select() all of fields same fields in groupby() if seleted fields are not be field's member in groupby() output will display with empty data on that selected field
- if you want to use shortcut of
->groupby( )to be same as all fields in->select("field1 as fl1","field2")you can do shortcut by add one operator'$selected'into->groupby('$selected')also auto removeasoperator
$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
- Function to support side menu or dropdown menu
- request join two table
<?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) ;
}
}
On Laravel blade views file test.blode.php do getgroup() same as below :
<!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
- Paginate
- pagination are displays data and page breakedown with data perge
- paginate going to don't care function limit() if you have added into order command
- Output of data will is in property ->items it also show below
- Output of pages number linkdata as ->link and drawing of html code by use method ->link()
- switch to page 2 using as example : 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>
?>
- insert via Controller
- insert prepare code example below
- $fillable had removed replace with $schema and fillable will run behind $schema
- once collectaion and field data isn't in schema member insert will reject and has error
- find below example to use function
getModifySequence()for auto increase number was bild-in this function you have to create schema to prepare on Model file then set'AutoInc' => true , 'AutoIncStartwith' => 101,with
$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') ) ;
}
- Update
- to prevent update field put in schema
idcard => [ 'UpdateProtected'=>true ] - update can be many style do you need to see examples below
- to prevent update field put in 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
- Delete documents
- request begin with where statement
$deleteresult = ShoppingModel::collection("products")
->where("id" ,"=",444)
->andwhere("id" ,"=",442)
->delete("id",">",440);
$deleteresult = ShoppingModel::collection("products")
->delete("id","=",440);
- Handle insert error in view
- add script below into your view file.blade.php
<script>
var msg = '{{Session::get('alert')}}';
var exist = '{{Session::has('alert')}}';
if(exist){
alert(msg);
}
</script>
nantaburi/mongodb-sql-model 适用场景与选型建议
nantaburi/mongodb-sql-model 是一款 基于 PHP 开发的 Composer 扩展包,目前已累计 124 次下载、GitHub Stars 达 0, 最近一次更新时间为 2020 年 06 月 30 日, 在 PHP 生态内属于活跃度较高的组件。
它主要适用于以下技术方向: 「database」 「mongodb」 「sql」 「mongo」 「model」 「laravel」 等业务场景。在实际项目中,围绕这些方向常见需要落地的问题包括:接口对接、性能调优、并发安全、与既有框架(Laravel / ThinkPHP / Yii / Webman 等)的兼容适配,以及生产环境的日志埋点与稳定性保障。
我们在过去多个企业项目中使用过 nantaburi/mongodb-sql-model 或与其功能相近的方案,如果你在选型或落地过程中遇到问题,例如 版本兼容、二次改造、私有化封装、与内部系统对接、生产 BUG 排查,欢迎联系我们协助评估。
基于 nantaburi/mongodb-sql-model 在你已有业务上做功能扩展、字段裁剪、UI 适配、与内部账号 / 权限 / 日志系统的深度对接。
线上偶发问题、内存泄漏、慢查询、并发异常等排查修复;针对高流量场景做缓存、队列、索引层面的调优。
承接完整的项目从需求 → 设计 → 开发 → 上线 → 长期运维;也可按月提供技术保姆服务。
与 nantaburi/mongodb-sql-model 相关的其它包
同方向 / 同关键字的高下载量 PHP Composer 包推荐,方便对比选型:
Dibi is Database Abstraction Library for PHP
Generates Symfony2 documents, forms and CRUD
Store your language lines in the database, yaml or other sources
A package for automatically encrypting and decrypting Eloquent attributes in Laravel 5.5+, based on configuration settings.
Query filtering in your frontend
A PSR-7 compatible library for making CRUD API endpoints
统计信息
- 总下载量: 124
- 月度下载量: 0
- 日度下载量: 0
- 收藏数: 0
- 点击次数: 8
- 依赖项目数: 0
- 推荐数: 0
其他信息
- 授权协议: MIT
- 更新时间: 2020-06-30