innerJoin with ActiveDataProvider on Yii2

Question

I'm trying the following:

I have 3 models: Clinic, Pack and OfferedTreatment.

Pack and OfferedTreatment are related to Clinic by a FK clinic_id and both models are related to each other by a junction model: Pack_OfferedTreatment (pack_id, offeredTreatment_id). One Pack can have several OfferedTreatments.

From ClinicController I want a certain clinic and a certain pack related to that clinic and get all related offeredTreatments to show a view

The result I have from the following function is it returns me ALL the offeredTreatments associated to the clinic, but not to the pack....what am I doing wrong?

 /**
 * @param $clinicId
 * @param $packId
 * @return array
 * @throws NotFoundHttpException
 */
public function actionPackOfferedTreatments($clinicId, $packId)
{

    $clinicId = (int)$clinicId;
    $packId = (int)$packId;

    // Find model of the clinic
    $model = $this->findModel($clinicId);

    // pack offeredTreatments:
    $packOfferedTreatmentDataProvider = new ActiveDataProvider([
        'query' => $model->getOfferedTreatments()
                         ->innerJoin('pack_offeredTreatment', false)
                         ->where(['pack_offeredTreatment.pack_id' => $packId]),
        'pagination' => false,
        'sort' => [
            'defaultOrder' => [
                'order' => SORT_ASC
            ]
        ]
    ]);
    Yii::$app->response->format = Response::FORMAT_JSON;
    return $packOfferedTreatmentDataProvider->getModels();
}

yes, I want to know what's wrong on my innerJoin...it lists all offeredTreatment, but I want to list only the offeredTreatments that are related to a certain pack due to the join table pack_offeredTreatment — Ber Tsacianegu del Tepuy

Jørgen Jørgen · Accepted Answer · 2019-02-15T07:58:37

I would think about it another way. You say that Offered Treatment is related to the clinic with a foreign key - but it should not be. The pack is related to the clinic, and the treatment is related to the pack, so it's already related trough pack.

If you have relations set up, Clinic hasMany Packs, and Packs hasMany treatments, you could run your query on a clinic with ->innerJoinWith('packs.treatments'). If you have the pack, you can run ->with(['clinic', 'treatments']).

From the docs:

You can eagerly load deeply nested relations, such as a.b.c.d. All parent relations will be eagerly loaded. That is, when you call with() using a.b.c.d, you will eagerly load a, a.b, a.b.c and a.b.c.d.

innerJoin with ActiveDataProvider on Yii2

1 Answers