Cruzito Cruzito - 1 year ago 114
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 Source

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) {
    // or $q->whereSlug($tag);

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

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

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 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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download