How to set complex queries on 4 tables in yii2?

Question

Yii

Have a request

SELECT * 
FROM  `mmm_tr_in_import`  `in_import` 
LEFT JOIN  `mmm_users`  `user` ON  `in_import`.`fulluserid` =  `user`.`fulluserid` 
LEFT JOIN  `mmm_currency`  `currency` ON  `in_import`.`value_currency` =  `currency`.`value_currency` 
LEFT JOIN  `mmm_tr_in`  `duplicate` ON user_id = user.id_user
AND duplicate.currency_id = currency.id_currency
AND duplicate.paid_date_gmt = in_import.paid_date_gmt
LIMIT 0 , 30

it helps to make comparison of 2 tables with additional data.
I'll add tables if necessary, but that's not the point. I created a request

$query = self::find()->alias('in_import')->joinWith(['user','currency'])
                 ->select(['*','duplicate.*','user.id_user','currency.id_currency'])
                 ->leftJoin(['duplicate'=>TrInModel::tableName()], 
                        'user_id=user.id_user AND duplicate.currency_id=currency.id_currency AND  duplicate.paid_date_gmt = in_import.paid_date_gmt');

But such a query does not provide any information about the duplicate table. In the query result, they simply "disappear".

app\models\TrInImportSearch Object
(
    [page_size] => 200
    [_attributes:yii\db\BaseActiveRecord:private] => Array
        (
            [id_in_import] => 67
            [fulluserid] => 300023
            [paid_date_gmt] => 2001-01-20
            [comment] => Пополнение счета 300023 в системе my.moneymoo.com . Без НДС
            [amount] => 3
            [value_currency] => RUB
            [source] => 5
            [payer_name] => 6
            [payer_inn] => 7
            [payer_bank_bik] => 8
            [payer_bank_name] => 9
            [payer_bank_account] => 10

            [status] => 0
        )

    [_oldAttributes:yii\db\BaseActiveRecord:private] => Array
        (
            [id_in_import] => 67
            [fulluserid] => 300023
            [paid_date_gmt] => 2001-01-20
            [comment] => Пополнение счета 300023 в системе my.moneymoo.com . Без НДС
            [amount] => 3
            [value_currency] => RUB
            [source] => 5
            [payer_name] => 6
            [payer_inn] => 7
            [payer_bank_bik] => 8
            [payer_bank_name] => 9
            [payer_bank_account] => 10

            [status] => 0
        )

    [_related:yii\db\BaseActiveRecord:private] => Array
        (
            [user] => app\models\UsersModel Object
                (
                    [newpassword] => 
                    [newpassword2] => 
                    [password_1] => 
                    [password_2] => 
                    [f_login] => 
                    [f_email] => 
                    [city_name] => 
                    [_companies:app\models\UsersModel:private] => Array
                        (
                        )

                    [_attributes:yii\db\BaseActiveRecord:private] => Array
                        (
                            [id_user] => 23
                            [fulluserid] => 300023
                            [role] => client
                            [first_name] => уаукпкупкуц
                            [last_name] => Серобаба
                            [email] => [email protected]
                            [password] => e10adc3949ba59abbe56e057f20f883e
                            [created_gmt] => 2017-01-06 01:13:55
                            [date_birth] => 2017-01-16
                            [gender] => male
                            [phone] => [phone]
                            [active] => 0
                            [country_id] => 3
                            [city_id] => 231
                            [timezone_gmt] => 9
                            [blocked] => 0
                            [auth_key] => 6c20fb6820455b81db622772465d13a1
                            [del] => 0
                        )

                    [_oldAttributes:yii\db\BaseActiveRecord:private] => Array
                        (
                            [id_user] => 23
                            [fulluserid] => 300023
                            [role] => client
                            [first_name] => уаукпкупкуц
                            [last_name] => Серобаба
                            [email] => [email protected]
                            [password] => e10adc3949ba59abbe56e057f20f883e
                            [created_gmt] => 2017-01-06 01:13:55
                            [date_birth] => 2017-01-16
                            [gender] => male
                            [phone] => [phone]
                            [active] => 0
                            [country_id] => 3
                            [city_id] => 231
                            [timezone_gmt] => 9
                            [blocked] => 0
                            [auth_key] => 6c20fb6820455b81db622772465d13a1
                            [del] => 0
                        )

                    [_related:yii\db\BaseActiveRecord:private] => Array
                        (
                        )

                    [_errors:yii\base\Model:private] => 
                    [_validators:yii\base\Model:private] => 
                    [_scenario:yii\base\Model:private] => default
                    [_events:yii\base\Component:private] => Array
                        (
                        )

                    [_behaviors:yii\base\Component:private] => Array
                        (
                        )

                )

            [currency] => app\models\CurrencyModel Object
                (
                    [_attributes:yii\db\BaseActiveRecord:private] => Array
                        (
                            [id_currency] => 1
                            [value_currency] => RUB
                            [value_currency_long] => Russin Ruble
                        )

                    [_oldAttributes:yii\db\BaseActiveRecord:private] => Array
                        (
                            [id_currency] => 1
                            [value_currency] => RUB
                            [value_currency_long] => Russin Ruble
                        )

                    [_related:yii\db\BaseActiveRecord:private] => Array
                        (
                        )

                    [_errors:yii\base\Model:private] => 
                    [_validators:yii\base\Model:private] => 
                    [_scenario:yii\base\Model:private] => default
                    [_events:yii\base\Component:private] => Array
                        (
                        )

                    [_behaviors:yii\base\Component:private] => Array
                        (
                        )

                )

        )

    [_errors:yii\base\Model:private] => 
    [_validators:yii\base\Model:private] => 
    [_scenario:yii\base\Model:private] => default
    [_events:yii\base\Component:private] => Array
        (
        )

    [_behaviors:yii\base\Component:private] => Array
        (
        )

)

Who will tell you where to look or how to ask differently?

Answer 1

1 decision.
Created a new table view with a query

CREATE
VIEW mmm_tr_in_import_view
AS SELECT in_import.*, user.id_user user_id, currency.id_currency currency_id
FROM  `mmm_tr_in_import`  `in_import` 
LEFT JOIN  `mmm_users`  `user` ON  `in_import`.`fulluserid` =  `user`.`fulluserid` 
LEFT JOIN  `mmm_currency`  `currency` ON  `in_import`.`value_currency` =  `currency`.`value_currency`

and linked it to the second table.
Solution 2 (didn't test):
create relay links using

$this->hasOne(TrInModel::className(), [ 'currency_id'=>'currency_id'])
  ->alias('duplicate')
  ->andOnCondition(" duplicate.paid_date_gmt =in_import.paid_date_gmt");

Answer 2

In the debug panel, you can see what request you really got. Haven't tried using joinWith and leftJoin at the same time, maybe it makes sense to try to make a connection for duplicate and move it to the first joinWith?

Answer 3

The default joinWith also specifies that this data is fetched (by a separate query, after fetching) and loaded into the model. That's why you see them in the dump. (this can be disabled by the second joinWith parameter). Well, he works with relays, so he knows what to load where.
leftJoin - it's stupid to join the table in, so to speak, manual mode. He knows nothing about relays, that something needs to be chosen - he stupidly joins the table and that's it. Accordingly, nothing is selected.
If you want automatic selection - work through relays with with/joinWith