Yii Framework Forum: Find specific item through relation - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Find specific item through relation

#1 User is offline   ehochedez 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 32
  • Joined: 17-November 11
  • Location:China

Posted 14 September 2017 - 08:55 AM

Hello,

Considering I have three models :
- User (id, name)
- Items (id, name)
- UserItems (id, user_id, item_id, quantity)
with the right relations to make User::Model()->with('items') work as expected.

Somewhere in my page I want to display all the items with the quantity the current user owns next to it.

$user = User::Model()->with('items')->findByPk(1);
$items = Item::Model()->findAll();
// Here i display some users general informations
foreach ($items as $item) {
   $user_item = $user->items->findByAttributes(array('id'=>$item->id));   // This line does not work
   $user_item_quantity = $user_item ? $user_item->quantity : 0;
}

That, of course does not work :)
Another solution that should work would be:

foreach ($items as $item) {
   $user_item = $user->items(array('condition'=>'id=$item->id'));
   $user_item_quantity = $user_item ? $user_item[0]->quantity : 0;
}



Do you think of any elegant way to do this without firing too many SQL queries ? Are there any other syntax ?

Thanks for your feedback.
0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,277
  • Joined: 16-February 11
  • Location:Japan

Posted 14 September 2017 - 05:41 PM

Is this what you want to do?

$user = User::Model()->with('items')->findByPk(1);
foreach ($user->items as $item) {
   echo $item->quantity;
}

0

#3 User is offline   ehochedez 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 32
  • Joined: 17-November 11
  • Location:China

Posted 14 September 2017 - 11:05 PM

View Postsoftark, on 14 September 2017 - 05:41 PM, said:

Is this what you want to do?

$user = User::Model()->with('items')->findByPk(1);
foreach ($user->items as $item) {
   echo $item->quantity;
}



Hi softark,

Not really, I want to loop on the complete list of items, and next to each items display the quantity the user owns.
Your solution will only loop on the items the user owns.
0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,277
  • Joined: 16-February 11
  • Location:Japan

Posted 15 September 2017 - 02:31 AM

I see. So you want to do something like the following:
$user = User::Model()->findByPk(1);
// Here i display some users general informations

$items = Item::Model()->findAll();
foreach ($items as $item) {
   $user_item = UserItem::Model()-findByAttributes(['user_id' => $user->id, 'item_id' => $item->id]);
   $user_item_quantity = $user_item ? $user_item->quantity : 0;
   // Here you display item's information and the quantity of it the user has
}


Right?

Hmm, but it could be more elegant, I believe. :)
0

#5 User is offline   ehochedez 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 32
  • Joined: 17-November 11
  • Location:China

Posted 15 September 2017 - 03:02 AM

View Postsoftark, on 15 September 2017 - 02:31 AM, said:

I see. So you want to do something like the following:
$user = User::Model()->findByPk(1);
// Here i display some users general informations

$items = Item::Model()->findAll();
foreach ($items as $item) {
   $user_item = UserItem::Model()-findByAttributes(['user_id' => $user->id, 'item_id' => $item->id]);
   $user_item_quantity = $user_item ? $user_item->quantity : 0;
   // Here you display item's information and the quantity of it the user has
}


Right?

Hmm, but it could be more elegant, I believe. :)


That's what I ended up using but it requires one query for each user item.. not exactly optimal :(
I'm sure there must be a better way :)
0

#6 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,277
  • Joined: 16-February 11
  • Location:Japan

Posted 15 September 2017 - 07:36 AM

Yes, you are right. The following code requires 1 + 1 + N queries:
$user = User::Model()->findByPk(1);  // 1 query
$items = Item::Model()->findAll();   // 1 query
foreach ($items as $item) {
   $user_item = UserItem::Model()-findByAttributes(['user_id' => $user->id, 'item_id' => $item->id]); // N queries
   $user_item_quantity = $user_item ? $user_item->quantity : 0;
}


