Tuesday 18 September 2018

Laravel Filtering Eloquent Results and Left Join Example

These scripts join table demographics to countries table by looking into line 2.
Line 7 to 18 shows how can we filter the resulted collection on the results.
Finally, this returns an array of demographics with age, gender, and location as indexes.


       $mixDemographics =DB::table('demographics')
            ->leftJoin('countries', 'demographics.country_id', '=', 'countries.iso_3166_2')
            ->where('user_id',$userId)
            ->get();

        $demographics = [];
        $demographics['age'] = $mixDemographics->filter(function ($demo) {
            return ($demo->metric == 'AGE');
        });

        $demographics['gender'] = $mixDemographics->filter(function ($demo) {
            return ($demo->metric == 'GENDER');
        });


        $demographics['location'] = $mixDemographics->filter(function ($demo) {
            return ($demo->metric == 'COUNTRY');
        });

        return $demographics;



Share:

Tuesday 17 July 2018

Webpush notifications for Laravel

Here the necessary steps in creating a webpush applications in LARAVEL. Add the library in your composer by checking this site.
In order for you to run this site, you must enforce https on your website by adding this line in you AppServiceProvider.php

    public function boot()
    {
        $this->app['request']->server->set('HTTPS', true);
    }
I wanted to use sqlite in heroku so I need to add the sqlite driver in composer.json then run 'composer update'.

    "require": {
        "php": ">=7.0.0",
        "fideloper/proxy": "~3.3",
        "laravel-notification-channels/webpush": "^3.0",
        "laravel/framework": "5.5.*",
        "laravel/tinker": "~1.0",
        "pusher/pusher-php-server": "^3.0",
        "ext-pdo_sqlite": "*"
    },
Share:

Monday 16 July 2018

Return JSON from a Laravel server from a different domain using javascript

Been tinkering for 3 hours how to get the things going. For the javascript side In web.php add the route For the controller make sure you have added ->setCallback($request->input('callback')) at the end of the json response.
Share:

Monday 9 July 2018

Complete Postgres installation step by step

Homebrew is a popular package manager for OS X. To install Postgres with Homebrew, follow the steps below: The first thing to do is install Homebrew if you haven’t done so already. Homebrew site has a simple command that you have to paste in your terminal to do so. Make sure to accept the command line developer tools installation if prompted. Next, run brew install postgres to install Postgres. It might take a little while to compile and install. After compilation is done, it’ll give you some instructions to finish setting it up. The database will be initialized during installation, so there isn’t a need to run initdb to finish installation of Postgres via Homebrew. Near the end of the installation instructions you should see mention of the command brew services. If you don’t already have brew services installed. It may be installed with this command: brew services stop/start postgresql createuser -s postgres

Remove previous versions of PostgreSQL

brew uninstall --force postgresql
Delete all Files of Postgres

rm -rf /usr/local/var/postgres
Install Postgres using Homebrew

brew install postgres
Start Postgres

pg_ctl -D /usr/local/var/postgres start

or

brew services stop/start postgresql

download pgAdmin https://www.postgresql.org/ftp/pgadmin/pgadmin4/v2.0/macos/
Share:

Wednesday 2 May 2018

Complex Laravel Eloquent Example



