Saturday, February 11, 2017

Laravel adnance query builder using and or conditions

It is very important to make query dynamically based on what parameters we received. Best way to do this using Laravel is use query as a function. Background color yellow show how it works.


DB::table('users')
        ->where('name', '=', 'John')
        ->where(function($query) use($params) {
            $query->where('votes', '>', 100);
            $query->orWhere('title', '<>', 'Admin');
        })->get();

The query above will produce the following SQL:

select * from users where name = 'John' 
and (votes > 100 or title <> 'Admin') 

Or you can do below:

$where = [
    ['name', '=', "Some name"],
    ["OR" => function($q) {
        $q->where("type", "=", "EMPLOYEE");
        $q->orWhere("type", "=", "SUPERVISOR");
    }]
];


$list = DB::table('employees')->where($where)->get();

Laravel where in example query | Laravel query builder | Query Builder Component

In laravel it is need to do create criteria to search in database where in some id list or others. Below shows how can we use where in Laravel. 

DB::table('table')->whereIn('id', array(1, 2, 3))->get();

All query builder options available in below link:

Laravel Query Builder

Raw Queries in Laravel | Get Sql Query And Bindings From Laravel Eloquent Query Builder | Execute Raw Query With Parameters

Laravel has a strong query builder to build queries. It is as much powerful as we need. But sometimes we need to execute raw sql using Laravel. Below is a code snippet to execute raw sql queries using Laravel. The below code will return some results.


$results = DB::select( DB::raw("SELECT * FROM table_name WHERE field_id = :field_id"),
array(
    'field_id' => 5,
));

If you dont need to return results and only need to do some statement level queries then do the following:

DB::statement("UPDATE USER SET STATUS='ACTIVE' WHERE ID=1;");


You can get Raw SQL and Bindings from Laravel eloquent query builder:

$query = DB::table("my_table")->where("id", $id);
echo $query->toSql();
print_r($query->getBindings());


Laravel group concat operation

It is important to get some concat value. You can use GROUP_CONCAT in Laravel easily. Below is a code snippet to do the trick:


$result = DB::table('table_name')
       ->select(DB::raw('group_concat(field_name) as field_name_alias'))
       ->where('condition_field', '=', $condition_value )
       ->first();

Simple Laravel Layouts using Blade

The most part of Laravel is usage of layout. It helps you to design a better application. You can create a layout and use that for view pages. Below is a good & simple example how you can create and use layouts.

At first create file named "test.blade.php" under "resources/views/layouts" folder with following contents:


<!DOCTYPE html>
<html>
    <head>
        <title>Title - @yield('title')</title>      
    </head>

    <body>
        <section>
            @yield('content')                
        </section>
        <section>
            @yield('pageScript')
        </section>
    </body>
</html>

The use of the layout file would be as like (from any resources blade file):


@extends('layouts.test') //This line will include layout file

@section('title', 'Title of the page') //Set title of the page

@section('content') //Start content of the page
Html content goes here
@stop //End content of the page

@section('pageScript') //Start javascript of the page
<script src="{{ asset('/js/some-js-file.js') }}"></script>

<script>
$(document).ready(function () {
    
});
</script>
@stop //End javascript of the page

Laravel throw/return custom exception/response

In Laravel it is very simple to handle exception and return custom error codes to view. Below is a example how you can return custom http header code.


<?php
namespace App\Http\Controllers;

use Request;

class TestController extends Controller { 
   public function test_function(Request $request) {
       return response("content", 430)->header('Content-Type', "text/html");
   }
}
?>

Friday, February 10, 2017

VBScript Empty Recycle Bin

VBScript is a scripting language to manage computer developed by Microsoft. Now it is easy to empty your recycle bin easily. Below is a code snippet that describe how recycle bin be empty.

Const RECYCLE_BIN = &Ha&

Set FSO = CreateObject("Scripting.FileSystemObject")
Set ObjShell = CreateObject("Shell.Application")
Set ObjFolder = ObjShell.Namespace(RECYCLE_BIN)
Set ObjFolderItem = ObjFolder.Self
Wscript.Echo ObjFolderItem.Path

Set colItems = ObjFolder.Items
For Each objItem in colItems
    Wscript.Echo objItem.Name
    If (objItem.Type = "File folder") Then
        FSO.DeleteFolder(objItem.Path)
    Else
        FSO.DeleteFile(objItem.Path)
    End If
Next