Stuck on Random & Unique in Query Builder
Hi - I'm been at Laravel for a couple of weeks now and am getting the hang of it but am struggling with a kind of complex query I need to build. Essentially I have three tables: polaroids (a list of files), tags (a list of keywords) and polaroid_tag (a pivot table for the two). Essentially a user selects an array of tags and passes the tag id number array through the URL parameter and I need to query it to:
- Get the keyword text (tag) from associated with the id.
- Count the number of occurrences of each keyword in the pivot table
- Pick a single polaroid row to display as an example.
This part all works - but I would like selection of the single polaroid to be random and distinct from any other it selects. This is what I have so far:
public function getIndex()
{
$getTags = Request::query('tag');
$tagTable = DB::table('tags')
->join('polaroid_tag', 'tags.id', '=', 'polaroid_tag.tag_id')
->join('polaroids', 'polaroids.id', '=', 'polaroid_tag.polaroid_id')
->select(DB::raw('count(*) as tag_count, tag, tag_id as id, filename, polaroid_id'))
->whereIn('tags.id', $getTags)
->groupBy('tag')
->get();
$this->layout = View::make('story.close',compact('tagTable'));
}
When testing in my phpMyAdmin using
ORDER BY RAND( )
in my INNER JOIN generated random selections, but I don't know how to mimic this in Laravel Query Builder, also it would not make them necessarily distinct if 3 tags were being displayed.
Any guidance would be appreciated! Thanks.
Please or to participate in this conversation.