Command needed when installing MySQL

MySQL command not found? Looking tru this forum https://stackoverflow.com/questions/10577374/mysql-command-not-found-in-os-x-10-7

    echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile

To connect to MySQL prompt

    mysql -u root -p

Change the MySQL password permanently

    mysql -u root -p
    ALTER USER `root`@`localhost` IDENTIFIED BY 'password', `root`@`localhost` PASSWORD EXPIRE NEVER;

Read More

Laravel Eloquent Relationship 101

Laravel uses the default 'id' as the primary key when defining table.
With this in mind we will be having difficulty especially if we are using Laravel eager loading style as stated in stackoverflow.
In order to have that peace of mind when loading fields it is safer to create a different id key for different table. This is how to go with that approach.
For our parent migration script we have

 
<?php  
 use Illuminate\Support\Facades\Schema;  
 use Illuminate\Database\Schema\Blueprint;  
 use Illuminate\Database\Migrations\Migration;  
 class CreateAuthorsTable extends Migration  
 {  
   public function up()  
   {  
     Schema::create('authors', function (Blueprint $table) {  
       $table->bigIncrements('au_id');  
       $table->string('name');  
       $table->timestamp('birth_date');  
       $table->unsignedBigInteger('au_st_id');  
       $table->timestamps();  
     });  
   }  
   public function down()  
   {  
     Schema::dropIfExists('authors');  
   }  
 }  

For our child table, we will have

 
<?php  

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBooksTable extends Migration
{
    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->bigIncrements('bk_id');
            $table->unsignedBigInteger('bk_au_id');
            $table->string('title');
            $table->timestamp('published_date')->nullable();
            $table->timestamps();

            $table->foreign('bk_au_id')->references('au_id')->on('authors')->onDelete('cascade');
        });
    }
    
    public function down()
    {
        Schema::dropIfExists('books');
    }
}

To complete the migration script for address

 
<?php  

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateAuthorAddressesTable extends Migration
{
    public function up()
    {
        Schema::create('author_addresses', function (Blueprint $table) {
            $table->bigIncrements('aud_id');
            $table->unsignedBigInteger('aud_au_id');
            $table->string('city')->nullable();
            $table->string('address')->nullable();
            $table->timestamps();

            $table->foreign('aud_au_id')->references('au_id')->on('authors')->onDelete('cascade');
        });
    }
    
    public function down()
    {
        Schema::dropIfExists('author_addresses');
    }
}

This is how to define the relationship that an Author can write 1 or more books. The relationship also shows that he lives in just one (1)address at a time

 

<?php  

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Author extends Model
{
    protected $primaryKey = 'au_id';

    public function books(): HasMany
    {
        return $this->hasMany('App\Book', 'bk_au_id', 'au_id');
    }

    public function address(): HasOne
    {
        return $this->hasOne('App\AuthorAddress','aud_au_id','au_id');
    }
}

This is to define that a book belongs to an author

 

<?php  

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Book extends Model
{
    protected $primaryKey = 'bk_id';

    public function author(): BelongsTo
    {
        return $this->belongsTo('App\Author', 'bk_au_id', 'au_id');
    }
}

Read More

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;

Read More

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,
            ]);

Another exist implemenation

    
          $tenantUsers = User::whereHas('roles', function ($query) {
            $query->whereIn('roles.name', ['broker', 'manager', 'administrator']);
        })
            ->with(['roles' => function ($q) {
                $q->whereIn('roles.name', ['broker', 'manager', 'administrator'])
                    ->select('roles.id', 'roles.name');
            }])->where(function ($query) use ($search) {
                $query->where('email', 'LIKE', '%' . $search . '%')
                    ->orWhere(DB::raw("concat(first_name, ' ', last_name)"), 'LIKE', "%" . $search . "%")
                    ->orWhere('first_name', 'LIKE', '%' . $search . '%')
                    ->orWhere('last_name', 'LIKE', '%' . $search . '%');
            })->get(['id', 'first_name', 'last_name', 'email']);  


select 
  id, 
  first_name, 
  last_name, 
  email 
from 
  users 
where 
  exists (
    select 
      * 
    from 
      roles 
      inner join model_has_roles on roles.id = model_has_roles.role_id 
    where 
      users.id = model_has_roles.model_uuid 
      and model_has_roles.model_type = 'App\Models\Universal\User' 
      and roles.name in ('broker', 'manager', 'administrator') 
      and model_has_roles.team_id is null 
      and (roles.team_id is null or roles.team_id is null)
  ) 
  and (
    email LIKE '%dev%' 
    or concat(first_name, ' ', last_name) LIKE '%dev% '
    or first_name LIKE '%dev%' 
    or last_name LIKE '%dev%'
  ) 
  and users.deleted_at is null

Another exist implemenation

  
  
          return Contact::where('is_third_party', true)
            ->when($search, function ($query) use ($search) {
                $query->where(function ($q) use ($search) {
                    $q->orWhere('mobile_number', 'LIKE', '%' . $search . '%')
                        ->orWhere('email_address', 'LIKE', '%' . $search . '%');
                });
            })->get();
            
            
        select 
          * 
        from 
          `contacts` 
        where 
          `is_third_party` = 1 
          and (
            `mobile_number` LIKE '%3%' or `email_address` LIKE '%3%'
          ) 
          and `contacts`.`deleted_at` is null

  

Another exist implemenation

  
  
$contacts = Contact::query()
            ->join('applications', 'applications.id', '=', 'contacts.application_id')
            ->join('users as brokers', 'brokers.id', '=', 'applications.broker_uuid')
            ->when($searchString, function ($query) use ($searchString) {
                //these where creates a parenthesis for the or
                $query->where(function ($query) use ($searchString) {
                    $query->where('contacts.first_name', 'like', '%' . $searchString . '%')
                        ->orWhere('contacts.last_name', 'like', '%' . $searchString . '%')
                        ->orWhere('contacts.email_address', 'like', '%' . $searchString . '%');
                });
            })
            ->when($brokerId, function ($query) use ($brokerId) {
                $query->where('applications.broker_uuid', $brokerId);
            })
            ->select(['contacts.*', 'brokers.first_name as  broker_first_name', 'brokers.last_name as broker_last_name']);
            

SELECT `contacts`.*,
       `brokers`.`first_name` AS `broker_first_name`,
       `brokers`.`last_name`  AS `broker_last_name`
FROM   `contacts`
       INNER JOIN `applications`
               ON `applications`.`id` = `contacts`.`application_id`
       INNER JOIN `users` AS `brokers`
               ON `brokers`.`id` = `applications`.`broker_uuid`
WHERE  ( `contacts`.`first_name` LIKE '%popo%'
          OR `contacts`.`last_name` LIKE '%popo%'
          OR `contacts`.`email_address` LIKE '%popo%' )
       AND `applications`.`broker_uuid` = '2bb75f31-8a87-443d-421a-3f21f4992322' 
       
  

Another exist implemenation

  
  
  here_here
  

Another exist implemenation

  
  
  here_here
  

Another exist implemenation

  
  
  here_here
  

Another exist implemenation

  
  
  here_here
  

Read More