PHP Builder::selectraw方法代码示例

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     if (isset($model->sort)) {
         $builder->selectraw("\n\t\t\t\t\t\t\tsum(IFNULL((SELECT sum((price - discount) * quantity) FROM transaction_details WHERE transaction_details.transaction_id = transactions.id and transaction_details.deleted_at is null),0)\n\t\t\t\t\t\t\t+ IFNULL((SELECT sum(price) FROM transaction_extensions WHERE transaction_extensions.transaction_id = transactions.id and transaction_extensions.deleted_at is null),0)\n\t\t\t\t\t\t\t+ transactions.shipping_cost - transactions.voucher_discount - transactions.unique_number\n\t\t\t\t\t\t\t) as amount\n\t\t\t\t\t\t")->orderby($model->sort, $model->sort_param);
     } else {
         $builder->selectraw("\n\t\t\t\t\t\tsum(IFNULL((SELECT sum((price - discount) * quantity) FROM transaction_details WHERE transaction_details.transaction_id = transactions.id and transaction_details.deleted_at is null),0)\n\t\t\t\t\t\t+ IFNULL((SELECT sum(price) FROM transaction_extensions WHERE transaction_extensions.transaction_id = transactions.id and transaction_extensions.deleted_at is null),0)\n\t\t\t\t\t\t+ transactions.shipping_cost - transactions.voucher_discount - transactions.unique_number\n\t\t\t\t\t\t) as amount\n\t\t\t\t\t");
     }
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     if (in_array(get_class($model), ['App\\Models\\Person', 'App\\Models\\Employee'])) {
         $builder->selectraw('IFNULL(if(item="phone", value, ""), "not available") as phone')->selectraw('IFNULL(if(item="address", value, ""), "not available") as address')->selectraw('IFNULL(if(item="email", value, ""), "not available") as email')->leftjoin('contacts', function ($join) {
             $join->on('persons.id', '=', 'contacts.person_id')->whereIn('contacts.person_type', ['App\\Models\\Person', 'App\\Models\\Employee'])->where('contacts.is_default', '=', true)->wherenull('contacts.deleted_at');
         })->groupby('persons.id');
     } else {
         $builder->selectraw('IFNULL(if(item="phone", value, ""), "not available") as phone')->selectraw('IFNULL(if(item="address", value, ""), "not available") as address')->selectraw('IFNULL(if(item="email", value, ""), "not available") as email')->leftjoin('contacts', function ($join) {
             $join->on('branches.id', '=', 'contacts.branch_id')->whereIn('contacts.branch_type', ['App\\Models\\Branch', 'App\\Models\\Employee'])->where('contacts.is_default', '=', true)->wherenull('contacts.deleted_at');
         })->groupby('branches.id');
     }
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     if ($model->getTable() == 'tmp_vouchers') {
         $builder->selectraw('IFNULL(sum(quota_logs.amount),0) as quota')->leftjoin('quota_logs', function ($join) {
             $join->on('quota_logs.voucher_id', '=', 'tmp_vouchers.id')->wherenull('quota_logs.deleted_at');
         })->groupby('tmp_vouchers.id');
     } else {
         $builder->selectraw('IFNULL(sum(quota_logs.amount),0) as quota_referral')->selectraw('IF(tmp_vouchers.type="referral", tmp_vouchers.code, "BALIN") as code_referral')->leftjoin('tmp_vouchers', function ($join) {
             $join->on('tmp_vouchers.user_id', '=', 'users.id')->wherein('type', ['referral', 'promo_referral'])->wherenull('tmp_vouchers.deleted_at');
         })->leftjoin('quota_logs', function ($join) {
             $join->on('quota_logs.voucher_id', '=', 'tmp_vouchers.id')->wherenull('quota_logs.deleted_at');
         })->groupby('users.id');
     }
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     if (isset($model->workend)) {
         $end = $model->workend;
     } else {
         $end = 'now';
     }
     $prefix_person = 'hrps_';
     //env('DB_PREFIX_HR_PERSON');
     $prefix_empl = 'hres_';
     //env('DB_PREFIX_HR_EMPLOYMENT');
     $prefix_org = 'hrom_';
     //env('DB_PREFIX_HR_ORGANISATION');
     $builder->selectraw('CONCAT(' . $prefix_org . 'charts.name, " cabang ", ' . $prefix_org . 'branches.name) as newest_position')->selectraw($prefix_org . 'branches.organisation_id as organisation_id')->selectraw($prefix_org . 'charts.department as newest_department')->selectraw($prefix_empl . 'works.id as newest_work_id')->selectraw($prefix_empl . 'works.nik as newest_nik')->selectraw($prefix_empl . 'works.status as newest_status')->selectraw($prefix_empl . 'works.start as newest_work_start')->selectraw($prefix_empl . 'works.end as newest_work_end')->join(DB::raw($prefix_empl . 'works'), function ($join) use($end, $prefix_empl, $prefix_person) {
         $join->on(DB::raw($prefix_person . 'persons.id'), '=', DB::raw($prefix_empl . 'works.person_id'))->where(function ($query) use($end, $prefix_empl) {
             $query->where(function ($query) use($end, $prefix_empl) {
                 $query;
                 // $query->wherenull( DB::raw($prefix_empl.'works.end'))
                 // ->orwhere( DB::raw($prefix_empl.'works.end'), '>=', date('Y-m-d H:i:s', strtotime($end)));
             })->wherenull(DB::raw($prefix_empl . 'works.deleted_at'));
         });
     })->join(DB::raw($prefix_org . 'charts'), function ($join) use($prefix_empl, $prefix_org) {
         $join->on(DB::raw($prefix_empl . 'works.chart_id'), '=', DB::raw($prefix_org . 'charts.id'))->wherenull(DB::raw($prefix_org . 'charts.deleted_at'));
     })->join(DB::raw($prefix_org . 'branches'), function ($join) use($prefix_org) {
         $join->on(DB::raw($prefix_org . 'charts.branch_id'), '=', DB::raw($prefix_org . 'branches.id'))->wherenull(DB::raw($prefix_org . 'branches.deleted_at'));
     })->groupby(DB::raw($prefix_person . 'persons.id'));
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $prefix_empl = 'hres_';
     //env('DB_PREFIX_HR_EMPLOYMENT');
     $builder->selectraw('tl1.grade as grade')->leftjoin(DB::raw($prefix_empl . 'grade_logs as tl1'), function ($join) use($prefix_empl) {
         $join->on(DB::raw($prefix_empl . 'works.id'), '=', 'tl1.work_id')->on(DB::raw('(tl1.id = (select id from ' . $prefix_empl . 'grade_logs as tl2 where tl2.work_id <> tl1.work_id and tl2.deleted_at is null order by tl2.updated_at desc limit 1))'), DB::raw(''), DB::raw(''))->wherenull('tl1.deleted_at');
     })->groupby(DB::raw($prefix_empl . 'works.id'));
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw('IFNULL(users2.name, IFNULL(vouchers2.code,"EMPTY")) as reference_name')->leftjoin('point_logs', function ($join) {
         $join->on('point_logs.user_id', '=', 'users.id')->whereIn('point_logs.reference_type', ['App\\Models\\User', 'App\\Models\\Voucher'])->wherenull('point_logs.deleted_at');
     })->leftjoin(DB::raw('(SELECT name, id, deleted_at from users) as users2'), function ($join) {
         $join->on('users2.id', '=', 'point_logs.reference_id')->where('point_logs.reference_type', '=', 'App\\Models\\User')->wherenull('users2.deleted_at');
     })->leftjoin(DB::raw('(SELECT code, id, deleted_at from tmp_vouchers) as vouchers2'), function ($join) {
         $join->on('vouchers2.id', '=', 'point_logs.reference_id')->where('point_logs.reference_type', '=', 'App\\Models\\Voucher')->wherenull('vouchers2.deleted_at');
     });
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw('persons.*')->selectraw('CONCAT(charts.name, " cabang ", branches.name) as jabatan')->join('works', function ($join) {
         $join->on('persons.id', '=', 'works.person_id')->wherenull('works.end')->orwhere('works.end', '>=', date('Y-m-d H:i:s'))->wherenull('works.deleted_at');
     })->join('works_authentications', function ($join) {
         $join->on('works.id', '=', 'works_authentications.work_id')->wherenull('works_authentications.deleted_at');
     })->join('charts', function ($join) {
         $join->on('works.chart_id', '=', 'charts.id')->wherenull('charts.deleted_at');
     })->join('branches', function ($join) {
         $join->on('charts.branch_id', '=', 'branches.id')->wherenull('branches.deleted_at');
     })->orderby('tmp_auth_group_id', 'asc')->groupby('persons.id');
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     if (isset($model->workend)) {
         $end = $model->workend;
     } else {
         $end = 'now';
     }
     $builder->selectraw('CONCAT(charts.name, " cabang ", branches.name) as current_job')->selectraw('works.id as current_work_id')->selectraw('works.calendar_id as current_calendar_id')->selectraw('works.grade as current_grade')->join('works', function ($join) use($end) {
         $join->on('persons.id', '=', 'works.person_id')->where(function ($query) use($end) {
             $query->wherenull('works.end')->orwhere('works.end', '>=', date('Y-m-d H:i:s', strtotime($end)));
         });
     })->join('charts', function ($join) {
         $join->on('works.chart_id', '=', 'charts.id')->wherenull('charts.deleted_at');
     })->join('branches', function ($join) {
         $join->on('charts.branch_id', '=', 'branches.id')->wherenull('branches.deleted_at');
     })->groupby('persons.id');
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw("\n\t\t\t\t\tsum(IFNULL((SELECT abs(sum(amount)) FROM point_logs WHERE point_logs.reference_id = transactions.id and point_logs.deleted_at is null and point_logs.reference_type like '%Sale%' and point_logs.amount < 0),0)\n\t\t\t\t\t) as point_discount\n\t\t\t\t");
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $prefix_contact = 'hrom_';
     $builder->selectraw('(SELECT IFNULL(value, "not available") from ' . $prefix_contact . 'contacts as contacts where contacts.contactable_id = ' . $model->getTable() . '.id and contacts.contactable_type like "%' . class_basename($model) . '" and type = "address" and is_default = 1 and contacts.deleted_at is null) as address')->selectraw('(SELECT IFNULL(value, "not available") from ' . $prefix_contact . 'contacts as contacts where contacts.contactable_id = ' . $model->getTable() . '.id and contacts.contactable_type like "%' . class_basename($model) . '" and type = "phone" and is_default = 1 and contacts.deleted_at is null) as phone')->selectraw('(SELECT IFNULL(value, "not available") from ' . $prefix_contact . 'contacts as contacts where contacts.contactable_id = ' . $model->getTable() . '.id and contacts.contactable_type like "%' . class_basename($model) . '" and type = "email" and is_default = 1 and contacts.deleted_at is null) as email')->groupby($model->getTable() . '.id');
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw('IFNULL(marital_statuses.status,"tidak ada") as marital_status')->leftjoin('marital_statuses', function ($join) {
         $join->on('persons.id', '=', 'marital_statuses.person_id')->on(DB::raw('(marital_statuses.id = (select id from marital_statuses as ms2 where ms2.person_id = marital_statuses.person_id and ms2.deleted_at is null order by ms2.id desc limit 1))'), DB::raw(''), DB::raw(''))->wherenull('marital_statuses.deleted_at');
     });
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw('IFNULL(transaction_logs.status, "na") as status')->lefttransactionlogstatus(['cart', 'wait', 'payment_process', 'paid', 'packed', 'shipping', 'delivered', 'canceled', 'abandoned'])->groupby('transactions.id');
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw($model->getTable() . '.*');
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw('images.thumbnail as thumbnail')->selectraw('images.image_xs as image_xs')->selectraw('images.image_sm as image_sm')->selectraw('images.image_md as image_md')->selectraw('images.image_lg as image_lg')->leftjoin('images', function ($join) use($model) {
         $join->on('images.imageable_id', '=', $model->getTable() . '.id')->where('images.imageable_type', '=', get_class($model))->where('images.is_default', '=', true)->wherenull('images.deleted_at');
     });
 }

 /**
  * Apply the scope to a given Eloquent query builder.
  *
  * @param  \Illuminate\Database\Eloquent\Builder  $builder
  * @param  \Illuminate\Database\Eloquent\Model  $model
  * @return void
  */
 public function apply(Builder $builder, Model $model)
 {
     $builder->selectraw('addresses.address as current_address')->selectraw('addresses.zipcode as current_zipcode')->selectraw('addresses.phone as current_phone')->leftjoin('addresses', function ($join) use($model) {
         $join->on('addresses.owner_id', '=', $model->getTable() . '.id')->where('addresses.owner_type', '=', get_class($model))->wherenull('addresses.deleted_at');
     });
 }