Yii2 Ar: Join Dropped?

Yii 2.0 General Discussions
21

I just fixed this issue.

22

So, its either:

A ) Use innerJoin() and add SQL to my controller code. Seems very ugly since I want to use AR.

B ) Put the innerJoin()/query into a model method. Where’s my flexibility?

@fsb, I’m not hanging in the irc room much anymore, too busy. ;) I’m sure I showed you the wiki page though? Didn’t turn it into a proper extension because of the reasons we discussed back then. :)

23

Maybe an idea, but I’m just brainstorming…


$customers = Customer::find()->where('orders.subtotal>100')->all();

If there’s a relation set in the where() using the dot syntax(‘relationName.attribute’), the method could look if the model has a relation method:


    public function getOrders()

    {

        return $this->hasMany('Order', array('customer_id' => 'id'));

    }

And then adds a JOIN statement, using the innerJoin() method which is based on the ActiveRelation which getOrders() returns.

The following would query based on a joined relation, AND also retrieve the related models:


$customers = Customer::find()->where('orders.subtotal>100')->with('orders')->all();
24

Can you confirm, that in both cases, lazy and eager loading, the related records are obtained the same way?




$orders = $customer->getOrders()->all();

Can we also use the shorthand?


$orders = $customer->orders->all();
25

I disagree. Yii’s AR is SQL. Mindplay explained this very well a while back. Yii’s AR is the best(*) PHP AR because it is not ideological. It provides a practical OOP API for using an RDBMS via an SQL interface. It does not try to do the impossible: hide the nature of tables or pretend that SQL isn’t in the way of using them.

(*) Objective fact. Take my word for it.

26

Ofcourse its SQL. ;) But like you say, its an OOP API. If I use an OOP API, why would I want to put pure SQL somewhere else?

What do you think of my idea? It’s optional, so you can still use the long way of adding [size=“2”][font=“Courier New”]->innerJoin(‘tbl_order’, ‘tbl_order.customer_id=tbl_customer.id’)[/font][/size].

27

I’ve been looking into the Query and QueryBuilder class, and things might get very complex if such a thing is based on just using a column name.

What about adding a togetherWith() method, in addition to the already existing with() method? It would function as Yii1’s together.


    public function with()

    {

        $this->with = func_get_args();

        if (isset($this->with[0]) && is_array($this->with[0])) {

            // the parameter is given as an array

            $this->with = $this->with[0];

        }

        return $this;

    }

    

    public function togetherWith()

    {

        $togetherWith = $this->with = func_get_args();

        if (isset($this->with[0]) && is_array($this->with[0])) {

            // the parameter is given as an array

            $this->with = $this->with[0];

            $togetherWith = array_keys($this->with[0]);

        }

        foreach($togetherWith as $relationName) {

            $model = new $this->modelClass;

            if(isset($model->$relationName) && $model->$relationName instanceof ActiveRelation) {

                //the following might be better moved to a new method in ActiveRelation

                $relation = $model->$relationName;

                $relatedModelName = $relation->modelClass;

                $on = $relationName .'.'. key($relation->link) .'='. $model::tableName() .'.'. array_shift(array_values($relation->link));

                $this->innerJoin($relatedModelName::tableName().' '.$relationName, $on);

            }

        }

        return $this;

    }

So now you could do:




// This retrieves all Customers, and then for each Customer it retrieves only the related Orders that have a subtotal above 100

$customers = Customer::find()->with('orders')->where('subtotal>100')->all();


// This only retrieves the Customers that have Orders with a subtotal above 100, and then retrieves the Orders of those Customers

$customers = Customer::find()->togetherWith('orders')->where('subtotal>100')->all();
28

I like the idea of having a “together” option, but then the core will become bloated with joining and aliasing logic which isn’t required for other situations.

29

I suspect you didn’t fully digest or agree with what Mindplay wrote.

I prefer to see what’s going on. I like code that says what it does. So I prefer the explicit form over the hidden shortcut.

30

Will be there some replacement for dropped STAT relation?.

31

For those who will come to this post from google, here’s the good news: problem is already solved.

Docs on that: https://github.com/yiisoft/yii2/blob/master/docs/guide/db-active-record.md#working-with-relational-data

32

404: page not found …

I’m coming here from google looking for self::stat .

Of course … there is yet a solution

This following is an exmple of a classic relation and a count stat.





    /**

     * @return \yii\db\ActiveQuery

     */

    public function getPublishers()

    {

        return $this->hasMany(Publisher::className(), ['group_id' => 'id']);

    }


    /**

     * @return \yii\db\ActiveQuery

     */

    public function getPublisher_count()

    {

        return $this->hasMany(Publisher::className(), ['group_id' => 'id'])

                ->count();

    }
33

fixed, thx