Cruzito Cruzito - 6 months ago 46
MySQL Question

Laravel querying data through a pivot table

I have a table called 'projects' and a table called 'tags'. These 2 are connected through a pivot table named 'project_tag' so for example like this:

There is 1 record in the projects table with project_id 1

then in the 'project_tag' table there is this :

project_id 1 and tag_id 1


and tag_id 1 is "sometag" for example

Now my problem is I want to query all the projects that has "sometag" as name in the tags table.
Is there a way to do this? Or should I work with the id's instead of the tag values?

What my models look like:

Project model:

public function tags()
{
return $this->belongsToMany('App\Tag')->withTimestamps();
}


Tag model:

public function projects()
{
return $this->belongsToMany('App\Project');
}


I am a bit lost in my database structure :) I am fairly new to laravel

Many thanks in advance!

Answer

To query projects by certain tags you ca do like this:

// Get projects by a single tag
$tag = 'mytag';

Project::whereHas('tags', function ($q) use ($tag) {
    $q->whereName($tag);
    // or $q->whereSlug($tag);
})->get();

// Get projects by multiple tags
$tags = ['mytag', 'othertag', 'iamtag'];

Project::whereHas('tags', function ($q) use ($tags) {
    $q->whereIn('name', $tags);
})->get();

Or you can query projects from pivot table like this:

// Get projects by a single tag
$tag_id = 1;

Project::whereIn('id', function($q) use ($tag_id) {
    $q->select('project_id')->from('project_tag')->where('tag_id', $tag_id);
})->get();

// Get projects by multiple tags
$tag_ids = [1, 2, 3];

Project::whereIn('id', function($q) use ($tag_ids) {
    $q->select('project_id')->from('project_tag')->whereIn('tag_id', $tag_ids);
})->get();