Database Joining can be complicated and feel time consuming at first but when used properly can actually save time and improve your code.
Before I get into anything in-depth, I’m going say that I didn’t have much background in programming before this internship. This year, I’m a second year Computer Science Undergrad at UCSC who’s had only two quarters of programming experience before being given the task to make a Magento Extension.
Image taken from: http://websiteexpert.com/ecommerce/magento-ecommerce
Magento and its frameworks was already confusing enough let alone have to build a custom table. However, because of a previous experience with working on a WordPress Plugin, I thought it was doable. The biggest hurdle I had to overcome in this project though was trying to save the Offer ID for each product. After much trial and error, I finally was able to create my own table that would store the Offer IDs.
My table basically looked like this:
| My Auto Increment |
Title |
Magento ProductID |
Status |
OfferID |
| 1 |
Product1 |
1 |
null |
null |
The title was the name of the product. So, in order to keep my table up to date, I created a listener that has to check everything that a user edited or created in a new table.
When the user creates a new product, my table looks like this:
| My Auto Increment |
Title |
Magento ProductID |
Status |
OfferID |
| 1 |
Product1 |
1 |
null |
null |
| 2 |
Product2 |
2 |
null |
null |
Copying data every time the user clicked “save” seems correct, but its not and unnecessary. I thought that this was the proper way of organizing a database until my boss, Michi, told me that I should have been using joins instead of copying information from one table to another. If you look at the second table you can see that the OfferID and the Status values are null. Since this table is meant to keep track of the OfferID and its status, I really didn’t need the Product1 and Product2 row to exist. Ideally, I should have only created a row for a value for OfferID
So I decided to do a Left Join between my OfferID table and Magento’s Product table, while using the Magento Product table as the anchor. By getting rid of the title column, I reduced my table to four columns and was able to use the Magento ProductID column as a bridge to join both tables.
A simplified Magento Product table:
| Magento ProductID |
Title |
| 1 |
Product1 |
| 2 |
Product2 |
The new OfferID table with a value in Magento ProductID 1:
| My Auto Increment |
Magento ProductID |
Status |
OfferID |
| 1 |
1 |
On |
123 |
The Left Join Table with Mangeto Product as anchor:
| Title |
Status |
OfferID |
| Product1 |
On |
123 |
| Product2 |
null |
null |
After I joined the table, everything ran more smoothly. The OfferID table only stores a value when an OfferID is entered now, before it would store a null value when it was not needed. Now that I linked to the Magento Product table, there is no point in having a listener. The process was simplified because the program would check after every save if a change is made. My widget pulls information directly from the Magento Product table, therefore getting rid of my listener.
One of the lessons I learned from this? Had I spent a little more time researching about joins beforehand, I would have saved a lot more time coding the Magento Extension.
