I have the following query:
public function getEventsByKeywordIds($data){
//Query events by dates
$query = DB::table('events')
->join('events_dates', function($join) use ($data){
$join->on('events.id', '=', 'events_dates.event_id')
->where('events_dates.start_date', "<=", date_format(date_create($data['date_end']), "Y-m-d"))
->where('events_dates.end_date', '>=', date_format(date_create($data['date_start']), "Y-m-d"));
});
//Query events by keywords
$query = $query->join('events_keywords', function($join) use ($data){
$join->on('events.id', '=', 'events_keywords.event_id');
})->whereIn('events_keywords.keyword_id', $data["keyword_ids"]);
//Query places
$query = $query->join('places', function($join) use ($data){
$join->on('events.place_id', '=', 'places.id');
});
//Set offset and limit
$query = $query
->take($data['limit'])
->offset($data['offset'])
->orderBy('events_dates.start_date', 'ASC')
->orderBy('events.name', 'ASC')
->groupBy('events.id');
$events = $query->get();
return $events;
}
I am interested in the part that mentions "Query events by keywords".
events_keywordsis a many-to-many table, linking event id's and keyword id's to each other.
The requirement exists to return the keywords per event to the client, but so far the query only returns 1 keyword per event that it returns.
Note: $data["keyword_ids"]is an array of keywords sent from the client, because only events related to certain keywords should be returned.
Do you have any suggestions to adapt this query in a simple way to meet the requirement?
Edit: what I require is actually the keyword names, which is stored in the keyword table (not the many-to-many.
I solved the problem by creating a many to many relationship through Laravel's belongsToMany relation.
foreach($events as $event){
$temp_event = EventModel::find($event->id);
$event->keywords = $temp_event->keywords;
}
return $events;
This is not the most efficient solution I'm sure, but it works.