Zend_Db_Select how to select from a subquery (view)

Question

How can I use Zend_Db_Select to directly select from a subquery (view)?

I have 5 tables with the same structure, I want to get all the rows from them, combine them and remove duplicates. I use UNION, which automatically removes duplicates. The problem is that I add a static column for each table before, so there is one column that differs => duplication.

Here is my request:

SELECT `news_main`.*, 'main' as `category` FROM `news_main` UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics` UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` ORDER BY `date` DESC LIMIT 8 

See how to add static values ​​to a new category column? Now everything else is the same (there are duplicate lines), but since they are from different categories, UNION cannot delete them.

So I thought that I could SELECT remove all rows from this subquery and group them to remove duplicates, for example:

 SELECT * FROM ( SELECT `news_main`.*, 'main' as `category` FROM `news_main` UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics` UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` ORDER BY `date` DESC LIMIT 8 ) as subtable GROUP BY `source` ORDER BY `date` DESC 

I ran this in MySQL and it works great. The only problem is ...

How to do this with Zend_Db_Select fancy functions?

Thanks in advance!

Answer 1

I'm not sure if you can use the nesting option in the constructor from Zend_Db_Select or if you should even do it this way, but an alternative solution would be to just get the db adapter and build the sql query manually.

 $db = Zend_Db_Table::getDefaultAdapter(); $db->query("SELECT * FROM ( SELECT `news_main`.*, 'main' as `category` FROM `news_main` UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics` UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` ORDER BY `date` DESC LIMIT 8 ) as subtable GROUP BY `source` ORDER BY `date` DESC "); 

Related: Zend_Db_Table Subquery

Answer 2

From what I can tell from the source code for Zend_Db_Select, its from () method calls its _join () method, which has the case when the first from () parameter is a Zend_Db_Select object: http://framework.zend.com/svn /framework/standard/trunk/library/Zend/Db/Select.php

 } else if ($name instanceof Zend_Db_Expr|| $name instanceof Zend_Db_Select) { $tableName = $name; $correlationName = $this->_uniqueCorrelation('t'); 

If not, from () it is necessary to support the smoothing of the subquery, enclosing it in parentheses to force it to be transferred to the Zend_Db_Expr instance, as in joinRight () in this example: Zend Framework: Zend_Db_Select - how to join the user subqueries table?

Answer 3

Just define a class to quote your subquery, and then you can add another processing to it:

 class Acme_Db_Expr_Subquery extends Zend_Db_Expr { public function __toString() { return '( ' . $this->_expression . ' )'; } } 

Then use it in the FROM clause (my case, copypaste from a real application, works) or JOIN (speculate, don't try).

 $innerSelect = $dbTableSomeModel->select(true); // Configure it, maybe kick around many layers of abstarction $nestedSelect->from( array( 'derived_alias' => new Acme_Db_Expr_Subquery( $innerSelect ), ) ,array( 'column_alias' => 'column_expression', ) );