2

Let's imagine we have custom component similar to small shop, that contains many products. Database is very relational. Now we want to display list of all the products in our shop using overwritten model's method getListQuery(). There we have query with multiple joins. In view.html.php we fetch all items by $this->get('items') and everything is ready to present data. This is option no 1.

I have idea to make it more object-oriented. It means getListQuery() doesn't use any relations to get additional data which describe product. It only gets PK and columns from products table. Now we overwrite model's getItems().

public function getItems() {
        $items = parent::getItems();
        for($i=0;$i<count($items);++$i){
            $Product = JTable::getInstance('Product');
            $Product->bind($items[$i]);
            $items[$i] = $Product;
        }
        return $items;
}

Now every item is instance of OurComponentTableProduct. This table has getters to get product data that is located in other table. Directly in view we use getters to some data, for example getName() or getCategory(). This is my 2nd option.

As you can see, second option is similar to Entity or Java DAO (I think so). In this case our entity is Table class. Moreover I think it is more elastic and elegant. I see here disadvantage caused by making additional queries to database. For example, product prices are located in another table than product primary data and these tables have relation product_id-product_id. Using first option we can just JOIN table with prices and select column. In second option we write getPrice method that create query and fetch price. Every item do himself query.In first option we fetch everything in one query.

Of course, we have view with product details. Here we can again write one huge query to fetch this product (DRY rule), but we can also use our OurComponentTableProduct. Just load by PK and this is all.

Performance is very imporant for me, beacause I'm just developing big component with a large number of rows in db: hundreds of thousands in each relational table.

What do you think is better solution and why?

2

I would suggest you to use mixed versions of both the cases.

  1. Product and its related information in one join query
  2. Product statistics such as items sold etc., which cannot join in single query with another getXXX method.

Java DAO frameworks, such as Hibernate, and Joomla database API are entirely two different approaches. Joomla database queries (with model) are designed to work with single query. If you want to use multiple functions for different tables there are few issues.

  1. Joomla Model is designed to get a single query and fetch result for it. i.e. the query is tied to your model. If you want to use two different methods say getProduct and getPrice, you need to write your own implementation of fetching query for both methods. So you will end up adding separate model for each dataset.

  2. You will be overriding JModelList for fetching list of products. So your other objects like pagination, filters, form data - all are tied to your model. So again you need to do manual stuff for everything instead of simply using parent methods defined in JModelList.

  3. Performance - It depends on lots of other factors like your indexes, how much data present etc. Fetching simple result from one table will always loads faster than a join. However you will loose the flexibility of building complex query and their by getting refined list using joins. And anyway you need to build all where clauses specific to each individual query if you are not using join. A join will be executing in memory of database and individual queries needs multiple calls to database. A well optimized database will fetch results faster using join compare to multiple individual results. Add the network overhead if your database on different server, for example using Amazon RDS.

  4. Not all results can be derived by using multiple single queries. You will end up fetching unneeded data just to filter out the required dataset. For example,

    a. I want a list of products which are sold 10 days ago.

    b. Products in one table and transactions in other

    How can you get only products which are sold 10 days ago without using joins?

From all above points, I would say writing individual queries for individual datasets have no added advantage.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.