Course lectures : 1 of 1

Filter records by lookup table using Eloquent

Last updated: 9th March 2016

laravel laravel eloquent eloquent orm object relational mapper php orm eloquent

Today we'll have a look at how we can use Laravel's Illuminate\Database\Eloquent\Builder to perform a select query, which fetches records based on the result involving main and lookup table.

For this example we will use two simple models Order and User.

I assume you're using the default User model with the associated migration that come with Laravel, therefore we only need to add an Order model represented by the following, very simplified migration

class CreateOrdersTable extends Migration
{
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {

            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->timestamps();

            $table->foreign('user_id')
                  ->references('id')->on('users')
                  ->onDelete('cascade');

        });
    }

    public function down()
    {
        Schema::drop('orders');
    }
}

Our models will have a One-to-many relationship

class Order extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}
class User extends Authenticatable
{
    public function orders()
    {
        return $this->hasMany(Order::class);
    }
}

With all that set up, add some records to both tables and in the controller of your choice add a method that will display records based on some request parameter. I'm going to use the q query string to filter my records with the query builder and start with getting records that match the order id (it should technically be just one record).

public function index()
{
    return view('orders')->with('orders', $this->orders());
}

private function orders()
{
    $q = request('q');

    if (empty($q)) {
        return Order::all();
    }

    return Order::where('id', $q)->get();
}

Now let's see how we can filter results further by adding the associated user's name to the query

private function orders()
{
    $q = request('q');

    if (empty($q)) {
        return Order::all();
    }

    return Order::with('user')
            ->where('id', $q)
            ->orWhereHas('user', function($query) use ($q) {
                $query->where("name", "like", "%$q%");
            })->get();
}

The above will produce the following sql statement, which is exactly what we would expect

select * from `orders`
where `id` = ?
or exists (
    select *
    from `users`
    where `orders`.`user_id` = `users`.`id`
    and `name` like ?
)

Now we also would like to filter our results by user's email address and id. Let's first try and add the orWhere method to our query

private function orders()
{
    $q = request('q');

    if (empty($q)) {
        return Order::all();
    }

    return Order::with('user')
            ->where('id', $q)
            ->orWhereHas('user', function($query) use ($q) {
                $query->where("name", "like", "%$q%");
                $query->orWhere("email", "like", "%$q%");
                $query->orWhere("id", $q);
            })->get();
}

If you run this query, you probably notice that the results aren't quite what you'd expect. Let's have a look at the sql statement produced by the above

select * from `orders`
where `id` = ?
or exists (
    select *
    from `users`
    where `orders`.`user_id` = `users`.`id`
    and `name` like ?
    or `email` like ?
    or `id` = ?
)

What we need to do in order to make it work, is to wrap our user related conditions within its own where block, which will add the necessary brackets around the series of or conditions

private function orders()
{
    $q = request('q');

    if (empty($q)) {
        return Order::all();
    }

    return Order::with('user')
            ->where('id', $q)
            ->orWhereHas('user', function($query) use ($q) {
                $query->where(function($query) use ($q) {
                    $query->where("name", "like", "%$q%");
                    $query->orWhere("email", "like", "%$q%");
                    $query->orWhere("id", $q);
                });
            })->get();
}

Now our statement looks as follow and should return the correct results.

select * from `orders`
where `id` = ?
or exists (
    select *
    from `users`
    where `orders`.`user_id` = `users`.`id`
    and (
        `name` like ?
        or `email` like ?
        or `id` = ?
    )
)

COMMENTS

If you'd like to include code example please wrap your code within <pre><code> tags.


<pre>
<code>
function fullName(firstName, lastName)
{
    return firstName + ' ' + lastName;
}
</code>
</pre>