Specifying where

[andreCatita]

Hello,

I am trying this in version 1 and 2, (although in 2 it doesn't make sense anymore, it's just that I adapted your version 2 code to still use table 'post' with defaultLanguage) but let's go with version 1, I am trying with it now, with post and postLang, and defaultLanguage is on post and other languages are stored on postLang, if both post and postLang, have a is_visib column (to control visibility of such content)

(default language -> not specific = en) I perform a find()->where('is_visib=1') he goes to Post, ignores PostLang and works nice.

now, assume I specified \Yii::$app->language = 'ru'; I perform a find()->where('is_visib=1') he goes to Post, performs ('is_visib=1') against Post only, and then joins with PostLang to retrieve the 'russian' version.

but how do I perform this verification of visible against PostLang, since I'm not on defaultLanguage?

Shouldn't this be working in version 1? Or he just can't figure out where to apply where, there's no where to force this right?

If there are no ways or suggestions, should I just update to version 2, and move all my default language 'Post' content to 'PostLang', and then this should work?

[andreCatita]

I can't get specific wheres to work, he only looks for them in the left side table, am I missing something?

UPDATE

Do I have to create a custom action in my ActiveQuery class, and do something like the following? Grabbing the translation and then adding a condition to it?

    public function visible() {
        if (!isset($this->with['translations'])) {
            $this->with(['translation' => function ($query) {
                    $query->andWhere('is_visib=1');
                }]);
        }
        return $this;
    }

If this is the only way, OK, I'll just have to accept it and move on, I just have one more question, if this condition is not met, my relation will not return values, however an object of the Post type will always be returned.

Imagine, a find()->all() for a specific language, which has no visible content. I will have untranslated objects returned, but that for instance, works with foreach, and it will iterate objects without translation, I'd rather reset the object to NULL, if no translation meets my visible condition, how can I make it so?

[OmgDef]

@andreCatita I think your own scope is good idea in this case. Also you can use joinWith

[andreCatita]

@OmgDef Care to give a helping hand? Having a hard time with that part exactly, here's what I've tried:

    public function visible() {
        if (!isset($this->with['translations'])) {
            $this->innerJoinWith(['translation' => function ($query) {
                    $query->andWhere('is_visib=1');
                }]);
        }
        return $this;
    }

But the query resultant from this, lost the id_category in the condition.

SELECT `shop_categories`.* FROM `shop_categories` INNER JOIN `shop_categories_lang` ON `shop_categories`.`id_category` = `shop_categories_lang`.`id_category` WHERE (`lang`='en') AND (is_visib=1)

So I have also thought of changing, getTranslation($language = null) in your MultilingualBehavior, to use a join instead of hasOne, but ends up in the same problem, if you can show a quick example how I would make it so it becomes a join, so I get NULL instead of empty objects.

Much Tanks!

I'd think as this as a future possible enhancement maybe, this way it blocks filtering and custom search per language, and you might make this in a better way then mine.

UPDATE2

The thing is:

$category = \common\models\Categories::find()->langWhere('id_category=:id AND is_visib=1', [':id' => 96])->one();

I would like this, to sustain itself, if I create a custom scope with that name (langWhere), that I can hook my specifications, the thing is, id_category is ambiguous, because it exists in both 'categories' and 'categories_lang', and if I set here the full table name it's kinda as much as work, as making a custom findBySql to get frontend objects.

Also, if I have more then one scope, let's say visible() separately or so, that hook onto the relation, it duplicates the params in the query, per each one.

What is the very best approach for this? I feel like i'm hacking around, and I might be missing something very obvious.

[OmgDef]

@andreCatita

        $data = Post::find()->joinWith([
            'translation' => function ($query) {
                $query->where(['language' => 'en']);
            }
        ], 'INNER JOIN')->indexBy('id')->all();

This will return all models with en translation

Or you can do something like this

        $data = Post::find()->joinWith([
            'translation' => function ($query) {
                $query->where(['language' => 'en'])->andWhere('title is not null');
            }
        ], 'INNER JOIN')->indexBy('id')->all();

[andreCatita]

@OmgDef

Yeah, that's similar to what I have, but here's the problem with that: That way it's not even performing an INNER JOIN, it just performs a LEFT JOIN, the 2nd parameter is ignored.

Also, this still leaves the problem of ambiguous columns, when performing a find for a specific id. Hence the reason I am performing an innerJoinWith:

But if I have a Category, ID: 92, and I want an object with it's translation. I'm going to have to perform an innerJoinWith, and in the where, I'm going to have to use the "CategoriesLang tableName()" to specify the ID, otherwise it will just say it's ambiguous.

(Well obviously the ID itself, could be pointed to the left tabel, but a search for a specific TITLE in a translated language, would force me to the above)

And performing such a query, without an internal resolution to the MultilingualBehavior it's very similar to make a custom find and performing all the joins manually, which is very sad unfortunately

Do you understand the problem? :(

[andreCatita]

@OmgDef Trying to fix the problem, run into a different one, if you maybe could shed some light on the best way to approach the following, I would be very greateful.

How can I enforce all relations that have the ML behavior, to inner join themselfs with the Lang table?

I looked at hasOne(), for example:

    public function hasOne($class, $link)
    {
        /* @var $class ActiveRecordInterface */
        /* @var $query ActiveQuery */
        $query = $class::find();
        $query->primaryModel = $this;
        $query->link = $link;
        $query->multiple = false;
        return $query;
    }

In BaseActiveRecord, I tried calling a custom scope after that find(), but despite saying $class::find(), he doesn't go through the find() in the Model, because my custom scope says there is no relation called 'translation'.

But I'm not even sure extending the AR and changing hasOne, hasMany is the better way, there are many variants for the relations.

Should I just join hasOne manually, ie of testing:

     ->join('INNER JOIN', 'shop_categories_lang', 'is_visib = :id', [':id' => 1]);

Just what do you think it's best? I need my relations to have their visibility checked on their language table.

Thanks, any help or tip is appreciated!

[OmgDef]

@andreCatita I think in your case for output it's better to use QueryBuilder