select   n0y_t_item_description.s_title,
n0y_t_item_description.s_slug as item_slug,
n0y_t_item.d_price         as price,
n0y_t_category_description.s_name,
n0y_t_category_description.s_slug as category_slug,
n0y_t_item_resource.pk_i_id as path_id,
n0y_t_item_resource.s_path as image_path,
n0y_t_item_resource.s_extension as path_ext,
parent_slug from `n0y_t_item` inner join `n0y_t_item_description` on `n0y_t_item_description`.`fk_i_item_id` = `n0y_t_item`.`pk_i_id` inner join `n0y_t_category` on `n0y_t_category`.`pk_i_id` = `n0y_t_item`.`fk_i_category_id` inner join `n0y_t_category_description` on `n0y_t_category`.`pk_i_id` = `n0y_t_category_description`.`fk_i_category_id` inner join `n0y_t_item_resource` on `n0y_t_item_resource`.`fk_i_item_id` = `n0y_t_item`.`pk_i_id` inner join (select pk_i_id, s_slug as parent_slug
from n0y_t_category join n0y_t_category_description on n0y_t_category.pk_i_id = n0y_t_category_description.fk_i_category_id
) as n0y_pCat on `n0y_pCat`.`pk_i_id` = `n0y_t_category`.`fk_i_parent_id` where (`b_active` = '1' and `i_main` = '1') order by RAND() limit 7
         
   
   
   
        $sql = '  xxt_item_description.s_title, 
                  xxt_item_description.s_slug as item_slug, 
                  xxt_item.d_price         as price,
                  xxt_category_description.s_name, 
                  xxt_category_description.s_slug as category_slug,
                  xxt_item_resource.pk_i_id as path_id,
                  xxt_item_resource.s_path as image_path,
                  xxt_item_resource.s_extension as path_ext,
                  parent_slug';

        $sql = str_replace('xx', DB::getTablePrefix(), $sql);


        $joinQry =    "(select pk_i_id, s_slug as parent_slug
                   from xxt_category join xxt_category_description on xxt_category.pk_i_id = xxt_category_description.fk_i_category_id
            ) as xxpCat";

        $joinQry = str_replace('xx', DB::getTablePrefix(), $joinQry);

        $items = DB::table('t_item')
            ->select(DB::raw(
                $sql
            ))
            ->join('t_item_description', 't_item_description.fk_i_item_id', 't_item.pk_i_id')
            ->join('t_category', 't_category.pk_i_id', 't_item.fk_i_category_id')
            ->join('t_category_description', 't_category.pk_i_id', 't_category_description.fk_i_category_id')
            ->join('t_item_resource', 't_item_resource.fk_i_item_id', 't_item.pk_i_id')
            ->where(function ($q) use ($tenDaysAgo, $currentDate) {
                $q->where('b_active', 1)
                   ->where('i_main',1);
//                    ->whereDate('dt_expiration', '>', date($currentDate->toDateTimeString()))
//                    ->whereBetween('dt_pub_date', array(date($tenDaysAgo->toDateTimeString()), date($currentDate->toDateTimeString())));
            })
            ->join(DB::raw($joinQry), function ($join) {
                $join->on("pCat.pk_i_id", "=", "t_category.fk_i_parent_id");
            })

            ->orderByRaw('RAND()')
            // ->order('dt_pub_date','desc')
            ->take(7)
            ->get();



ORIGINAL SQL WHERE EXISTS


SELECT *
FROM `items`
WHERE EXISTS
    (SELECT `items_city`.`id`
     FROM `items_city`
     WHERE items_city.item_id = items.id)
     
Laravel Eloquent Query with Update


  DB::table('contributions')
            ->whereStatusId(ContributionStatus::RECONCILED)
            ->whereNull('submission_id')
            ->whereExists(function ($query) {
                $query->select("declarations.id")
                    ->from('declarations')
                    ->whereRaw('declarations.id = contributions.declaration_id');
            })
            ->update([
                'submission_id' => $submission->id,
            ]);

Share:

Saturday 21 April 2018

Understanding Laravel Return set

Difference between Laravel Eloquent, DB Raw, Json Encode and Json Decode result set. If you're using a DB:select raw command

