Mam takie tabele:
CREATE TABLE `request_hr` (
`c_id` INT(11) NOT NULL,
`request_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`c_id`, `request_id`),
INDEX `request_hr_request_id_foreign` (`request_id`),
CONSTRAINT `request_hr_request_id_foreign` FOREIGN KEY (`request_id`) REFERENCES `employment_requests` (`id`),
CONSTRAINT `request_hr_user_id_foreign` FOREIGN KEY (`c_id`) REFERENCES `users` (`c_id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
CREATE TABLE `employment_requests` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`type` ENUM('onboarding','offboarding','ChangeOfPosition') NOT NULL DEFAULT 'onboarding' COLLATE 'utf8_unicode_ci',
`status` INT(11) NOT NULL,
`first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`c_id` INT(10) NULL DEFAULT NULL,
`job_title` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
`team` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
`localisation` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`room` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`manager_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`created_by` INT(10) UNSIGNED NOT NULL,
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
`deleted_at` TIMESTAMP NULL DEFAULT NULL,
`start` DATE NULL DEFAULT NULL,
`stop` DATE NULL DEFAULT NULL,
`contract_type` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`vat` TINYINT(1) NOT NULL,
`company1` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
`per_hour1` TINYINT(1) NULL DEFAULT NULL,
`pretax1` TEXT NULL COLLATE 'utf8_general_ci',
`net1` TEXT NULL COLLATE 'utf8_general_ci',
`beginning1` DATE NULL DEFAULT NULL,
`end1` DATE NULL DEFAULT NULL,
`company2` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`per_hour2` TINYINT(1) NULL DEFAULT NULL,
`pretax2` TEXT NULL COLLATE 'utf8_unicode_ci',
`net2` TEXT NULL COLLATE 'utf8_unicode_ci',
`beginning2` DATETIME NULL DEFAULT NULL,
`end2` DATETIME NULL DEFAULT NULL,
`is_student` TINYINT(1) NOT NULL DEFAULT '0',
`language` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`range` LONGTEXT NOT NULL COLLATE 'utf8_unicode_ci',
`business_unit_id` INT(11) NOT NULL,
`offboarding_conversation_date` DATE NULL DEFAULT NULL,
`documents_mutual` TINYINT(1) NOT NULL,
`documents_period` TINYINT(1) NOT NULL,
`documents_additional_info` TEXT NULL COLLATE 'utf8_unicode_ci',
`emails_to_inform` TEXT NULL COLLATE 'utf8_unicode_ci',
`autoresponder_text` TEXT NULL COLLATE 'utf8_unicode_ci',
`additional_info` TEXT NULL COLLATE 'utf8_unicode_ci',
`note_taken_access` TEXT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`id`),
INDEX `employment_requests_manager_id_foreign` (`manager_id`),
INDEX `employment_requests_created_by_foreign` (`created_by`),
CONSTRAINT `employment_requests_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
CONSTRAINT `employment_requests_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`email` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`username` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`notes` VARCHAR(1024) NOT NULL COLLATE 'utf8_unicode_ci',
`uprawnienia` TEXT NULL COLLATE 'utf8_unicode_ci',
`password` VARCHAR(60) NOT NULL COLLATE 'utf8_unicode_ci',
`remember_token` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`created_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` TIMESTAMP NULL DEFAULT NULL,
`c_id` INT(11) NOT NULL,
`budget_mgr_id` INT(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `users_c_id_IDX` (`c_id`) USING BTREE,
INDEX `users_id_IDX` (`id`) USING BTREE,
INDEX `budget_mgr_id` (`budget_mgr_id`),
FULLTEXT INDEX `users_email_IDX` (`email`),
FULLTEXT INDEX `users_username_IDX` (`username`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
i takie model:
<?php
class EmploymentRequest extends Model
{
use SoftDeletes;
const BY_MUTUAL_AGREEMENT = 'Za porozumieniem stron';
const BY_PERIOD_OF_NOTICE_OBSERVED = 'Z zachowaniem okresu wypowiedzenia';
const AUTORESPONDER_MIN_STRING_LENGTH = 5;
const OFFBOARDING = 'offboarding';
const ONBOARDING = 'onboarding';
const CHANGE_OF_POSITION = 'changeOfPosition';
const NEW = 0;
const SUPPORTED = 1;
const DONE = 2;
const C_ID = 'c_id';
const DELETED_AT = 'deleted_at';
const FURNITURE = 'furniture';
const SOFTWARE = 'software';
const HARDWARE = 'hardware';
const CHANGE_OF_POSITION_MAPPED = 'Zmiana roli';
const EMPLOYMENT_TYPES = [
self::FURNITURE,
self::SOFTWARE,
self::HARDWARE
];
const BOARDING_TYPES = [
self::ONBOARDING,
self::OFFBOARDING,
self::CHANGE_OF_POSITION
];
const BOARDING_TYPES_MAPPING = [
self::CHANGE_OF_POSITION => self::CHANGE_OF_POSITION_MAPPED
];
public $timestamps = true;
protected $fillable = [
'first_name',
'last_name',
self::C_ID,
'job_title',
'start',
'manager_id',
'team',
'room',
'localisation',
'created_by' ,
'contract_type',
'status',
'vat',
'company1',
'pretax1',
'net1',
'per_hour1',
'beginning1',
'end1',
'company2',
'pretax2',
'net2',
'per_hour2',
'beginning2',
'end2',
'is_student',
'language',
'range',
'business_unit_id',
'stop',
'type',
'offboarding_conversation_date',
'documents_mutual',
'documents_period',
'documents_additional_info',
'emails_to_inform',
'autoresponder_text',
'additional_info',
'note_taken_access'
];
public function supporters()
{
return $this->belongsToMany('App\User', 'request_supporter', 'request_id', 'user_id');
}
public function hrsTakenAccess(): BelongsToMany
{
return $this->belongsToMany(User::class, 'request_hr', 'request_id', 'c_id');
}
}
Chciałbym z tabeli request_hr pobrać wszystkie c_id dla danego requestu tak więc mam relację belongsToMany w modelu EmploymentRequest która nazywa się hrsTakenAccess ale otrzymuję zapytanie z laravela co wygląda tak:
SELECT `users`.*, `request_hr`.`request_id` AS `pivot_request_id`, `request_hr`.`c_id` AS `pivot_c_id` FROM `users` INNER JOIN `request_hr` ON `users`.`id` = `request_hr`.`c_id` WHERE `request_hr`.`request_id` = ? AND `users`.`deleted_at` IS NULL
co jest złe bo zamiast `users`.`id` chce otrzymać `users`.`c_id` co robię nie tak ?