Eloquent query [solved]

I have 3 tables

cards card_users users

Cards model class
    public function cardUser()
    {
        return $this->belongsTo(CardUser::class);
    }

CardUser model class
    public function user()
    {
        return $this->belongsTo(User::class);
    }

In Card controller and I need make a filter by typing an email and search cards by user accross CardUser model. CardUser model contains the user profile, the email is in users table, and this is related in CardUser table.

Class CardController extends Controller {
        $cards = Card::query();
        if (!empty($search) and strlen($search) >= 3 ) {
        }
        $cards = $cards->paginate(5)->withQueryString();
...
}

How can add a email filter using query builder?.

automica

2 years ago

Level 54

@bashman

$cards = Card::query();
if (!empty($search) and strlen($search) >= 3) {
    $cards->whereHas('user', function (Builder $query) use ($search) {
        $query->where('email', 'like', '%' . $search . '%');
    });
}
$cards = $cards->paginate(5)->withQueryString();

bashman

2 years ago

Best Answer

Level 3

Searching some more I found the way.

            if (!empty($search) and strlen($search) >= 3 ) {

                $cards->whereHas('cardUser.user', function ($query) use ($search) {
                    $query->where('email', 'like', "%$search%");
                });
            }

automica

2 years ago

Level 54

@bashman you shouldnt need to define a model for your join table.

if your CardUser model provides a card_id and user_id then your relationship between user and card is

User hasMany Card
Card belongsTo User

you should define the relationships on Card and User model and remove the one on CardUser.

Doing that would mean the following would resolve your issue.

$cards = Card::query();
if (!empty($search) and strlen($search) >= 3) {
    $cards->whereHas('user', function (Builder $query) use ($search) {
        $query->where('email', 'like', '%' . $search . '%');
    });
}
$cards = $cards->paginate(5)->withQueryString();

if you are querying CardUser directly as you have additional columns, then you should access this data via ->withPivot to get pivot data.

https://laravel.com/docs/10.x/eloquent-relationships#retrieving-intermediate-table-columns

1 like

Please or to participate in this conversation.