mercredi 23 octobre 2019

Using join in orWhere clause to search the data from different table as well

Hello I am trying to add a search which will search data from two different table based on multiple columns. I have succeeded in getting results from one table but when I try to join my search with other table it returns no record. Here is my code.

Controller

public function getCustomers() {
    if (request()->ajax()) {
        $term = request()->input('q', '');

        $business_id = request()->session()->get('user.business_id');
        $user_id = request()->session()->get('user.id');

        $contacts = Contact::where('business_id', $business_id);

        $selected_contacts = User::isSelectedContacts($user_id);
        if ($selected_contacts) {
            $contacts->join('user_contact_access AS uca', 'contacts.id', 'uca.contact_id')
                    ->where('uca.user_id', $user_id);
        }
        if (!empty($term)) {
            $contacts->where(function ($query) use ($term) {
                $query->where('name', 'like', '%' . $term . '%')
                        ->orWhere('supplier_business_name', 'like', '%' . $term . '%')
                        ->orWhere('mobile', 'like', '%' . $term . '%')
                        ->orWhere('cars.registration_number', 'like', '%' . $term . '%')
                        ->orWhere('contacts.contact_id', 'like', '%' . $term . '%');
            })->join('cars', 'contacts.id', 'cars.contact_id')
                    ->where('cars.contact_id', 'contacts.id');
        }

        $contacts = $contacts->select(
                        'contacts.id', DB::raw("IF(contacts.contact_id IS NULL OR contacts.contact_id='', name, CONCAT(name, ' (', contacts.contact_id, ')')) AS text"), 'mobile', 'landmark', 'city', 'state', 'pay_term_number', 'pay_term_type'
                )
                // ->onlyCustomers()
                ->get();
        if (count($contacts) <= 0) {
            $contacts = Car::where('contact_id', 'like', '%' . $term . '%')->get();
        }
        return json_encode($contacts);
    }
}

What is it that I am doing wrong here. Thank you in advance



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire