Attach fields from another table

Yii 2.0 General Discussions
1

Hi, I have two tables and I want to attach fields from another table
This is my query:

$sql = ‘SELECT product.*, shop.alias_shop, shop.address_shop FROM product
INNER JOIN shop
ON product.id_shop = shop.id
WHERE product.status_prod = 1’;
$query = Product::findBySql($sql);

$pages = new Pagination([‘totalCount’ => $query->count(), ‘pageSize’ => 12,
‘pageSizeParam’ => false, ‘forcePageParam’ => false]);
$products = $query->offset($pages->offset)->limit($pages->limit)->all();

debug($products, 1);
// There are no fields from another table

return $this->render(‘search’, compact(‘products’, ‘pages’, ‘q’));

Please tell me the correct query.

2

Hi @PBA, welcome to the forum.

The SQL is correct. But in the following line of code:

$products is an array of Product models, not the direct result sets of the query. They may or may not contain the fields from shop table. It depends on the definition of Product model.

Have you defined the relations between Product and Shop? If not, I strongly recommend you define them, because they will help you a lot to accelerate your development.

Given the relation of shop in Product model, the query could be written like the following:

$query = Product::find()->with('shop')->where([`status_prod` => 1]);
$pages = new Pagination([‘totalCount’ => $query->count(), ‘pageSize’ => 12,
‘pageSizeParam’ => false, ‘forcePageParam’ => false]);
$products = $query->offset($pages->offset)->limit($pages->limit)->all();

foreach($products as $product) {
    echo $product->name;
    echo $product->shop->alias_shop . ' ' . $prodcut->shop->address_shop;
}

Or, if you do want to do it without ActiveRecord for some reason, you could use asArray():

$products = $query->offset($pages->offset)->limit($pages->limit)->asArray()->all();
3

Yes, I have and it works fine. But after debug I see ful row from product and full row from shop.
I’m afraid for memory. One row from product ~1K, one row from shop ~1K and service information ~1K
Imagine I have one million products, so I get busy with three gigabytes of memory.
Is it correct or the memory is occupied by the number of products on the page, in my case twelve, 12 * 3K = 36K?

4

The memory consumption is not determined by the total number of records, but by the the LIMIT which is equal to the page size. So you don’t have to worry about the memory consumption and the execution speed as long as you are working with the decent page sizes, even if you use ActiveRecords.

5

Thank you very much!