$currencyRaw = DB::select( DB::raw('select * from currency));
It will give you a type array of objects in which you can display with -> command. The return set will be like this

array (
  0 => 
  stdClass::__set_state(array(
     'pk_c_code' => 'EUR',
     's_name' => 'European Union euro',
     's_description' => 'Euro €',
     'b_enabled' => 1,
  )),
  1 => 
  stdClass::__set_state(array(
     'pk_c_code' => 'GBP',
     's_name' => 'United Kingdom pound',
     's_description' => 'Pound £',
     'b_enabled' => 1,
  )),
  2 => 
  stdClass::__set_state(array(
     'pk_c_code' => 'USD',
     's_name' => 'United States dollar',
     's_description' => 'Dollar US$',
     'b_enabled' => 1,
  )),
)  
If you're using a json_decode with FALSE parameter on eloquent object

        $currencyEloquent = Currency::all();
        $currencyJsonDecodeFalse  = json_decode(($currencyEloquent),false);
It will give you a type array of objects in which you can display with -> command. The return set will be like this

array (
  0 => 
  stdClass::__set_state(array(
     'pk_c_code' => 'EUR',
     's_name' => 'European Union euro',
     's_description' => 'Euro €',
     'b_enabled' => true,
  )),
  1 => 
  stdClass::__set_state(array(
     'pk_c_code' => 'GBP',
     's_name' => 'United Kingdom pound',
     's_description' => 'Pound £',
     'b_enabled' => true,
  )),
  2 => 
  stdClass::__set_state(array(
     'pk_c_code' => 'USD',
     's_name' => 'United States dollar',
     's_description' => 'Dollar US$',
     'b_enabled' => true,
  )),
)
If you're using a ->toArray on eloquent object

        $currencyEloquent = Currency::all();
        $currencyArray  = $currencyEloquent->toArray();
It will give you a type array with keys and values in which you can display with $currency['s_name']. The return set will be like this

array (
  0 => 
  array (
    'pk_c_code' => 'EUR',
    's_name' => 'European Union euro',
    's_description' => 'Euro €',
    'b_enabled' => true,
  ),
  1 => 
  array (
    'pk_c_code' => 'GBP',
    's_name' => 'United Kingdom pound',
    's_description' => 'Pound £',
    'b_enabled' => true,
  ),
  2 => 
  array (
    'pk_c_code' => 'USD',
    's_name' => 'United States dollar',
    's_description' => 'Dollar US$',
    'b_enabled' => true,
  ),
)  
If you're using a json_decode with TRUE parameter on eloquent object

        $currencyEloquent = Currency::all();
        $currencyJsonDecodeFalse  = json_decode(($currencyEloquent),true);
It will give you a type array with keys and value in which you can display with $currency['s_name'] command. The return set will be like this

array (
  0 => 
  array (
    'pk_c_code' => 'EUR',
    's_name' => 'European Union euro',
    's_description' => 'Euro €',
    'b_enabled' => true,
  ),
  1 => 
  array (
    'pk_c_code' => 'GBP',
    's_name' => 'United Kingdom pound',
    's_description' => 'Pound £',
    'b_enabled' => true,
  ),
  2 => 
  array (
    'pk_c_code' => 'USD',
    's_name' => 'United States dollar',
    's_description' => 'Dollar US$',
    'b_enabled' => true,
  ),
)  

Various ways to play with the return set of DB:RAW


        $sql = "select * from users";
        $keyUserIds = DB::select(DB::raw($sql));
        // $keyUserIds is an array of objects

        //converted to collection
        $collectionUserId = collect($keyUserIds);

        //to get columns
        $pluckedUserId = $collectionUserId->pluck('user_id');


        //Convert array of objects to array of ids only
        $user_ids = array_column($keyUserIds, 'user_id');
Share:

Monday 12 March 2018

Laravel Seeding Example with Faker library

Install faker from https://github.com/fzaninotto/Faker

composer require fzaninotto/faker
From the command line

php artisan make:seeder UsersTableSeeder




use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->call(UsersTableSeeder::class);
    }
}





use Illuminate\Database\Seeder;

class UsersTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        factory(\App\User::class)->create([
            'email' => 'admin@admin.com',
            'password' => '123',
        ]);

        factory(\App\User::class, 100)->create();
    }
}

Share:

Popular Posts

Recent Posts

Pages

Powered by Blogger.

About Me

My photo
For the past 10 years, I've been playing with codes using PHP, Java, Rails. I do this for a living and love new things to learn and the challenges that comes with it. Besides programming I love spending time with friends and family and can often be found together catching the latest movie or planning a trip to someplace I've never been before.