How I can join two tables if one column contains multiple ID's.
Little story, I have branch and in my branch have many employees and managers. I have table Branch where is column managers and employees.
I have to show to each my manager his branches.
Branch table
| ID | Name | Business_ID | Managers | Employees | status |
|---|---|---|---|---|---|
| 1 | Beauty | 15 | 4,5,9,17 | 44,76,54,72 | 1 |
So if I'm manager and my id is 5 I should see this "Beauty" row.
table > branch
| id | name | address | for_who | description | icon | start_time | end_time | business_id | is_featured | status |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Beauty OY | New York avn 6 | 1 | Little desc.. | 45hjds.png | 09:00:00 | 19:00:00 | 15 | 1 | 1 |
table > branch_categories
| branch_id | category_id |
|---|---|
| 1 | 1 |
table > branch_managers
| branch_id | user_id |
|---|---|
| 1 | 4 |
| 1 | 5 |
| 1 | 9 |
| 1 | 17 |
table > branch_employees
| branch_id | user_id |
|---|---|
| 1 | 44 |
| 1 | 76 |
| 1 | 54 |
| 1 | 72 |
Model > Branch.php
public $table = 'branch';
protected $dates = [
'created_at',
'updated_at',
'deleted_at',
];
protected $guarded = [];
protected $withCount = ['reviews'];
protected $appends = ['imageUri', 'avg_rating'];
public function getImageUriAttribute()
{
if (isset($this->attributes['icon'])) {
return url('upload/') . '/' . $this->attributes['icon'];
}
}
public function managers(){
return $this->belongsToMany(Branch::class);
}
public function employees(){
return $this->belongsToMany(Branch::class);
}
public function getAvgRatingAttribute()
{
$revData = Review::where('branch_id', $this->attributes['id'])->get();
$star = $revData->sum('star');
if ($star > 1) {
$t = $star / count($revData);
return number_format($t, 1, '.', '');
}
return 0;
}
public function Reviews()
{
return $this->hasMany('App\Review', 'branch_id', 'id')->orderBy('created_at', 'desc');
}
So I created two models more for branch employees and branch manager. But I already have existing EmployeeInfo model and controller. I dont know if doing right way or not.
Model > BranchEmployee.php
class BranchEmployee extends Model
{
use SoftDeletes;
public $table = 'branch_employees';
protected $guarded = [];
public function branches(){
return $this->belongsToMany(BranchEmployee::class);
}
}
and employeeInfo model looks like this:
Model > EmployeeInfo.php
class EmployeeInfo extends Model
{
public $table = 'employee_detail';
protected $dates = [
'created_at',
'updated_at',
];
protected $fillable = [
'emp_id', 'address', 'description', 'service', 'icon', 'experience', 'status',
];
protected $appends = ['imageUri', 'avg_rating'];
public function setServiceAttribute($value)
{
$this->attributes['service'] = implode(',', $value);
}
public function getServiceAttribute($value)
{
return explode(',', $value);
}
public function getImageUriAttribute()
{
if (isset($this->attributes['icon'])) {
return url('upload/') . '/' . $this->attributes['icon'];
}
}
public function User()
{
return $this->belongsTo('App\User', 'emp_id', 'id');
}
public function getAvgRatingAttribute()
{
return 4;
$revData = Review::where('provider_id', $this->attributes['id'])->get();
$star = $revData->sum('star');
if ($star > 1) {
$t = $star / count($revData);
return number_format($t, 1, '.', '');
}
return 'N/A';
}
}
Model > BranchManager.php
class BranchManager extends Model
{
use SoftDeletes;
public $table = 'branch_managers';
protected $guarded = [];
public function branches(){
return $this->belongsToMany(Manager::class);
}
}
First I had admin panel created where was easy to query all data from database. Because admin can see all data and limit data with Gate access. But now is harder to create queries for business type of users (Owner, Manager, Employee). I think I've done whole relations with tables wrongly and now im struggling with that.
Business branch controller where I tried to query data only for business owner and manager who owns branches and who have assigned for branches.
Controller > BranchController.php
public function index()
{
if (!Gate::denies('branch_access')) {
$branch = DB::table('branch')
->join('business', 'branch.business_id', '=', 'business.id')
->select('branch.id', 'branch.name', 'branch.address', 'branch.for_who', 'branch.start_time', 'branch.end_time', 'branch.icon', 'branch.is_featured', 'branch.status')
->where('business.owner_id', Auth::id())
->get();
} elseif (!Gate::denies('branch_manager_access')) {
$branch = DB::table('branch')
->join('business', 'branch.business_id', '=', 'business.id')
->join('branch_managers', 'branch.id', '=', 'branch_id')
->select('branch.id', 'branch.name', 'branch.address', 'branch.for_who', 'branch.start_time', 'branch.end_time', 'branch.icon', 'branch.is_featured', 'branch.status')
->where('branch_managers.user_id', Auth::id())
->get();
} else {
abort_if(true, Response::HTTP_FORBIDDEN, '403 Forbidden');
}
return view('salon.branch.index', compact('branch'));
}
Admin - Controller > BranchController.php
public function index()
{
if (!Gate::denies('branch_access')) {
$branch = branch::all();
} else {
abort_if(true, Response::HTTP_FORBIDDEN, '403 Forbidden');
}
return view('admin.branch.index', compact('branch'));
}
My SaaS project >
What I'm trying to do is that admin can view in his panel all infos etc... and Business panel Owner can see all his branches and Manager can see all assigned branches only.
I've also thinking start all over and with fresh head with good snippets.. I've checked other people repositories. But every saas is different way and more confusing. Multi-tenant was first idea, but I dont need subdomain system, just panel for admins and Vendors (Businesses) and End User (User who's buyng Vendor services / goods).