What do you think about this? It will require only 3 queries.
$user = User::Model()->with('user_items')->findByPk(1);  // 1 + 1 queries
$items = Item::Model()->findAll();   // 1 query
foreach( $items as $item ) {
    $user_item = findUserItem($user->user_items, $item);  // No queries
    $user_item_quantity = $user_item ? $user_item->quantity : 0;
}

...
public static function findUserItem($user_items, $item)
{
    foreach($user_items as $user_item) {
        if ($user_item->item_id == $item->id) {
            return $user_item;
        }
    }
    return null;
}


I really don't know it's faster or better, though. ;) Maybe someone will come up with a better idea.

As a side note, you should define a dedicated AR model for 'user_item' table, and you should also specify the direct relations between 'user' and 'user_item', and between 'item' and 'user_item', because 'user_item' has the 'quantity' field that makes 'user_item' table something more than a mere junction table.
0

#7 User is offline   ehochedez 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 32
  • Joined: 17-November 11
  • Location:China

Posted 15 September 2017 - 07:48 AM

View Postsoftark, on 15 September 2017 - 07:36 AM, said:

Yes, you are right. The following code requires 1 + 1 + N queries:
$user = User::Model()->findByPk(1);  // 1 query
$items = Item::Model()->findAll();   // 1 query
foreach ($items as $item) {
   $user_item = UserItem::Model()-findByAttributes(['user_id' => $user->id, 'item_id' => $item->id]); // N queries
   $user_item_quantity = $user_item ? $user_item->quantity : 0;
}


What do you think about this? It will require only 3 queries.
$user = User::Model()->with('user_items')->findByPk(1);  // 1 + 1 queries
$items = Item::Model()->findAll();   // 1 query
foreach( $items as $item ) {
    $user_item = findUserItem($user->user_items, $item);  // No queries
    $user_item_quantity = $user_item ? $user_item->quantity : 0;
}

...
public static function findUserItem($user_items, $item)
{
    foreach($user_items as $user_item) {
        if ($user_item->item_id == $item->id) {
            return $user_item;
        }
    }
    return null;
}


I really don't know it's faster or better, though. ;) Maybe someone will come up with a better idea.

As a side note, you should define a dedicated AR model for 'user_item' table, and you should also specify the direct relations between 'user' and 'user_item', and between 'item' and 'user_item', because 'user_item' has the 'quantity' field that makes 'user_item' table something more than a mere junction table.


Hi softark, thank you for sharing your solution.
I did think of that as well but you will agree that it is not ideal :)
I thought there might be a way to search among the items of the relation (ActiveRecord collection ?) such as :

$user->items->findByAttributes(array('id'=>$item->id));


Maybe it would do the same as the code you suggested but at least it would look pretty :)
Maybe I'm simply looking for something that does not exist...
0

#8 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,277
  • Joined: 16-February 11
  • Location:Japan

Posted 15 September 2017 - 09:10 AM

View Postehochedez, on 15 September 2017 - 07:48 AM, said:

Maybe I'm simply looking for something that does not exist...


Probably yes. And you should make it for yourself and share it with us. :)

BTW, I just noticed that you seem to be using Yii 1.1.x. Please bare in mind that the following discussion applies to Yii 2.x only.

