CODEWITHSUNDEEP
joinyii2yii2-advanced-appquery-builder
Roby Sottini
Roby Sottini

Reputation: 2265

Yii2: How to join tables with Query Builder?

I'm using the Yii2 framework for a very basic stock software. I have two tables of a very basic stock software:

products = (id_product, name)
movements = (id_movement, quantity, date)

The products table has all the product of a supermarket. When the supermarket buy a product, it is registered in the movements table.

I want to show a grid with the current stock in the index.php view. I am thinking to have a actionIndex function to get the total of each products.

Basically I want to do this:

public function actionIndex()
{
    Get movements and products from database;
    For each product {
        if (movement is IN) {
            add();
        }
        else {
            subtract();
        }
    }
    return...
}

Using Gii Code Generator, I made a CRUD for products and a CRUD for movements.

So first, I need to get the products and movements from database. Without Yii, I can make a simple JOIN query. But I have no idea how to do it using Yii. What is the best way to do it? I would like to use the query builder.

I found this question but I didn't work for me. And this documentation but I really don't understand it.

Upvotes: 0

Views: 3853

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You can use innerJoin or similar function 

    $result=    (new Query())
        ->select('table1.col1, table1.col2,,,, table2.co1, table2.col2 ...')
        ->from('products ')
        ->innerJoin('movements', 'product.key= movements.key');

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html

Upvotes: 1

Related Questions