Yii Framework Forum: Yii2 Ar: Join Dropped? - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Yii2 Ar: Join Dropped?

#21 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,871
  • Joined: 04-October 08
  • Location:DC, USA

Posted 21 May 2013 - 08:33 PM

I just fixed this issue.
0

#22 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 21 May 2013 - 10:58 PM

View Postqiang, on 20 May 2013 - 10:20 PM, said:

The key difference is the explicit innerJoin() call in Yii 2.
In Yii 1.1, the goal B and C are mixed together via with() call; while in Yii 2, they are separated. Of course in Yii 2, you can easily define a scope to make it look better:
$customers = Customer::find()->orderSubtotalGreaterThan(100)->with('orders')->all();


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. :)
0

#23 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 21 May 2013 - 11:08 PM

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();

0

#24 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 22 May 2013 - 02:40 AM

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();

0

#25 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 118
  • Joined: 09-January 11

Posted 22 May 2013 - 10:09 AM

View PostyJeroen, on 21 May 2013 - 10:58 PM, said:

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


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.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#26 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 22 May 2013 - 10:18 AM

View Postfsb, on 22 May 2013 - 10:09 AM, said:

I disagree. Yii's AR is SQL. Softark 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.

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 ->innerJoin('tbl_order', 'tbl_order.customer_id=tbl_customer.id').
0

#27 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 22 May 2013 - 02:17 PM

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();

0

#28 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,455
  • Joined: 04-March 10
  • Location:UK

Posted 22 May 2013 - 02:37 PM

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.
0

#29 User is offline   fsb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 118
  • Joined: 09-January 11

Posted 22 May 2013 - 08:10 PM

View PostyJeroen, on 22 May 2013 - 10:18 AM, said:

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?


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

View PostyJeroen, on 22 May 2013 - 10:18 AM, said:

What do you think of my idea? It's optional, so you can still use the long way of adding ->innerJoin('tbl_order', 'tbl_order.customer_id=tbl_customer.id').


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.
Join the happiest place on Freenode: #yii
We've got a MrFisk the bot, hilarious banter and all kinds of other groovy stuff going on.
Comparison of Internet Relay Chat clients
Web IRC client
0

#30 User is offline   Alex.Sh 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 23-July 12
  • Location:Riga, Latvia

Posted 24 May 2013 - 02:39 PM

Will be there some replacement for dropped STAT relation?.
0

#31 User is offline   ORey 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,688
  • Joined: 20-April 09
  • Location:Moscow, Russia

Posted 07 January 2014 - 03:35 PM

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/y...relational-data
God is real unless declared as integer
0

#32 User is offline   realtebo 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 619
  • Joined: 03-October 12
  • Location:Ferrara

Posted 11 May 2014 - 07:24 AM

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();
    }

Il computer non ragiona, ma ha sempre ragione.
Ricordalo quando fai il debug
0

#33 User is offline   ORey 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,688
  • Joined: 20-April 09
  • Location:Moscow, Russia

Posted 11 May 2014 - 03:29 PM

fixed, thx
God is real unless declared as integer
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users