In my laravel vue application I have the following datatable with some filters in a vue component(department-user-list.vue).
<template>
<div>
<cs-card
:cardButton="false"
:title="`Team members`"
>
<template slot="header-action">
<div class="inline-block mr-4" direction-from="top">
<open-overlay identifier="corporateInviteEmployeeToDepartmentModal">
<cs-button size="small" variant="secondary">
Invite team member
</cs-button>
</open-overlay>
</div>
<div class="inline-block" direction-from="top">
<cs-button @click="redirectToAssignation" size="small">
Assign team member
</cs-button>
</div>
</template>
<Datatable
v-model="selectedForAction"
:data="dataset"
:headers="headers"
:is-loading="isLoading"
:has-action-bar-column="true"
:key-id="`id`"
:search-panel="{placeholder: 'Search team member...'}"
@change="handlePaginationChange"
@paginate="loadDepartmentEmployees"
>
<!--Filter Slot-->
<template v-slot:filters>
<!--Nationality filter-->
<div class="w-2/12 pl-4 h-auto">
<cs-multiple-select
v-model="nationalitiesFilter"
:options="countries"
key-id="id"
label="name"
name="nationality"
placeholder="Nationality"
>
</cs-multiple-select>
</div>
<!--Certificate Status filter-->
<div class="w-6/12 pl-4 h-auto">
<cs-multiple-select
v-model="certificateStatusFilter"
:options="certificateStatus"
name="certificateStatusFilter"
placeholder="Qualification status"
@input="loadDepartmentEmployees"
/>
</div>
<!--Matrix Status filter-->
<div class="w-4/12 pl-4 h-auto">
<cs-multiple-select
v-model="matrixStatusFilter"
:options="matrixStatus"
key-id="value"
label="label"
name="matrixStatusFilter"
placeholder="Matrix status"
@input="loadDepartmentEmployees"
/>
</div>
<!--Employee Type filter-->
<div class="w-4/12 pl-4 h-auto">
<cs-multiple-select
v-model="selectedEmployeeTypes"
:options="employeeType"
key-id="value"
label="label"
name="selectedEmployeeTypes"
placeholder="Employee type"
@input="loadDepartmentEmployees"
>
</cs-multiple-select>
</div>
</template>
<!--Table Header-->
<template v-slot:header.country="{ header}">
<span class="material-icons-outlined">language</span>
</template>
<!--Table Body-->
<template v-slot:item.name="{ item }">
<div class="flex items-center cursor-pointer">
<div class="rounded-full w-8 h-8 mr-4 overflow-hidden">
<img :src="item.profile_image[0].url" alt=""
class="w-full h-full object-cover">
</div>
<a :href="employeeDetailRoute(item)">
<span class="text-certstyle-titles font-bold leading-loose">{{ item.name }}</span>
</a>
<span
v-if="item.is_subcontractor && item.company_name"
:title="item.company_name"
class="text-certstyle-text-light bg-certstyle-background flex font-semibold cursor-help text-xs rounded mx-2 py-1 px-2"
>
{{ item.company_name.substring(0,10) }}
<span v-if="item.company_name.length > 10">...</span>
</span>
</div>
</template>
<template v-slot:item.job_title="{ item }">
{{ item.current_jobtitle || item.department_jobtitle }}
</template>
<template v-slot:item.country="{ item }">
<span v-if="item.country" class="font-normal">
<country-flag width="w-5" :country-code="item.country.country_code"></country-flag>
</span>
</template>
<template v-slot:item.certificate_status="{ item }">
<div class="status--summary--component inline-block mr-2 relative"
@click="getValidityStatus(item.certificate_matrix) !== 'all valid' &&
getValidityStatus(item.certificate_matrix) !== '-'
? openCertificatesModal(item) : null
">
<label :class="getValidityStatusClass(item.certificate_matrix)" class="badge">
{{ getValidityStatus(item.certificate_matrix) }}
</label>
</div>
</template>
<template v-slot:item.matrix_status="{ item }">
<div class="status--summary--component inline-block mr-2 relative"
@click="getMatrixStatus(item.certificate_matrix) === 'non compliant'
? openCertificatesModal(item)
: null
">
<label :class="getMatrixStatusClass(item.certificate_matrix)" class="badge">
{{ getMatrixStatus(item.certificate_matrix) }}
</label>
</div>
</template>
<template v-slot:actionItems="slotProps">
<DatatableActionbarItem
:slot-props="slotProps"
identifier="removeConfirmationModal"
label="Remove"
variant="danger"
/>
</template>
<template v-slot:rowActionItems="slotProps">
<DatatableActionbarItem
icon=""
label="Contact details"
@click.native="openContactDetailsModal(slotProps.item)"
:slot-props="slotProps"
/>
</template>
</Datatable>
<modal-md
v-if="selectedEmployee !== null"
:options="{ useDefaultContentStyling: false }"
:identifier="`statusSummaryComponent`">
<template slot="modal_title">
Qualifications
<span v-if="selectedEmployee !== null && selectedEmployee !== undefined">
{{ selectedEmployee.name }}
</span>
</template>
<div class="bg-white" slot="modal_content">
<div
class="flex items-center justify-between text-certstyle-text-light bg-white border-certstyle-border border-b text-sm py-2 px-10">
<cs-dashboard-table-header-item-unstyled :item="`statusSummaryCertificateTitle`">
<p>Qualification</p>
</cs-dashboard-table-header-item-unstyled>
<cs-dashboard-table-header-item-unstyled :item="`statusSummaryCertificateStatus`">
<p>Validity Status</p>
</cs-dashboard-table-header-item-unstyled>
<cs-dashboard-table-header-item-unstyled :item="`statusSummaryMatrixStatus`">
<p>Matrix Status</p>
</cs-dashboard-table-header-item-unstyled>
</div>
<!--Certificates-->
<div class="text-certstyle-titles">
<div v-for="certificate in selectedEmployee.certificate_matrix"
v-if="selectedEmployee.certificate_matrix.length > 0"
class="table--row flex items-center justify-between border-certstyle-border last:border-b-0 border-b px-10 py-4">
<!-- Title -->
<cs-dashboard-table-item-unstyled
class="w-1/2"
:item="`statusSummaryCertificateTitle`">
<p :title="certificate.title" class=" ">
{{ certificate.title | limitString(30, '...') }}
</p>
</cs-dashboard-table-item-unstyled>
<cs-dashboard-table-item-unstyled class="w-32" :item="`statusSummaryCertificateStatus`">
<div class="flex items-center justify-start w-full">
<!--Expired styling-->
<label v-if="certificate.matrix_status === 0"
class="badge badge-danger">
-
</label>
<!--Expires styling-->
<label v-if="certificate.matrix_status && certificate.expired === 1"
class="badge badge-danger">
expired
</label>
<!--Expires styling-->
<label v-if="certificate.matrix_status && certificate.expire_soon === 1"
class="badge badge-danger">
expires soon
</label>
<!--Active styling-->
<label
v-if="certificate.matrix_status && certificate.expire_soon === 0 && certificate.expired === 0"
class="badge badge-success">
active
</label>
</div>
</cs-dashboard-table-item-unstyled>
<cs-dashboard-table-item-unstyled
class="w-32"
:item="`statusSummaryMatrixStatus`">
<!--Active styling-->
<label v-if="certificate.matrix_status"
class="badge badge-success">
compliant
</label>
<label v-else
class="badge badge-danger">
non-compliant
</label>
</cs-dashboard-table-item-unstyled>
</div>
<div v-else
class="table--row flex items-center justify-between border-certstyle-border last:border-b-0 border-b px-10 py-4">
<cs-dashboard-table-item-unstyled
class="w-1/2"
:item="`statusSummaryCertificateTitle`">
No certificates found
</cs-dashboard-table-item-unstyled>
<cs-dashboard-table-item-unstyled class="w-32"
:item="`statusSummaryCertificateStatus`">
</cs-dashboard-table-item-unstyled>
<cs-dashboard-table-item-unstyled
class="w-32"
:item="`statusSummaryMatrixStatus`">
</cs-dashboard-table-item-unstyled>
</div>
</div>
</div>
</modal-md>
</cs-card>
<contact-details
v-if="userToShowContactDetails"
:user="userToShowContactDetails"
/>
<add-employee-modal
:countries="countries"
:identifier="`addEmployeeModal`"
:invite-modal-title="`Invite to department`"
:suggestion-url="userSuggestionApiURL"
:title="`Assign to ${department ? department.name: ''}`"
@inviteEmployees="handleInvitedEmployees"
@selectEmployee="addEmployeeToDepartment"
@removeEmployee="handleRemoveEmployee"
/>
<cs-confirmation-modal
v-if="checkRole(['admin', 'planner'])"
@proceed="handleRemoveEmployee"
identifier="removeConfirmationModal">
<div class="text-certstyle-titles font-normal" slot="content">
Removing employees from this department can not be undo.
</div>
<div slot="cancelButton"
class="cursor-pointer hover:bg-certstyle-background border border-certstyle-border rounded px-6 py-2 text-certstyle-text-light mr-4">
Cancel
</div>
<cs-button slot="proceedButton">
Remove
</cs-button>
</cs-confirmation-modal>
</div>
</template>
This list and the filters works fine but when I try to search a user by typing their names, it kept giving me following error,
Duplicate keys detected: ‘1025’. This may cause an update error.
found in
—> at resources/js/components/reusable/datatable/Datatable.vue at resources/js/components/reusable/csCard.vue at resources/js/components/dashboard/communities/department/one-departments/department-user -list.vue
But when I try to filter the list by some other param, they get filtered without any error.
I’m struggling to find what I’m doing wrong here….
UPDATE
following is my non-filtered json data for a single user
{
"id": 1038,
"unique_id": "0a3938c1-07d5-3884-9df0-a8fe3201a3e5",
"first_name": "Mango",
"last_name": "F1",
"job_title": null,
"email": "mangoF1@gmail.com",
"phone_number": null,
"phone_country_calling_code": null,
"country_id": null,
"company_name": null,
"is_subcontractor": 0,
"current_jobtitle": "Deck Foreman",
"department_jobtitle": "Rigging Engineers",
"language": "en",
"email_verified_at": "2022-04-12T12:47:55.000000Z",
"gender": null,
"state": null,
"postal_code": null,
"house_number": null,
"street": null,
"city": null,
"date_of_birth": null,
"city_of_birth": null,
"emergency_number": null,
"opt_in": null,
"created_at": "2022-04-12T12:46:34.000000Z",
"updated_at": "2022-04-12T12:47:55.000000Z",
"emergency_number_country_calling_code": null,
"deleted_at": null,
"has_ip_restriction": 0,
"address": null,
"latitude": null,
"longitude": null,
"place_id": null,
"bouwpas_worker_id": null,
"bouwpas_certificate_id": null,
"certificate_matrix": [
{
"id": 463,
"is_active": 1,
"title": "Further Offshore Emergency Response Team Member [Training and Competence Assessment]",
"description": null,
"has_validity": 1,
"is_valid_forever": 0,
"validity": 2,
"validity_unit": "years",
"accreditation_id": 6,
"created_at": "2021-01-13T22:31:44.000000Z",
"updated_at": "2021-01-13T22:31:44.000000Z",
"deleted_at": null,
"user_id": 1038,
"certificate_id": 463,
"expire_soon": 0,
"expired": 0,
"mandatory": 0,
"matrix_status": 1
},
{
"id": 443,
"is_active": 1,
"title": "First Aid & CPR - USCG",
"description": null,
"has_validity": 0,
"is_valid_forever": 0,
"validity": null,
"validity_unit": "years",
"accreditation_id": 14,
"created_at": "2021-01-13T22:31:43.000000Z",
"updated_at": "2021-01-13T22:31:43.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 0,
"matrix_status": 0
},
{
"id": 1547,
"is_active": 1,
"title": "VHF/HF Long Range Radio Operators Certificate",
"description": null,
"has_validity": 0,
"is_valid_forever": 0,
"validity": null,
"validity_unit": "years",
"accreditation_id": 8,
"created_at": "2021-01-13T22:31:54.000000Z",
"updated_at": "2021-01-13T22:31:54.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 0,
"matrix_status": 0
},
{
"id": 729,
"is_active": 1,
"title": "LOLER Competent Person (Competence Re-Assessment)",
"description": null,
"has_validity": 1,
"is_valid_forever": 0,
"validity": 2,
"validity_unit": "years",
"accreditation_id": null,
"created_at": "2021-01-13T22:31:46.000000Z",
"updated_at": "2021-01-13T22:31:46.000000Z",
"deleted_at": null,
"user_id": 1038,
"certificate_id": 729,
"expire_soon": 0,
"expired": 0,
"mandatory": 0,
"matrix_status": 1
},
{
"id": 213,
"is_active": 1,
"title": "CISRS NVQ/SVQ level 3 is assessed and verified by NCC",
"description": null,
"has_validity": 0,
"is_valid_forever": 0,
"validity": null,
"validity_unit": "years",
"accreditation_id": 9,
"created_at": "2021-01-13T22:31:42.000000Z",
"updated_at": "2021-01-13T22:31:42.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 0,
"matrix_status": 0
},
{
"id": 951,
"is_active": 1,
"title": "jira master",
"description": null,
"has_validity": 1,
"is_valid_forever": 0,
"validity": 4,
"validity_unit": "years",
"accreditation_id": 7,
"created_at": "2021-01-13T22:31:48.000000Z",
"updated_at": "2021-01-13T22:31:48.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 0,
"matrix_status": 0
},
{
"id": 348,
"is_active": 1,
"title": "Doctor",
"description": null,
"has_validity": 0,
"is_valid_forever": 0,
"validity": null,
"validity_unit": "years",
"accreditation_id": null,
"created_at": "2021-01-13T22:31:43.000000Z",
"updated_at": "2021-01-13T22:31:43.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 1,
"matrix_status": 0
},
{
"id": 464,
"is_active": 1,
"title": "JIRA Training",
"description": null,
"has_validity": 1,
"is_valid_forever": 0,
"validity": 4,
"validity_unit": "years",
"accreditation_id": 6,
"created_at": "2021-01-13T22:31:44.000000Z",
"updated_at": "2021-01-13T22:31:44.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 1,
"matrix_status": 0
},
{
"id": 507,
"is_active": 1,
"title": "JIRA Training Refresher (Offshore)",
"description": null,
"has_validity": 1,
"is_valid_forever": 0,
"validity": 2,
"validity_unit": "years",
"accreditation_id": 1,
"created_at": "2021-01-13T22:31:44.000000Z",
"updated_at": "2021-01-13T22:31:44.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 1,
"matrix_status": 0
},
{
"id": 551,
"is_active": 1,
"title": "HLO Helideck Emergency Response Team Leader (HERTL) Training",
"description": null,
"has_validity": 1,
"is_valid_forever": 0,
"validity": 2,
"validity_unit": "years",
"accreditation_id": 6,
"created_at": "2021-01-13T22:31:44.000000Z",
"updated_at": "2021-01-13T22:31:44.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 1,
"matrix_status": 0
},
{
"id": 443,
"is_active": 1,
"title": "First Aid & CPR - USCG",
"description": null,
"has_validity": 0,
"is_valid_forever": 0,
"validity": null,
"validity_unit": "years",
"accreditation_id": 14,
"created_at": "2021-01-13T22:31:43.000000Z",
"updated_at": "2021-01-13T22:31:43.000000Z",
"deleted_at": null,
"user_id": null,
"certificate_id": null,
"expire_soon": null,
"expired": null,
"mandatory": 1,
"matrix_status": 0
}
],
"country_code": null,
"formatted_date_of_birth": "2022-05-20",
"name": "Mango F1",
"company_role": "EMPLOYEE",
"profile_image": [
{
"url": "https://tuktuk.test/storage/281/yeaUdAnyhJalEEdOI4BLBrOMP8NCZDYy2FX1xQe5.png",
"fileName": "yeaUdAnyhJalEEdOI4BLBrOMP8NCZDYy2FX1xQe5.png"
}
],
"inviter_user": "",
"pivot": {
"department_id": 290,
"user_id": 1038,
"id": 342,
"job_title_id": 998,
"created_at": "2022-04-12T12:46:34.000000Z",
"updated_at": "2022-04-12T12:48:12.000000Z"
},
"country": null,
"media": [
{
"id": 281,
"model_type": "App\User",
"model_id": 1038,
"uuid": "a3919544-001a-4a07-88ef-30e0bfae6120",
"collection_name": "profile_image",
"name": "yeaUdAnyhJalEEdOI4BLBrOMP8NCZDYy2FX1xQe5",
"file_name": "yeaUdAnyhJalEEdOI4BLBrOMP8NCZDYy2FX1xQe5.png",
"mime_type": "image/png",
"disk": "public",
"conversions_disk": "public",
"size": 5251,
"manipulations": [
],
"custom_properties": [
],
"responsive_images": [
],
"order_column": 272,
"created_at": "2022-04-12T12:47:55.000000Z",
"updated_at": "2022-04-12T12:47:55.000000Z",
"generated_conversions": [
],
"original_url": "https://tuktuk.test/storage/281/yeaUdAnyhJalEEdOI4BLBrOMP8NCZDYy2FX1xQe5.png",
"preview_url": ""
}
]
},
Following is my controller function,
/**
* Search User for typeahead menus
*
* @param string $locale
* @param Company $company
* @param Request $request
* @return JsonResponse
*/
public function suggestions(string $locale, Company $company, Request $request): jsonResponse
{
$users = User::with(['departments' => function ($q) {
$q->addSelect([
'job_title' => JobTitle::select('title')
->whereColumn('department_user.job_title_id', 'job_titles.id')
])->havingRaw('job_title IS NOT NULL')
->limit(1);
}])->select(['id', 'first_name', 'last_name'])
->has('departments')
->when($request->get('search_text'), function ($query) {
$query->where('first_name', 'like', request('search_text') . '%')
->orWhere('last_name', 'like', request('search_text') . '%')
->orWhereHas('departments.jobTitles', function ($query) {
$query->where('title', 'like', request('search_text') . '%');
});
})
->groupBy('id')
->paginate($request->get('per_page', config('view.ui_component.typeahead_suggestions_limit')));
return response()->json($users);