Friday, July 31, 2020

Remove and update duplicate value comma separate value ids to unique in mysql query

Hi All,

After lot of R&D I made a below query for remove duplicate comma separate value in mysq table. I \

Hope it will help you :)



UPDATE sdbi_catalog_product
 JOIN
 (SELECT `productID`,GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(sdbi_catalog_product.catID, ',', sub0.aNum), ',', -1)) AS ids
FROM sdbi_catalog_product
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(sdbi_catalog_product.catID) - LENGTH(REPLACE(sdbi_catalog_product.catID, ',', ''))) >= sub0.aNum
GROUP BY productID)x
ON x.productID=sdbi_catalog_product.productID

SET sdbi_catalog_product.catID=x.ids

Saturday, July 11, 2020

Top and basic Laravel interview questions

Top and basic Laravel interview questions
1) What is Laravel?
Ans: Laravel is a free open source "PHP framework" based on the MVC design pattern.
It is created by Taylor Otwell. Laravel provides expressive and elegant syntax that helps in creating a wonderful web application easily and quickly.
2) Explain Events in laravel ?
Ans : An event is an action or occurrence recognized by a program that may be handled by the program or code. Laravel events provides a simple observer implementation, that allowing you to subscribe and listen for various events/actions that occur in your application.
3) Explain validations in laravel?
Ans : In Programming validations are a handy way to ensure that your data is always in a clean and expected format before it gets into your database.

Laravel provides several different ways to validate your application incoming data.By default Laravel’s base controller class uses a ValidatesRequests trait which provides a convenient method to validate all incoming HTTP requests coming from client.You can also validate data in laravel by creating Form Request.

Laravel validation Example

$validatedData = $request->validate([
            'name' => 'required|max:255',
            'username' => 'required|alpha_num',
            'age' => 'required|numeric',
        ]);

4) How to install laravel via composer ?
Ans : You can install Laravel via composer by running below command.

composer create-project laravel/laravel your-project-name version

5) List some features of laravel 6 ?
Ans  :
Laravel 6 features

Inbuilt CRSF (cross-site request forgery ) Protection.
Inbuilt paginations
Reverse Routing
Query builder
Route caching
Database Migration
IOC (Inverse of Control) Container Or service container.
Job middleware
Lazy collections
6) What is PHP artisan? List out some artisan commands ?
Ans : PHP artisan is the command line interface/tool included with Laravel. It provides a number of helpful commands that can help you while you build your application easily. Here are the list of some artisan command:-

php artisan list
php artisan help
php artisan tinker
php artisan make
php artisan –versian
php artisan make model model_name
php artisan make controller controller_name
7) List some default packages provided by Laravel Framework?
Ans :
Below are a list of some official/ default packages provided by Laravel

Cashier
Envoy
Passport
Scout
Socialite
Horizon
Telescope
8) What are named routes in Laravel?
Ans : Named routing is another amazing feature of Laravel framework. Named routes allow referring to routes when generating redirects or Urls more comfortably.
You can specify named routes by chaining the name method onto the route definition:

Route::get('user/profile', function () {
    //
})->name('profile');

You can specify route names for controller actions:

Route::get('user/profile', 'UserController@showProfile')->name('profile');
9) What is database migration. How to create migration via artisan ?
Ans : Migrations are like version control for your database, that’s allow your team to easily modify and share the application’s database schema. Migrations are typically paired with Laravel’s schema builder to easily build your application’s database schema.

Use below commands to create migration data via artisan.

// creating Migration
php artisan make:migration create_users_table
10) What are service providers in Laravel ?
Ans :Service Providers are a central place where all laravel applications are bootstrapped . Your application as well all Laravel core services are also bootstrapped by service providers.
All service providers extend the Illuminate\Support\ServiceProvider class. Most service providers contain a register and a boot method. Within the register method, you should only bind things into the service container. You should never attempt to register any event listeners, routes, or any other piece of functionality within the register method.
11) Explain Laravel’s service container ?
Ans : One of the most powerful features of Laravel is its Service Container. It is a powerful tool for resolving class dependencies and performing dependency injection in Laravel.
Dependency injection is a fancy phrase that essentially means class dependencies are “injected” into the class via the constructor or, in some cases, “setter” methods.
12) What is a composer ?
Ans : Composer is a tool for managing dependency in PHP. It allows you to declare the libraries on which your project depends on and will manage (install/update) them for you.
Laravel utilizes Composer to manage its dependencies.

13) What is dependency injection in Laravel ?
Ans : In software engineering, dependency injection is a technique whereby one object supplies the dependencies of another object. A dependency is an object that can be used (a service). An injection is the passing of a dependency to a dependent object (a client) that would use it. The service is made part of the client’s state.[1] Passing the service to the client, rather than allowing a client to build or find the service, is the fundamental requirement of the pattern.
14) What are Laravel Contract’s ?
Ans : Laravel's Contracts are nothing but a set of interfaces that define the core services provided by the Laravel framework.
15) Explain Facades in Laravel ?
Ans : Laravel Facades provides a static like an interface to classes that are available in the application’s service container. Laravel self-ships with many facades which provide access to almost all features of Laravel ’s. Laravel facades serve as “static proxies” to underlying classes in the service container and provide benefits of a terse, expressive syntax while maintaining more testability and flexibility than traditional static methods of classes. All of Laravel’s facades are defined in the Illuminate\Support\Facades namespace. You can easily access a facade like so:


