Yii2 Filter data with multiple relation

Question

I am working on and listing page of boats where boats are of two types

Shared.
Private.

If shared boats are available

i have to check

if they provide half-day (1/2) trip then price will be shown of half day.
if half-day is not available then check if three fourths(3/4) of a day and price will be three forth of a day.
Else check for full day and take price of full day.

If shared boat is not available

Check for private boat available.
Then apply the same scenario given above for the private boats.

For these pricing i used conditions but in filtering of high-to-low/low-to-high price filtering i can't get any idea how to apply conditions. Can someone help me to solve these.

Models :

Boatinformation(Main).
Boatsharedfishingtrip(shared pricing)
Boatfishingcharter(Private Pricing)

Model Relations :

public function getBoatfishingcharter() {
    return $this->hasOne(Boatfishingcharter::className(), ['boat_id' => 'boat_id']);
}

public function getBoatsharedtrip() {
    return $this->hasOne(Boatsharedfishingtrip::className(), ['boat_id' => 'boat_id']);
}

if($model->boatsharedtrip->shared_fishing_charters == '1') {
    if($model->boatsharedtrip->one_two_day_4hrs == 1) {
        $shared_price = $model->boatsharedtrip->one_two_day_rate_per_angler;
    } else if($model->boatsharedtrip->three_four_day_6hrs == 1) {
        $shared_price = $model->boatsharedtrip->three_four_day_rate_per_angler;
    } else if($model->boatsharedtrip->full_day_8hrs == 1) {
        $shared_price = $model->boatsharedtrip->full_day_rate_per_angler;
    }
} else if($model->boatfishingcharter->private_fishing_charters == 1) {

    if($model->boatfishingcharter->one_two_day_4hrs == 1) {
        $private_price = $model->boatfishingcharter->one_two_day_rate;
    } else if($model->boatfishingcharter->three_four_day_6hrs == 1) {
        $private_price = $model->boatfishingcharter->three_four_day_rate;
    } else if($model->boatfishingcharter->full_day_8hrs == 1) {
        $private_price = $model->boatfishingcharter->full_day_rate;
    }
}

For filter i am currently using :

if($_GET['orderby'] == 'price_desc') {
    $query->orderBy(['one_two_day_rate_per_angler' => SORT_DESC, 'one_two_day_rate' => SORT_DESC, 'three_four_day_rate_per_angler' => SORT_DESC, 'three_four_day_rate' => SORT_DESC, 'full_day_rate_per_angler' => SORT_DESC, 'full_day_rate' => SORT_DESC]);
} elseif($_GET['orderby'] == 'price_asc') {
    $query->orderBy(['one_two_day_rate_per_angler' => SORT_ASC, 'one_two_day_rate' => SORT_ASC, 'three_four_day_rate_per_angler' => SORT_ASC, 'three_four_day_rate' => SORT_ASC, 'full_day_rate_per_angler' => SORT_ASC, 'full_day_rate' => SORT_ASC]);
}


$query = Boatinformation::find();
        $query->leftJoin('boatsharedfishingtrip', '`boatsharedfishingtrip`.`boat_id` = `boatinformation`.`boat_id`')
              ->leftJoin('rating_review', '`rating_review`.`boat_id` = `boatinformation`.`boat_id`')
              ->leftJoin('boatotherservice', '`boatotherservice`.`boat_id` = `boatinformation`.`boat_id`')
              ->leftJoin('boatfishingequipment', '`boatfishingequipment`.`boat_id` = `boatinformation`.`boat_id`')
              ->join('INNER JOIN','boatcompanyinformation', '`boatcompanyinformation`.`id` = `boatinformation`.`boat_id`')
              ->leftJoin('boatfishingcharter', '`boatfishingcharter`.`boat_id` = `boatinformation`.`boat_id`')
              ->where(['boat_publish' => 1])->all();

Edit

This is how i am displaying the boats in the listing

user_3793592 · Answer

Do I correctly understand that relation type between tables is "one-to-one"? I mean Boatinformation, Boatsharedfishingtrip and Boatfishingcharter?

Try this.

// Main query
$query = Boatinformation::find()
    ->leftJoin('boatsharedfishingtrip', '`boatsharedfishingtrip`.`boat_id` = `boatinformation`.`boat_id`')
    ->leftJoin('rating_review', '`rating_review`.`boat_id` = `boatinformation`.`boat_id`')
    ->leftJoin('boatotherservice', '`boatotherservice`.`boat_id` = `boatinformation`.`boat_id`')
    ->leftJoin('boatfishingequipment', '`boatfishingequipment`.`boat_id` = `boatinformation`.`boat_id`')
    ->join('INNER JOIN', 'boatcompanyinformation', '`boatcompanyinformation`.`id` = `boatinformation`.`boat_id`')
    ->leftJoin('boatfishingcharter', '`boatfishingcharter`.`boat_id` = `boatinformation`.`boat_id`')
    ->where(['boat_publish' => 1]);


// Sort
$orderBy = Yii::$app->request->get('orderby');

switch ($orderBy) {
    case 'price_desc':
        $query->orderBy(['one_two_day_rate_per_angler' => SORT_DESC, 'one_two_day_rate' => SORT_DESC, 'three_four_day_rate_per_angler' => SORT_DESC, 'three_four_day_rate' => SORT_DESC, 'full_day_rate_per_angler' => SORT_DESC, 'full_day_rate' => SORT_DESC]);
        break;

    case 'price_asc':
        $query->orderBy(['one_two_day_rate_per_angler' => SORT_ASC, 'one_two_day_rate' => SORT_ASC, 'three_four_day_rate_per_angler' => SORT_ASC, 'three_four_day_rate' => SORT_ASC, 'full_day_rate_per_angler' => SORT_ASC, 'full_day_rate' => SORT_ASC]);
        break;
}


// Execute
// var_dump( $query->createCommand()->getRawSql() );  // Uncomment to debug built SQL

$query->all();

Are you using Yii2 debug toolbar? If so, copy-paste here built SQL. If no, uncomment getRawSql line. It may help to figure out why it does not work for you.

Yii2 Filter data with multiple relation

1 Answers