Perform Advanced Search - Searching Through Child Relations

Question

Hello I am trying to build a search in my controller. These are my models:

Invoice.php

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Invoice extends Model
{
    use SoftDeletes;

    protected $dates = ['deleted_at'];

    protected $table = 'invoices';

    public function customerDetails() {
        return $this->belongsTo('App\Models\CustomerDetails', 'customer_id');
    }

    public function tickets() {
        return $this->hasMany('App\Models\Ticket', 'invoice_id');
    }


}

CustomerDetails.php

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class CustomerDetails extends Model {

    protected $table = 'customer_details';

    public function user()
    {
        return $this->belongsTo('App\Models\User');
    }

    public function invoices() {
        return $this->hasMany('App\Models\Invoices', 'customer_id');
    }
}

Ticket.php

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Ticket extends Model {

    protected $table = 'tickets';

}

I have a single search field where the user is allowed to enter a single value:

The allowed search parameters are: Invoice ID, Customer ID, First Name, Last Name, Email, Phone, Reference ID, Purchase Date

Now what this is supposed to do is go to the invoice table and check if the value entered matches the invoice_id or if the value entered matches the customer_id or it matches customer_details->first_name or customer_details->last_name or customer_details->email or customer_details->phone or reference_id or created_at

This is what i tried to do in my controller:

/**
 * Display a listing of the resource.
 *
 * @return Response
 */
public function index()
{
    $filter = Input::get('filter');
    $invoices = "";

    if (strlen($filter) > 0)
    {
        $invoices = Invoice::where('id', '=', $filter)
            ->orWhere(function ($query) use ($filter) {
                $query->whereHas('customerDetails', function ($q) use ($filter)  {
                    $q->where('id', '=', $filter)
                    ->orWhere('first_name', '=', $filter)
                    ->orWhere('last_name', '=', $filter);
                });
            })
            ->get();
    }
    else
    {
        $invoices = Invoice::with('customerDetails')
            ->whereRaw('created_at >= CURRENT_DATE')
            ->whereRaw('created_at < CURRENT_DATE + INTERVAL 1 DAY')
            ->get();
    }

    return [
        'invoices' => $invoices
    ];
}

In my above code i haven't added the relation for tickets yet i was just testing against customer_details first.

This is the query laravel generates:

select * from `invoices` where `invoices`.`deleted_at` is null and `id` = 'Phil' or ((select count(*) from `customer_details` where `invoices`.`customer_id` = `customer_details`.`id` and `id` = 'Phil' or `first_name` = 'Phil' or `last_name` = 'Phil') >= 1)

Based on the data returned i can already tell that that query is wrong because even when the name i entered doesn't exist it still returns some rows.

I would like to know what i am doing wrong and how to fix it and if this way is fast and efficient.

user3718908 user3718908 · Accepted Answer · 2015-08-14T20:58:34

Well i thought through it and finally came up with this, i was trying to figure out how to do it without sing joins but looks like that can't be done so had to do it this way instead:

$invoices = Invoice::select('invoices.*')
    ->leftJoin('customer_details', 'customer_details.id', '=', 'invoices.customer_id')
    ->where('invoices.id', '=', $filter)
    ->orWhere('invoices.reference_code', '=', $filter)
    ->orWhereRaw("DATE_FORMAT(invoices.created_at, '%Y-%m-%d') = ?", array($filter))
    ->orWhere('customer_details.id', '=', $filter)
    ->orWhere('customer_details.first_name', '=', $filter)
    ->orWhere('customer_details.last_name', '=', $filter)
    ->orWhere('customer_details.email', '=', $filter)
    ->orWhere('customer_details.phone', '=', $filter)
    ->with('customerDetails')
    ->get();

Perform Advanced Search - Searching Through Child Relations

1 Answers