Level 65
For extra where clauses, I use this
$somevar = Model::whereHas('relation', function($query)
{
$query->whereHas('relationTwo', function ($query)
{
$query->where('some_field', Auth::id());
});
})->get();
Sep 22, 2014
8
Level 2
Restriction on pivot table
I'm trying to form a query using Eloquent relationships where the 'where' clause is on a column in the pivot table, not the main tables. It's similar to a series setup (One main project with multiple entries).
I have three tables like so:
Video (ID, Title, Description)
VideoEntries (ID, Title, Description, Video)
VideoToEntry(ID, Video_ID, VideoEntryID)
So within my VideoEntry model, I have this:
public function videos()
{
return $this->belongsToMany('\Duplicolor\Models\Video\Video', 'video_to_entry', 'video_entry_id', 'video_id');
}
This is the query I'm running:
public function getSlugListByVideoId( $id )
{
dd(self::with('videos')->get()[0]->videos->toArray());
$query = self::with(array('videos' => function($query) use ($id)
{
$query->select('id')
->whereId( $id );
}))
->select( 'title', 'slug' )
->orderBy('title')
->lists('title', 'slug');
}
At the top dd(), I'm trying to see where stuff is pulled. The dd() outputs this:
array (size=2)
0 =>
array (size=12)
'id' => int 4
'title' => string 'Daily Driver Series featuring Matt Steele' (length=41)
'description' => string 'Small projects can make big differences to your daily driver vehicle! Matt Steele and our Daily Driver series will show you how a simple project can transform your car with Dupli-Color products.' (length=194)
'image' => string 'daily-driver-landing-img.png' (length=28)
'image_detail' => string 'daily-driver-landing-img.png' (length=28)
'text_side' => int 1
'featured' => int 0
'type' => int 1
'created_at' => string '2014-05-01 10:54:55' (length=19)
'updated_at' => string '-0001-11-30 00:00:00' (length=20)
'deleted_at' => null
'pivot' =>
array (size=2)
'video_entry_id' => int 5
'video_id' => int 4
1 =>
array (size=12)
'id' => int 5
'title' => string 'How-To Projects' (length=15)
'description' => string 'We've got a ton of videos demonstrating the application of specific Dupli-Color Products. Check them all out here!' (length=114)
'image' => null
'image_detail' => null
'text_side' => int 1
'featured' => int 0
'type' => int 2
'created_at' => string '-0001-11-30 00:00:00' (length=20)
'updated_at' => string '-0001-11-30 00:00:00' (length=20)
'deleted_at' => null
'pivot' =>
array (size=2)
'video_entry_id' => int 5
'video_id' => int 5
Which is great. I only want the ones that have the pivot 'video_id' as 1 ($id) though.. I'm not sure how to put that where restriction within this query setup..
Any ideas? Thank you for your help!
Level 2
I'm just trying to access the pivot table. I rather not create a model and relation for pivot tables.
Unless I'm thinking about this wrong?
Level 4
I think you are looking for the wherePivot function.
http://laravel.com/api/4.2/Illuminate/Database/Eloquent/Relations/MorphToMany.html#method_wherePivot
So you get something like this:
self::with(['videos' => function($query){ $q->wherePivot('video_id', 1); }])->get()
1 like
Level 65
Oh an extra field in the pivot table? If you need access to other custom fields in the pivot table, according to the docs, use this (combined with the above reply)
By default, only the keys will be present on the pivot object. If your pivot table contains extra attributes, you must specify them when defining the relationship:
return $this->belongsToMany('Role')->withPivot('foo', 'bar');
http://laravel.com/docs/4.2/eloquent#working-with-pivot-tables
Level 2
@Sportbundles, I already tried that.
$query = self::with(array('videos' => function($query) use ($id)
{
$query->wherePivot('video_id', $id );
}))
->select( 'title', 'slug' )
->orderBy('title')
->lists('title', 'slug');
dd(\DB::getQueryLog());
It outputs these queries:
select `title`, `slug` from `video_entries` order by `title` asc
It doesn't have another query that pulls the relations or anything. It literally just pulls ALL the columns.. not where video_id = 1.. it should only be pulling 12 results, but it pulls all 129 in the database. I might just have to change over to Fluent..
Level 4
How about one of the following ( just some random try-outs :D, I know their not the best solution ;) )
Video::find($id)->videoentries->with('videos')->get();
Video::find($id)->videoentries()->with('videos')->get();
Video::find($id)->videoentries();
Video::find($id)->videoentries;
Level 65
Did you check my reply using withPivot() in model? Maybe something along these lines. Not really sure why video_id doesn't link to one directly if it's a relationship
Model
public function videos()
{
return $this->belongsToMany('\Duplicolor\Models\Video\Video', 'video_to_entry', 'video_entry_id')->withPivot('video_id');
}
Controller?
public function getSlugListByVideoId($id)
{
$query = self::with(['videos' => function($query) use ($id)
{
$query->wherePivot('video_id', $id);
}])->get();
return $query;
}
Level 5
What about creating a model for the pivot table itself? Then you can just query it like any other model.
Please or to participate in this conversation.