I think we may be able to do something smart using 'Dynamic Relational Query' (http://www.yiiframew...elational-query).

class Item extends ActiveRecord
{
    ...
    /* the standard 'userItems' relation */
    public function getUserItems()
    {
        return $this->hasMany(UserItem::className(), ['item_id' => 'id']);
    }

    /* the special 'userItems' relation specifying a certain user */
    public function getOwnerUserItem($ownerId)
    {
        return $this->hasOne(UserItem::className(), ['item_id' => 'id'])
            ->where('user_id = :owner_id', [':owner_id' => $ownerId]);
    }
    ...
}


Then we can do this:
$user = User::findOne(1);
$items = Item::find()->getOwnerUserItem($user->id)->all();
foreach ($items as $item) {
   $user_item_quantity = $item->ownerUserItem ? $item->ownerUserItem->quantity : 0;
}

1

#9 User is offline   ehochedez 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 32
  • Joined: 17-November 11
  • Location:China

Posted 15 September 2017 - 09:35 AM

View Postsoftark, on 15 September 2017 - 09:10 AM, said:

class Item extends ActiveRecord
{
    ...
    /* the standard 'userItems' relation */
    public function getUserItems()
    {
        return $this->hasMany(UserItem::className(), ['item_id' => 'id']);
    }

    /* the special 'userItems' relation specifying a certain user */
    public function getOwnerUserItem($ownerId)
    {
        return $this->hasOne(UserItem::className(), ['item_id' => 'id'])
            ->where('user_id = :owner_id', [':owner_id' => $ownerId]);
    }
    ...
}


Then we can do this:
$user = User::findOne(1);
$items = Item::find()->getOwnerUserItem($user->id)->all();
foreach ($items as $item) {
   $user_item_quantity = $item->ownerUserItem ? $item->ownerUserItem->quantity : 0;
}



Thanks for your answer.
Ah yes sorry I am still using Yii 1.1, didn't notice it was a 2.0 forum.

From the doc I believe the answer you suggest would return only the items owned by the user not all the items no ?

Doc:
// SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 200 ORDER BY `id`
$orders = $customer->getBigOrders(200)->all();


Also it is mentioned that a query is fired every time we try to access the relational property which would defeat the purpose:

Quote

Unlike accessing a relation property, each time you perform a dynamic relational query via a relation method, a SQL statement will be executed, even if the same dynamic relational query was performed before.


Sorry, I don't have enough energy to install Yii 2.0 and validate my sayings :)
0

#10 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,277
  • Joined: 16-February 11
  • Location:Japan

Posted 15 September 2017 - 07:03 PM

View Postehochedez, on 15 September 2017 - 09:35 AM, said:

Thanks for your answer.
Ah yes sorry I am still using Yii 1.1, didn't notice it was a 2.0 forum.

Me too. :D
Let's continue in this forum at the moment. I will move it to the proper one later.

Quote

From the doc I believe the answer you suggest would return only the items owned by the user not all the items no ?


No, I was trying to get "UserItem" model, not "Item" model.

User has many Items through 'user_item' junction table. But it is in fact a short cut of 'User has many UserItems' and 'UserItem has one Item'. Usually we can safely ignore the junction table, but sometimes it is useful or necessary to have an independent model for it. It is true with your 'user_item' table because it has 'quantity' field that should be accessed using ActiveRecord syntax.

So I defined the 'userItems' and 'ownerUserItem' relations for UserItem model, not for Item model.

Quote

Doc:
// SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 200 ORDER BY `id`
$orders = $customer->getBigOrders(200)->all();


Also it is mentioned that a query is fired every time we try to access the relational property which would defeat the purpose:


$items = Item::find()->getOwnerUserItem($user->id)->all();

The line above will be translated to 2 sqls like the following (not exactly so, but conceptually):
select * from `item`;
select * from `user_item` where `item_id` in (1,2,3,4, ...) and `user_id` = :user_id;

The 1st sql is for getting Item models, and the 2nd is for getting the related UserItem models.
'(1,2,3,4 ...)' stands for the ids of the Items fetched in the first query.
By traversing those 2 arrays of result sets, Yii will construct the object array of Item with its related UserItem. The result contains all of the Items, but each Item may or may not have related UserItem.

Quote

Sorry, I don't have enough energy to install Yii 2.0 and validate my sayings :)

Never mind.
I know that Yii 1.1.x is a very good framework. I'm still using it for one of my sites.
But, you know, Yii 2.0 is much better. He he. :D
1

#11 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 3,588
  • Joined: 10-October 10
  • Location:Denmark

Posted 15 September 2017 - 07:26 PM

Yii 1 feels like PHP 3 after having used Yii2 for a while :)
"Less noise - more signal"
0

#12 User is offline   ehochedez 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 32
  • Joined: 17-November 11
  • Location:China

Posted 16 September 2017 - 12:09 PM

Yes I do plan to code the next websites with 2.0 :)
Softark, thanks a lot for your answer I learned something, and even if it's not an "out of the box" solution I believe it is a clean solution so I'll accept it :)
0

Share this topic:


Page 1 of 1
  • 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