mongodb-sql-style

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 .env under 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 UserDbModel at laravel root project and insert use Nantaburi\Mongodb\MongoNativeDriver\Model on 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 _01 to be other once you have more one multiple keys you can use _02 even _AB support 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()
- to protect update option add 'UpdateProtected'=>true once you inserted and do you want to protect update such as price , ID Card number , you can put option example idcard => [ 'UpdateProtected'=>true ] into option of that field

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

$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	`[DBModel]::collection(string $collectionName)`	`[DBModel]::collection('Products')->`

Middle functions

Functional	Middle function	Example
display documents	`->select( $field1 , $field3 ....)->`	`->select('*')->` `->select('userid' , 'username' , 'password')->` `->select('userid as uid' , 'username as uname' , 'password as pass')->` `->select('users.userid as uid' , 'users.username as uname' , 'users.password as pass'->)->`
where	`->where( String $field ,String $Operator, $values )->`	`->where("userid" ,"=", 100 )->`
where in	`->wherein(string $filed , array $values )->` `->where(string $filed ,"in",array $values )->`	`->wherein("userid" , [1,4,5,6,10])->` `->where("userid" ,"in" ,[1,4,5,6,10])->`
where or where	`->orwhere(String $field ,String $Operator, $values )->`	`->where("userid",'=',1)` `->orwhere("userid",'>=',10)->`
where and where	`->andwhere(String $field ,String $Operator, $values )->`	`->where("userid",'=',1)` `->andwhere("username",'=','supachai')->`
join collection	`->join(String $foreignCollection ,String $localCollection["."]localKey,String $foreign["."]foreignkey)->`	`->join("products_group","products.typeid","product_group.typeid" )->`
leftjoin collection	`->leftjoin(String $foreignCollection ,String $localCollection["."]localKey,String $foreign["."]foreignkey)->`	`->leftjoin("products_group","products.typeid","product_group.typeid" )->`
Group by	`->groupby(String $field1 ,String $field1...)->` #group same with ->select() function shortcut with '$selected' `->groupby( string '$selected')->`	`->groupby("userid","username")->` `->groupby('$selected')->`
sort order by	`->orderby(String $field1 ,String $field1...)->`	`->orderby("userid","username")->`
Limit display output	`->limit(int $limitPerpage [,Int $offset])->`	`->limit(10)->` `->limit(10,40)->`

End functions

Functional	End function	Example
get all documents static functon::	`DBModel::all( null )`	`DBModel::all()`
get documents	`->get(null)`	`->get()`
getgroup documents to group request join two collections	`->getgroup(null)`	`-->getgroup( ) ;`
get one first document	`->first(null)`	->first()
random documents `*request function groupby()`	`->random(int $numberDoc)`	`->select('id','name')->where("sale" , "=" , 1 )->groupby('$seleted')->random(2)`
Pagination	`->paginate(int $perpage)`	->paginate(10)
Update document # request where match	`where( string $key ,string $operator ,v $value )->update(array $DictArray = null )`	`->where("userid" , "=" , 1 ,)->update(["password" => "AAA321" ])` `->where('id',"=",454)` `->andupdate( [ "name" => "PHONE UPDATE 11.0 v3" , 'price' => 25200 ])` `->andupdate("description","=","Iphone version Thai v3")` `->update("description_th","New iPhone 11.0 v3th ") ;`
Delete document	`->delete(string $key ,string $operator ,string $value )`	`->delete(["userid", "=", 1 )` `->where("id" ,"=",444)` `->andwhere("id" ,"=",442)` `->delete("id",">",440);`
Insert document	`->insert(array $DictArray)`	`->insert(["userid" => 1 , "username" => "Supachai" , "password" => "321PASS" ])`
Insert document and get ID	`->insertGetId(array $DictArray [,string $fieldGet] )`	`$response = CompanyUser::collection("users")->insertGetId(["userid" => 1 , "username" => "Supachai" , "password" => "321PASS" ])` `->insertGetId(["userid" => 1 , "username" => "Supachai" , "password" => "321PASS" ] , 'username' )` `->insertGetId(["userid" => 1 , "username" => "Keattisak" , "password" => "PhomPoung" ] , 'userid' )`

Where comparison operation
- to comparison macthing field to use function format ->where( $field , $Operator , $value) for $Operator list see below table

Operator	description	Example
"="	`operator matches documents where the value of a field equals the specified value`	`->where("userid","=",100)`
">"	`selects those documents where the value of the field is greater than (i.e. >) the specified value`	`->where("userid",">",5)`
">="	`selects the documents where the value of the field is greater than or equal to (i.e. >=) a specified value (e.g. value.)`	`->where("userid",">=",5)`
"in"	`The "in" operator selects the documents where the value of a field equals any value in the specified array. To specify an "in" expression`	`->where("userid","in",[5,11,10])`
"nin"	`The "nin" operator selects the documents where the value of a field equals any value not in the specified array. To specify an "nin" expression`	`->where("userid","nin",[5,11,10])`
"<"	`< selects the documents where the value of the field is less than (i.e. <) the specified value.`	`->where("userid","<",5`
"<="	`<= selects the documents where the value of the field is less than or equal to (i.e. <=) the specified value.`	`->where("userid","<=",10)`
"!="	`!= selects the documents where the value of the field is not equal to the specified value. This includes documents that do not contain the field.`	`->where("username","!=","Supachai")`
"like"	`!= selects the documents where the value of the field is like machting values.`	`->where("username","like","Supa%")` `->where("username","like","%chai")` `->where("username","like","%upa%")`

spacial command on function select()

Operator	description	Example
count(*)	`count all documents to be number`	`->select('count(*)')->get()`

- spacial functions

class function()	description	Example
[ClassName]::getSequence([Auto Increase field],[Collection])	`to auto increase number`	`ModelDB::collection("customers")->insert(["id" => ModelDB::getSequence('id','customers') , "description" => "Elon mask"]) ;`

Create Laravel controller
- using command php artisan make:controller --model=UserDbModel at 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 :

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 remove as operator

      $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

$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>