Hello there.
Let me first tell you, that I do not have any experience with Yii2 or Yii1 so be little more patient 
I do not get how the ActiveRecord must be used when we deal with multilingual DB. Up to now I do write
every single SQL by hand using joins, sub queries and whatever is needed and everything was just fine. However, now with ActiveRecords the
things get messy.
Let’s assume that I have following tables -
+----------------------------------+
| products |
|----------------------------------+
| id int not null auto_increment |
| active tinyint(0) default 0 |
| sku varchar(...).. |
|..... |
+----------------------------------+
+----------------------------------+
| products_texts |
|----------------------------------+
| id int not null auto_increment |
| product_id FK to products |
| language_id FK to language tbl |
| caption varchar(...).. |
|..... |
+----------------------------------+
I have to make list of some products, for example some 20 items. If I write query for this, it would look like this one -
SELECT products.id, products.sku, products_texts.caption FROM products
INNER JOIN products_texts ON products_texts.product_id = products.id AND language.id = products_texts.language_id
WHERE
products.active = 1
LIMIT 20
However, when I use ActiveRecord, from what I learnt from the guide 2.0 I must do -
class Products extends \yii\db\ActiveRecord {
.....
/**
* @return \yii\db\ActiveQuery
*/
public function getTexts()
{
return $this->hasOne(ProductsTexts::className(), ['product_id' => 'id'])->onCondition(['language_id' => 'bg']);
}
.....
}
Thus will allow me to access multilingual texts via "$product->text" propertie.
I can do Product::find()->joinWith(‘texts’)… and continue to use the returned ActiveQuery, but generated SQL is useless.
It will make actual join, but result is useless - SELECT only select “products.*” without any option (or I can’t figure how) to select
any field from joined table.
So, finally, my question is - How should I use ActiveRecord for ML DB? Keep in mind that this single join is just for example. In the
reality there will be at least 4.
My main concern here is - do I will execute 21 queries (1 for table pages and 20 more for result texts) to fetch 20 products and their texts or I miss something essential with AR?
Thanks for your time.