Complex Zend Query from same user table

Question

I have a rather unique set of conditions and orders in which I need to retrieve data from a "sellers" table for an application I'm building in Zend framework.

The client is basically requesting an application where the directory page lists sellers in a very particular order, which is:

1. Sellers who have been approved in the last 7 days (then order by #4 below)
2. Then, selllers who have paid for upgraded features on the site, and are more the 7 days old (then order by #4 below)
3. Then, Sellers who are more than 7 days old and are more than 7 days old (then order by #4 below)
4. For all of the above, secondary order by would be their launch date, then alpha by business name

I'm trying to figure out the most effective way to write an action helper that will return the data in the correct sequence above, knowing that some of my views only need 1,2 (and 4), whereas other views within the application will need all 4.

Right now, I've been writing two or three separate queries, and passing them to 2 or 3 partialloop's inside the view, but I strive for properly written code, and would like to either combine my 3 queries into one object I can pass to one partial loop, or.... write one query. How can this be done?

Here's my helper at the moment:

class Plugin_Controller_Action_Helper_ListSellers extends Zend_Controller_Action_Helper_Abstract
{

//put your code here
public function direct($regulars = false, $filter = false)
{
    $dateMod = $this->dateMod = new DateTime();
    $dateMod->modify('-7 days');
    $formattedDate = $dateMod->format('Y-m-d H:i:s');
    // get sellers initialized in last 7 days
    $sellerTable = new Application_Model_DbTable_Seller();



        // get sellers initialized in last 7 days
        $select = $sellerTable->select()->setIntegrityCheck(false);
        $select->from(array('b' => 'seller'),array('sellerID', 'businessName','sellerPicture'));
        // select firstName, lastName, picture from user table, and businessName and sellerID from seller table.  All records from seller table
        $select->join(array('u' => 'user'), 's.userID = u.userID', array('firstName', 'lastName'));
        $select->order('s.launchDate DESC','s.businessName ASC');
        $select->where('s.active = 1 AND s.contentApproval = 1 AND s.paymentApproval = 1');
        $select->where('s.launchDate > ?', $formattedDate);
        if($filter){ $select->where('s.categoryID = ?', $filter);}
        $newSellers = $sellerTable->fetchAll($select);



        $query = $sellerTable->select()->setIntegrityCheck(false);
        $query->from(array('b' => 'seller'),array('sellerID', 'businessName','sellerPicture'));
        // select firstName, lastName, picture from user table, and businessName and sellerID from seller table.  All records from seller table
        $query->join(array('u' => 'user'), 's.userID = u.userID', array('firstName', 'lastName'));
        $query->order('s.launchDate DESC','s.businessName ASC');

        $query->where('s.active = 1 AND s.contentApproval = 1 AND s.paymentApproval = 1 AND s.featured = 1');
        $query->where('s.launchDate < ?', $formattedDate);
        if($filter){ $select->where('s.categoryID = ?', $filter);}
        $featuredSellers = $sellerTable->fetchAll($query);


    if($regulars){
        $where = $sellerTable->select()->setIntegrityCheck(false);
        $where->from(array('b' => 'seller'),array('sellerID', 'businessName','sellerPicture'));
        // select firstName, lastName, picture from user table, and businessName and sellerID from seller table.  All records from seller table
        $where->join(array('u' => 'user'), 's.userID = u.userID', array('firstName', 'lastName'));
        $where->order('s.launchDate DESC','s.businessName ASC');

        $where->where('s.active = 1 AND s.contentApproval = 1 AND s.paymentApproval = 1 AND s.featured IS NULL');
        $where->where('s.launchDate < ?', $formattedDate);
        $regularSellers = $sellerTable->fetchAll($where);
    }

}
}

Answer 1

I don't see any limits being applied to your queries. So does that mean you really want to select all matching records? For scalability reasons I'd guess that the answer should be no, there will be limits applied. In this case, you may just have to do 3 different queries.

But if there are no limits to be applied, then you could do a single simple query that selects all sellers, unfiltered and unsorted, and do your sorting and filtering in view helpers or just in your views.

Regardless, I recommend not putting database queries inside your controller layer, assuming you want to use the Model-View-Controller pattern which Zend is built for. Controllers should be thin. Your models should handle all database queries and just spit out the results into your controllers. I use the Data Mapper pattern extensively. Something like:

$mapper = new Application_Model_SellerMapper();

$newSellers = $mapper->fetchNewSellers();
$featuredSellers = $mapper->fetchFeaturedSellers();
$regularSellers = $mapper->fetchRegularSellers();

Each of your fetchX() methods would return an array of Application_Model_Seller instances, rather than Zend_Db_Table_Row instances.

This way you maintain Separation of Concerns and Single Responsibility Principle better, for more maintainable code. Even if you're the only developer on the project over the long-term, 6 months from now you won't remember what you wrote and why. And if someone else comes on the project, clarity becomes really important.