I want to apply a where condition in the Pivot table between Person and Topics in Laravel.

I basically have a Person table and a Topics table.

In between there is a pivot table person_topic with the two foreign keys (person_id and topic_id) plus the quantity.

I put a ::query() method with a whereHas which based on an array of "topic ids" performs a whereIn condition within the pivot table person_topics.

$from = request('from');
$to = request('to');
$topics = request('topics');
$unity = request('unity');
$limit = request('limit');

$eventsQuery = Event::query();

if (! is_null($topics) && $topics != "undefined") {
    $topics = array_map('intval', explode(',', $topics));
    $eventsQuery = $eventsQuery->whereHas('person.topics', function ($query) use ($topics) {
        return $query->whereIn('topic_id', $topics);
    });
}

$events = $eventsQuery->orderBy('date', 'DESC')
    ->whereIn('station_id', [$unity])
    ->whereBetween('date', [$from, $to])
    ->with('person')
    ->with('person.country')
    ->with('train')
    ->with('station')
    ->limit($limit)
    ->get();

return response()->json([
    'events' => $events,
    'message' => 'Success'
], 200);

The method works and returns me the expected results, only that it takes 2 minutes to receive the response.

How can I optimize / modify my code so that my request performs better?

Complete method code

$from = request('from');
$to = request('to');
$topics = request('topics');
$trains = request('trains');
$countries = request('countries');
$unity = request('unity');
$limit = request('limit');

if(is_null($trains) && $trains == "undefined" ){
    $limit = Event::count();
}

$eventsQuery = Event::query();

if(!is_null($countries) && $countries != "undefined" ){
    $countries = array_map('intval', explode(',', $countries));
    $eventsQuery = $eventsQuery->whereHas('person.country', function ($query) use ($countries) {
        return $query->whereIn('id', $countries);
    });
}
if(!is_null($trains) && $trains != "undefined" ){
    $trains = array_map('intval', explode(',', $trains));
    $eventsQuery = $eventsQuery->whereHas('train', function ($query) use ($trains) {
        return $query->whereIn('id', $trains);
    });
}
if(!is_null($topics) && $topics != "undefined" ){
    $topics = array_map('intval', explode(',', $topics));
    $eventsQuery = $eventsQuery->whereHas('person.topics', function ($query) use ($topics) {
        return $query->whereIn('topic_id', $topics);
    });
}

$events = $eventsQuery->orderBy('date','DESC')->whereIn('station_id', [$unity])->whereBetween('date',[$from, $to])->with('person')->with('person.country')->with('train')->with('station')->limit($limit)->get();

return response()->json([
    'events' => $events,
    'countries' => $countries,
    'message' => 'Success'
], 200);

CodePudding user response：

You can try this:
Ensure you created 2 models are Person and Topic
Create PersonTopic model and define 2 relationship like this

public function person()
{
    return $this->belongsTo('App\Models\Person', 'person_id', 'id');
}

public function topic()
{
    return $this->belongsTo('App\Models\Topic', 'topic_id', 'id');
}

And then , you can query

if(!is_null($topics) && $topics != "undefined" ){
    $topicIds = array_map('intval', explode(',', $topics));
    $rows = PersonTopic::with(['person', 'topic'])
            ->whereIn('topic_id', $topicIds)
            //can use whereHas if you want
            ->whereHas('topic', function ($query) use ($request) {
               $query->where('topics.column', $request->column);
            })
            ->get();
}

Use foreach loop to get details

foreach ($rows as $row) {
   //get person detail
   $person = $row->person;
   //get topic detail
   $topic = $row->topic;
}

CodePudding user response：

If you have the foreign_key of a person into event table, you can use joins into pivot table of person and topics tables instead of where has to optimize the query.

The query:

$eventsQuery = $eventsQuery->whereHas('person.topics', function ($query) use ($topics) {
    return $query->whereIn('topic_id', $topics);
});

Can be replaced with:

$eventsQuery->join(
  'person_topic',
  'person_topic.person_id',
  '=',
  'events.person_id'
)
    ->whereIn('person_topic.topic_id', $topics);