stirredo's avatar

How do I perform this search query in eloquent?

Hello, I am trying to perform a search query with particular tag names in bookmarks for a particular user with this query in eloquent:

SELECT * FROM `bookmarks_tags` WHERE tag_id in (SELECT id FROM `tags` WHERE name in ('new','please','work')) and bookmark_id in (SELECT id from bookmarks where userid = 1)

I have the following models setup with relationships:

##Tag model

class Tag extends Eloquent
{
   public function bookmarks()
    {
        return $this
            ->belongsToMany('Bookmark','bookmarks_tags');
    }
}

##Bookmark model:

class Bookmark extends Eloquent {

    .....

    public function tags()
    {
        return $this->belongsToMany('Tag','bookmarks_tags','bookmark_id','tag_id');
    }


}

##And lastly the user model:

class User extends Eloquent implements UserInterface, RemindableInterface {

    use UserTrait, RemindableTrait;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'users';
    .....

    public function bookmarks()
    {
        return $this->hasMany('Bookmark','userid');
    }

}

##Here is my db schema:

DB schema

I know that I can get bookmarks and tag id with following:

$bookmarkIDs       = Auth::user()->bookmarks()->get('id');
 $tagsID = Tag::whereIn('name', $tags)->get('id');

But I can't figure how to perform this query for bookmarks_tag.

0 likes
2 replies
cipsas's avatar

@stirredo I think you should use eager loading, ->with() method. Something like:

Tag::with('App\Bookmark')->get();
davorminchorov's avatar

@cipsas The method with() takes the name(s) of the relationship method(s) in the model, not a class path / object.

Example:

Tag::with('bookmarks')->get();

Please or to participate in this conversation.