Find specific item through relation

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.

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.

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

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…

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.yiiframework.com/doc-2.0/guide-db-active-record.html#dynamic-relational-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;

}



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:

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

Me too. :D

Let’s continue in this forum at the moment. I will move it to the proper one later.

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.




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



The line above will be translated to 2 sqls like the following (not exactly so, but conceptually):

[sql]

select * from item;

select * from user_item where item_id in (1,2,3,4, …) and user_id = :user_id;

[/sql]

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.

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

Yii 1 feels like PHP 3 after having used Yii2 for a while :)

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