[Solved] Db->Createcommand Not Behaving As Expected

Yii 1.1 General Discussion
1

Hello,

I’m trying to run this query via the Yii::app()->db->createCommand()




select items.item_id, items.item_title, items.user_id, user.avatar from items join user on user.id = items.user_id where items.user_id in (25,26,90);

When I run this via mySQL I get the correct results




+---------+-----------------+---------+----------------------+

| item_id | item_title      | user_id | avatar               |

+---------+-----------------+---------+----------------------+

|    1273 | test            |      25 | avatars/gravatar.png |

|    1274 | Some test item  |      25 | avatars/gravatar.png |

|    1275 | Some other item |      25 | avatars/gravatar.png |

|    1277 | test2           |      26 | avatars/gravatar.png |

|    1278 | test3           |      26 | avatars/gravatar.png |

|    1279 | test4           |      26 | avatars/gravatar.png |

+---------+-----------------+---------+----------------------+

So logically I created this command




Yii::app()->db->createCommand()

    ->select( 'i.item_id, i.item_title, i.item_price, i.image_path, i.user_id, u.avatar' )

    ->from( 'items i' )

    ->join( 'user u', 'u.id = i.user_id' )

    ->where( array( 'in', 'i.user_id', $friends_list ) )

    ->limit( $limit, $start-1 )

    ->queryAll();

However, the results I get are duplicated, for example




Array ( [item_id] => 1273 [item_title] => test [item_price] => 39.99 [image_path] => /item_images/1273.jpg [user_id] => 25 [avatar] => avatars/gravatar.png )

Array ( [item_id] => 1273 [item_title] => test [item_price] => 39.99 [image_path] => /item_images/1273.jpg [user_id] => 25 [avatar] => avatars/gravatar.png )

I can’t figure out what the problem is, as I said in mysql this work correctly and shows only one copy of each result, where as via Yii it returns duplicates.

Thanks!

2

What does your query look like when you do:


$command = Yii::app()->db->createCommand()

	->select( 'i.item_id, i.item_title, i.item_price, i.image_path, i.user_id, u.avatar' )

	->from( 'items i' )

	->join( 'user u', 'u.id = i.user_id' )

	->where( array( 'in', 'i.user_id', $friends_list ) )

	->limit( $limit, $start-1 )

	->queryAll();

echo $command->getText();
3

Oh my… I am stupid! :blink:

Later in the code I’m printing the contents of the array again… that’s why I keep seeing duplicates!

Just goes to show how important it is to check for the little things!

It works perfectly now… This is somewhat embarrassing xD

Thanks for the help anyways!

edit: Can someone please close/delete this thread, as it’s completely useless… thanks

4

Can you modify the post to add "[SOLVED]" in front of the post title?