如何使用透视表来获取数据，使用laravel 4中的pivot上的where条件

Question

How can I fetch data with belongsToMany relation models.

Table tags
  - id
  - name
  ...

Table photo_entries
  - id
  ...

Table photo_entry_tag
  - id
  - tag_id
  - photo_entry_id

Tag.php

public function photo_entry()
{
    return $this->belongsToMany('PhotoEntry');
}

PhotoEntry.php

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

Now I need to fetch photo entries from photo_entries table with their tags where tag_id=1.

I have tried this:

$photos = PhotoEntry::orderBy('votes', 'desc')->with(array('tags' => function($query)
{
   $query->where('tag_id', 1); 
}))->paginate(50);

But its not giving me the proper result, its returning all photos. I am not getting what is the issue.

Can any one please help me.

Answer 1

You need to select all records form photo_entries table that has a relation in photo_entry_tag table so to do that your query should be like this

$tag_id = 1;
$query = "SELECT * FROM `photo_entries` WHERE `id` IN (SELECT `photo_entry_id` FROM `photo_entry_tag` WHERE `tag_id` = {$tag_id})";

Also the equivalence code in eloquent will be like the code below

$tag_id = 1;
$photos = PhotoEntry::whereIn('id', function($query) use ($tag_id) {
    $query->select('photo_entry_id')->from('photo_entry_tag')->whereTagId($tag_id
);
})->get();

Also you can add orderBy('votes', 'desc') just before get() to sort the result