CODEWITHSUNDEEP
phpyii2
mohi
mohi

Reputation: 93

How to achieve muliple left join in yii2?

I have 3 tables named stock, product, users. I have a joinning query as below. Can anybody please tell me how to implement it in yii2?

SELECT 
    stock.sale_price_per_unit,
    sum(stock.quantity),
    product.product_name,
    users.username

FROM
    stock
LEFT JOIN product ON product.product_id=stock.product_id
LEFT JOIN users ON users.user_id=stock.seller_id
WHERE stock.product_id=3
GROUP BY stock.seller_id

Upvotes: 1

Views: 21247

Answers (3)

Ashish Pathak
Ashish Pathak

Reputation: 824

Here is full code For joins In model Geography.php here i have model geography and i bind three tables in geography table in yii2

#####**Create Relation With Country Table** 

public function getCountry() {
    return $this->hasOne(Country::className(), ['id' => 'country_id']);
}

#####**Create Relation With State Table** 

public function getState() {
    return $this->hasOne(State::className(), ['id' => 'state_id']);
}

**#####Create Relation With City Table** 

public function getCity() {
    return $this->hasOne(City::className(), ['id' => 'city_id']);
}

Upvotes: 0

Degger
Degger

Reputation: 4313

Refering to http://www.yiiframework.com/doc-2.0/guide-active-record.html#joining-with-relations and http://www.yiiframework.com/forum/index.php/topic/50423-joins-are-back/:

$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();

so your code should look like:

$query = Stock::find()
->joinWith(['product', 'users']);

$items = $query
    ->select([
        'sale_price_per_unit',
        'sum(quantity)',
        'product.product_name',
        'users.username'])
    ->where(['stock.product_id'=>3])
    ->groupBy('seller_id')
    ->all();

where relations "product" and "users" declared in Stock model: HAS_MANY:

public function getUsers()
{
  return $this->hasMany(User::className(), ['seller_id' => 'user_id']);
}

HAS_ONE

public function getProduct()
{
  return $this->hasOne(Product::className(), ['product_id' => 'product_id']);
}

Upvotes: 5

Samar Haider
Samar Haider

Reputation: 912

    $criteria = new CDbCriteria;
$criteria->select = 'DISTINCT t.*';
                                        $criteria->join = ' LEFT JOIN `shared_items` AS `si` ON t.workspace_id = si.workspace_id';
                                        $criteria->addCondition('t.status_id != ' . ProjectStatuses::STATUS_CLOSED);
                                        $criteria->addCondition('t.workspace_id = ' . $workspace_id);

Upvotes: -1

Related Questions