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 fk (person_id and topic_id) plus the quantity.

ER

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：

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);

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;
}