クエリビルダはあまり使わないのだけど、レコードの有無を取得する場合や集計処理なんかでは使うことがある🐱

生クエリがこうだとする

select
  *
from
  `employee_emails`
left join `employee_skills` on `employee_skills`.`employee_email_id` = `employee_emails`.`id`
    and `employee_skills.`name` = "Engineer" left join `user_account_register_email_settings` on `user_account_register_email_settings`.`employee_email_id` = `employee_emails`.`id` where `employee_emails`.`id` = 65 and (`employee_skills`.`employee_email_id = 65 or `user_account_register_email_settings`.`employee_email_id` = 65) limit 1

JOINはLEFT JOINとINNER JOINの違いを意識して使おう🐱

• INNER JOIN … &&での結合
• LEFT JOIN … || での結合。&&ではない箇所はnullで結合される

クエリビルダだとこうなる

namespace App\Repositories\Employee;

use App\Entities\EmployeeEmail;
use App\Repositories\Traits\ResourceConstructTrait;

class EmployeeEmailRepository implements EmployeeEmailInterface
{
    use ResourceConstructTrait;

    public function __construct(EmployeeEmail $resource)
    {
        $this->resource = $resource;
    }

    public function isEngineer(int $id): bool
    {
        // クエリビルダー開始
        $query = $this->resource->query();

        $query->leftJoin('employee_skills', function ($join) {
            $join->on(
                'employee_skills.employee_email_id',
                '=',
                'employee_emails.id'
            );
            $join->where('employee_skills.name', 'engineer');
        });
        $query->leftJoin('user_account_register_email_settings', function ($join) {
            $join->on(
                'user_account_register_email_settings.employee_email_id',
                '=',
                'employee_emails.id'
            );
        });
        $query->where('employee_emails.id', $id);
        $query->where(function ($query) use ($id) {
            $query->where('employee_skills.employee_email_id', $id)
                ->orWhere('user_account_register_email_settings.employee_email_id', $id);
        });
        // レコードがあるかで判定
        return is_null($query->first()) ? false : true;
    }
}