use Illuminate\Support\Facades\Cache;

Route::get('/cache', function () {
    return Cache::get('key');
});
16) What are Laravel eloquent?
Ans : Laravel's Eloquent ORM is simple Active Record implementation for working with your database. Laravel provide many different ways to interact with your database, Eloquent is most notable of them. Each database table has a corresponding “Model” which is used to interact with that table. Models allow you to query for data in your tables, as well as insert new records into the table.

Below is sample usage for querying and inserting new records in Database with Eloquent.


// Querying or finding records from products table where tag is 'new'
$products= Product::where('tag','new');
// Inserting new record
 $product =new Product;
 $product->title="Iphone 7";
 $product->price="$700";
 $product->tag='iphone';
 $product->save();
17) How to enable query log in Laravel ?
Ans : Use the enableQueryLog method to enable query log in Laravel


DB::connection()->enableQueryLog();
You can get array of the executed queries by using getQueryLog method:
$queries = DB::getQueryLog();
18) What is reverse routing in Laravel?
Ans : Laravel reverse routing is generating URL's based on route declarations. Reverse routing makes your application so much more flexible. It defines a relationship between links and Laravel routes. When a link is created by using names of existing routes, appropriate Uri's are created automatically by Laravel. Here is an example of reverse routing.

// route declaration

Route::get('login', 'users@login');
Using reverse routing we can create a link to it and pass in any parameters that we have defined. Optional parameters, if not supplied, are removed from the generated link.

{{ HTML::link_to_action('users@login') }}
19) How to turn off CRSF protection for specific route in Laravel?
Ans : To turn off CRSF protection in Laravel add following codes in “app/Http/Middleware/VerifyCsrfToken.php”


//add an array of Routes to skip CSRF check
private $exceptUrls = ['controller/route1', 'controller/route2'];
 //modify this function
public function handle($request, Closure $next) {
 //add this condition foreach($this->exceptUrls as $route) {
 if ($request->is($route)) {
  return $next($request);
 }
}
return parent::handle($request, $next);
}
20) What are traits in Laravel?
Ans : PHP Traits are simply a group of methods that you want include within another class. A Trait, like an abstract class cannot be instantiated by itself.Trait are created to reduce the limitations of single inheritance in PHP by enabling a developer to reuse sets of methods freely in several independent classes living in different class hierarchies.

Here is an example of a trait.

trait Sharable {

  public function share($item)
  {
    return 'share this item';
  }

}

You could then include this Trait within other classes like this:


class Post {

  use Shareable;

}

class Comment {

  use Shareable;

}
Now if you were to create new objects out of these classes you would find that they both have the share() method available:


$post = new Post;
echo $post->share(''); // 'share this item'

$comment = new Comment;
echo $comment->share(''); // 'share this item'
21) Does Laravel support caching?
Ans  : Yes, Laravel supports popular caching backends like Memcached and Redis.
By default, Laravel is configured to use the file cache driver, which stores the serialized, cached objects in the file system.For large projects, it is recommended to use Memcached or Redis.
22) Explain Laravel’s Middleware?
Ans : As the name suggests, Middleware acts as a middleman between request and response. It is a type of filtering mechanism. For example, Laravel includes a middleware that verifies whether the user of the application is authenticated or not. If the user is authenticated, he will be redirected to the home page otherwise, he will be redirected to the login page.

There are two types of Middleware in Laravel.
Global Middleware: will run on every HTTP request of the application.
Route Middleware: will be assigned to a specific route.
23) What is Lumen?
Ans : Lumen is PHP micro-framework that built on Laravel’s top components.It is created by Taylor Otwell. It is perfect option for building Laravel based micro-services and fast REST API’s. It’s one of the fastest micro-frameworks available.
You can install Lumen using composer by running below command

composer create-project --prefer-dist laravel/lumen blog
24) Explain Bundles in Laravel?
Ans : In Laravel, bundles are also called packages. Packages are the primary way to extend the functionality of Laravel. Packages might be anything from a great way to work with dates like Carbon, or an entire BDD testing framework like Behat.In Laravel, you can create your custom packages too.
25) How to use a custom table in Laravel Modal ?
Ans : You can use a custom table in Laravel by overriding protected $table property of Eloquent.


Below is sample uses

class User extends Eloquent{
 protected $table="my_user_table";

}
26) List types of relationships available in Laravel Eloquent?
Ans : Below are types of relationships supported by Laravel Eloquent ORM.

One To One
One To Many
One To Many (Inverse)
Many To Many
Has Many Through
Polymorphic Relations
Many To Many Polymorphic Relations
27) Why are migrations necessary?
Migrations are necessary because:

Without migrations, database consistency when sharing an app is almost impossible, especially as more and more people collaborate on the web app.
Your production database needs to be synced as well.
28) Provide System requirements for installation of Laravel framework ?
In order to install Laravel, make sure your server meets the following requirements:

PHP >= 7.1.3
OpenSSL PHP Extension
PDO PHP Extension
Mbstring PHP Extension
Tokenizer PHP Extension
XML PHP Extension
Ctype PHP Extension
JSON PHP Extension
29) List some Aggregates methods provided by query builder in Laravel ?
count()
max()
min()
avg()
sum()