0

I am creating custom admin grid in Magento 1.

I want to show all wishlist with some data like id, date_created etc. and also total value of each wishlist.

I am working with some modified Magento 1 tables wishlist (default columns) and wishlist_item (added column price). See simplified example here:

wishlist
+-------------+-------------+
| wishlist_id | customer_id |
+-------------+-------------+
|        8609 |       39099 |
|        9540 |       39043 |
+-------------+-------------+

and

wishlist_item
+-------------+-------+
| wishlist_id | price |
+-------------+-------+
|        8609 |   150 |
|        8609 |   199 |
|        8609 |    50 |
|        9540 |    54 |
|        9540 |     5 |
|        9540 |    47 |
|        9540 |    80 |
+-------------+-------+

Problem i have is getting total, as each wishlist_id can have multiple items/rows in table wishlist_item.

I can add total with custom renderer like this:

public function render(Varien_Object $row)
{
    if($row->getData()) {
        $read = Mage::getModel('core/resource')->getConnection('core_read');
        $sql = 'SELECT SUM(PRICE) FROM wishlist_item WHERE wishlist_id = ' . $row->getData('wishlist_id');
        return $read->fetchOne($sql);
    }
}

With this you can see i am getting total with SUM in table wishlist_item by wishlist_id. This works like it should, but i would like to get this by joining to collection instead.

So far i have tried something like this.

$collection = Mage::getResourceModel('wishlist/wishlist_collection')
    ->join(array("wi" => 'wishlist_item'), "wi.wishlist_id = main_table.wishlist_id", array("*","sum(wi.price) as total" ))
        ->where('wi.wishlist_id = main_table.wishlist_id');

This is not working, as wishlist_id table have multiple columns with same id.

Error and query here:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'wishlist_id' in order clause is ambiguous, query was: SELECT `main_table`.*, `wi`.*, sum(wi.price) AS `total` FROM `wishlist` AS `main_table`
 INNER JOIN `wishlist_item` AS `wi` ON wi.wishlist_id = main_table.wishlist_id WHERE (wi.wishlist_id = main_table.wishlist_id) ORDER BY wishlist_id DESC LIMIT 20

Any help would be much appreciated :)

  • Have you checked by removing where condition from your join? where condition is not required in this join. Remove whole where() code and check. – Jaimin Sutariya Mar 14 '18 at 8:22
  • Did not fix it. Error now: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'wishlist_id' in order clause is ambiguous, query was: SELECT main_table.*, wi.*, sum(wi.price) AS total FROM wishlist AS main_table INNER JOIN wishlist_item AS wi ON wi.wishlist_id = main_table.wishlist_id ORDER BY wishlist_id DESC LIMIT 20 – puntable Mar 14 '18 at 8:25
  • 1
    Are you using wishlist_id in SORT? If so change it to 'main_table.wishlist_id'. In your query 'wishlist_id' is used in ORDER BY, and it doesn't know which wishlist_id to consider. – Geethika Antony Mar 14 '18 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.