通过许多功能获取数据库记录

Question

I have 5 tables in my database. A minimalized version of them look like this:

+------------+ +--------------+ +---------------+ +-------------+ +-------------+
|    Blog    | |     Feed     | |   Category    | | SubCategory | | Blog_Subcat |
+------------+ +--------------+ +---------------+ +-------------+ +-------------+
| blog_id    | | feed_id      | | category_id   | | subcat_id   | | blog_id     |
| blog_title | | blog_id      | | category_name | | subcat_name | | subcat_id   |
| blog_blog  | | feed_content | +---------------+ | category_id | +-------------+
+------------+ +--------------+                   +-------------+

Pretty much, one blog can be part of 1 or more subcategories. One subcategory can only be part of one category.

I want to display the feeds of the blogs that correspond to certain category. I have some URLs in the form of: http://www.example.com/category_name.

What I do is:

I get the category_name. I search in the Category table for the category_id.
I look in the Sub-Category table all the subcategories that have that category_id.

Then I make a query that joins the Feed and the Blog_Subcat table on the blog_id WHERE category_id IN (array of sub categories).

After that I get all those Feeds that are in that category.

Right now is working, but my table has around 30,000 feeds in only one month so this process I feel is a little bit slow.

My last query looks like this:

public static function findLastPosts($subcategories=false){
    $table = new self;

    $query = $table->select()->setIntegrityCheck(false);
    $query->from('feeds', array('feeds.blog_id', 'feeds.feed_id', 'feeds.feed_content'));       
    $query->join(
        'blog_subcat', 
        'blog_subcat.blog_id = feeds.blog_id', 
        array('blog_subcat.blog_id')
    );

    $query->where('blog_subcat.category IN (?)', $subcategories);           
    $query->where('feeds.date_created <= NOW()');               
    $query->order('ati_feeds.date_created DESC');
     //rest of my code...
}

I'm using Zend Framework for this project. I want to know: if there a better way to get the feeds because some categories take a little bit too long to load?

Answer 1

Try this:

$columns = array(
    // whatever columns you ultimately want to select in this array
    // example: 'title' => 'blogs.title' queries 'blogs.title AS title'
);
$select  = $db->select(); // where $db is your Zend_Db_Adapter instance
$select -> from('feeds as f',$columns);
$select -> join('blogs as b','b.id = f.blog_id',array());
$select -> join('blog_subcat as bs','bs.blog_id = f.blog_id',array());
$select -> join('subcategories as s','s.id = bs.subcat_id',array());
$select -> join('categories as c','c.id = s.cat_id',array());
$select -> where('c.name = ?',$categoryName);
$select -> order('f.date_created DESC');
$select -> group('f.id');
$select -> limit($count,$pageNumber - 1);
$results = $db->fetchAll($select);
// and then whatever you do to populate view models

I think having one query the database can optimize will perform better than multiple queries to piece together the same thing. Especially if you use a remote MySQL server...