John Davidson

Message:

I am trying to collect related data of customers from three table view them as one table on browser, and to search dynamically with the value of severate columns from three tables.

Here is the code:

public function customersList(Request $request)

    {

        $info = [];

        $data = [];



        // if (!empty($request->value)) :

        //     $info = DB::table('customers')

        //         ->join('phones', 'customers.id', '=', 'phones.customer_id',)

        //         ->join('emails', 'customers.id', '=', 'emails.customer_id',)

        //         ->select('customers.id', 'customers.name', 'emails.email', 'emails.customer_id', 'phones.phone', 'phones.customer_id')

        //         ->where('customers.name', 'like', '%' . $request->value . '%')

        //         ->orWhere('emails.email', 'like', '%' . $request->value . '%')

        //         ->orWhere('phones.phone', 'like', '%' . $request->value . '%')

        //         ->get();

        // else :

        //     $info = DB::table('customers')

        //         ->join('phones', 'customers.id', '=', 'phones.customer_id',)

        //         ->join('emails', 'customers.id', '=', 'emails.customer_id',)

        //         ->select('customers.id', 'customers.name', 'emails.email', 'emails.customer_id', 'phones.phone', 'phones.customer_id')

        //         ->get();

        // endif;



        // $i = 0;

        // foreach ($info as $item) :

        //     $item = (array)$item;

        //     if (isset($data[$i]['id']) && $data[$i]['id'] != $item['id']) :

        //         $data[$i]['id'] = $item['id'];

        //         $data[$i]['name'] = $item['name'];

        //     endif;

        //     $i++;

        // endforeach;



        if (!empty($request->value)) :

            $customers = Customer::where('name', 'like', '%' . $request->value . '%')->get();

        else :

            $customers = Customer::all();

        endif;





        foreach ($customers as $customer) :

            if ((!empty($request->all()))) :

                $emails[] = Customer::find($customer['id'])->emails()->where('email', 'like', '%' . $request->value . '%')->get();

            else :

                $emails[] = Customer::find($customer['id'])->emails;

            endif;

            if (!empty($request->all())) :

                $phones[] = Customer::find($customer['id'])->phones()->where('phone', 'like', '%' . $request->value . '%')->get();

            else :

                $phones[] = Customer::find($customer['id'])->phones;

            endif;

        endforeach;



        foreach ($customers as $customer) :

            $data[$customer['id']]['id'] = $customer['id'];

            $data[$customer['id']]['name'] = $customer['name'];

            $data[$customer['id']]['age'] = $customer['age'];

            $data[$customer['id']]['gender'] = $customer['gender'];

            foreach ($emails as $item) :

                $i = 1;

                foreach ($item as $email) :

                    if ($customer['id'] == $email['customer_id']) :

                        $data[$customer['id']]['email-' . $i] = $email['email'];

                    endif;

                    $i++;

                endforeach;

            endforeach;

            foreach ($phones as $item) :

                $i = 1;

                foreach ($item as $phone) :

                    if ($customer['id'] == $phone['customer_id']) :

                        $data[$customer['id']]['phone-' . $i] = $phone['phone'];

                    endif;

                    $i++;

                endforeach;

            endforeach;

        endforeach;



        return response()->json([

            'status' => 200,

            'data' => $data

        ]);

    } 

Note: Please ignore the comented line. Those are because I tried get output in another way.

The output is:

But the prolem is when I try to search with customers table data like name, live search is ok and data filters perfectly. But search doesn't work for field like phone from phones, email from emails that is other two tables.

What is wrong in my code? I don't know eloquent relationship.
Can anyone help with my code?

---

My Answer:
In your code, you are trying to search for emails and phones based on the customer id, but you are not correctly using the relationships defined in your models.

Assuming you have defined relationships in your Customer, Email, and Phone models, you can simplify your code by using eager loading to load the related emails and phones along with the customers.

Here's an updated version of your code using eager loading:

php
public function customersList(Request $request)
{
$data = [];

$customers = Customer::with('emails', 'phones');

if (!empty($request->value)) {
$customers->where('name', 'like', '%' . $request->value . '%');
}

$customers = $customers->get();

foreach ($customers as $customer) {
$data[$customer->id]['id'] = $customer->id;
$data[$customer->id]['name'] = $customer->name;
$data[$customer->id]['age'] = $customer->age;
$data[$customer->id]['gender'] = $customer->gender;

foreach ($customer->emails as $i => $email) {
$data[$customer->id]['email-' . ($i + 1)] = $email->email;
}

foreach ($customer->phones as $i => $phone) {
$data[$customer->id]['phone-' . ($i + 1)] = $phone->phone;
}
}

return response()->json([
'status' => 200,
'data' => $data
]);
}


In this code, we are eager loading the emails and phones related to each customer using the `with` method. This will load the related emails and phones along with the customers in a more efficient way.

Make sure you have defined the relationships in your models like this:

In the Customer model:
php
public function emails()
{
return $this->hasMany(Email::class);
}

public function phones()
{
return $this->hasMany(Phone::class);
}


In the Email model:
php
public function customer()
{
return $this->belongsTo(Customer::class);
}


In the Phone model:
php
public function customer()
{
return $this->belongsTo(Customer::class);
}


By using relationships and eager loading, you can simplify your code and make it more efficient. This should help you search for emails and phones along with customer data in a dynamic way.