O P
O P

Reputation: 2365

Check if row exists, Laravel

I have the following db structure:

items:
id, name, user_id

users table:
id, name

user_favorites table:
id, user_id, item_id

On my items permalink pages, I have an 'Add to favorites' button which inserts a new row into user_favorites

I want to be able to replace it for a 'Remove from favorites' button if the user already has it in their favorites.

I can't figure out the logic behind this - do I need to check if a row exists in user_favorites that has the current user's id and the permalink item id? This did not work for me:

if (Auth::user()->id) {
    if (!is_null(DB::table('user_favorites')->where('user_id', '=', Auth::user()->id)->where('item_id', '=', $item->id)->first())) {
        // remove from favorites button will show
    }
}

Upvotes: 21

Views: 60820

Answers (4)

Amit Gupta
Amit Gupta

Reputation: 17688

The simplest way to do is to use toggle() method of many-to-many relationship.

e.g.

$user->roles()->toggle([1, 2, 3]);

The many-to-many relationship also provides a toggle method which "toggles" the attachment status of the given IDs. If the given ID is currently attached, it will be detached. Likewise, if it is currently detached, it will be attached

It also returns an array which tells you if the ID is attached or detached in DB.

Upvotes: 1

lijinma
lijinma

Reputation: 2942

I advise you to use exists() or count() to check, not use first().

The fastest way:

$result = DB::table('user_favorites')
    ->where('user_id', '=', Auth::user()->id)
    ->where('item_id', '=', $item->id)
    ->exists();

Or:

$result = DB::table('user_favorites')
    ->where('user_id', '=', Auth::user()->id)
    ->where('item_id', '=', $item->id)
    ->count();

SQL:

select count(*) as aggregate from `user_favorites` where *** limit 1

The faster way: only select id

$result = DB::table('user_favorites')
    ->where('user_id', '=', Auth::user()->id)
    ->where('item_id', '=', $item->id)
    ->first(['id']);

SQL:

select id from `user_favorites` where *** limit 1

The normal way:

$result = DB::table('user_favorites')
    ->where('user_id', '=', Auth::user()->id)
    ->where('item_id', '=', $item->id)
    ->first();

SQL:

select * from `user_favorites` where *** limit 1

Upvotes: 22

Ajay Kumar Ganesh
Ajay Kumar Ganesh

Reputation: 1852

Let User_favorite be a model that accesses your user_favorites table

$result = User_favorite::where('user_id',Auth::getUser()->id)
                         ->where('item_id',$item->id)
                         ->first();

if (is_null($result)) {
// Not favorited - add new
    User_favorite::create(['user_id'=>Auth::getUser()->id,'item_id'=>$item->id]);
} else {
// Already favorited - delete the existing
    $result->delete();
}

Upvotes: 6

Simone
Simone

Reputation: 21282

You may want something like this:

$user_favorites = DB::table('user_favorites')
    ->where('user_id', '=', Auth::user()->id)
    ->where('item_id', '=', $item->id)
    ->first();

if (is_null($user_favorites)) {
    // It does not exist - add to favorites button will show
} else {
    // It exists - remove from favorites button will show
}

Upvotes: 37

Related Questions