ZEND search with MySQL Match and Union

Question

I have to confess that I cannot work out how to complete this. I can get the query working in MySql, and I can get simple table-or-view queries in Zend Framework, but this is beyond me!

Here's the query I'm trying to run:

SELECT * from (
SELECT 
'Tab' as 'table_name', TabId as id, `TabTitle` as title,
(MATCH(`TabTitle`,`TabSubTitle`) AGAINST (@target)) as relevance
from Tab
UNION
SELECT 
'Tab2' as 'table_name', 
Tab2Id as id, `Tab2Title` as title,
(MATCH(`Tab2Title`,`Tab2Desc`) AGAINST (@target)) as relevance
from Tab2
)
as sitewide WHERE relevance > 0 order by relevance DESC;

I'd like any pointers you can offer as to how to shoehorn this into the MVC framework of Zend!

Edited to add:

This is the current Model Code I'm using, which gives an error "Argument 1 passed to Zend_Paginator_Adapter_DbTableSelect::__construct() must be an instance of Zend_Db_Select, string given,"

class Application_Model_Search extends Zend_Db_Table
{
protected $_name = ‘Search’;
protected $_primary = 'SearchId';

function getSearchResults($page, $searchTerm) 
{

    $query = "
SELECT * from (
 SELECT 
 'Tab' as 'table_name', TabId as id, `TabTitle` as title,
 (MATCH(`TabTitle`,`TabSubTitle`) AGAINST (@target)) as relevance
 from Tab
 UNION
 SELECT 
 'Tab2' as 'table_name', 
 Tab2Id as id, `Tab2Title` as title,
 (MATCH(`Tab2Title`,`Tab2Desc`) AGAINST (@target)) as relevance
 from Tab2
)
as sitewide WHERE relevance > 0 order by relevance DESC;        
    ";

    echo ($query);      

    $paginator = new Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect($query));
    $paginator->setItemCountPerPage(15); //
    $paginator->setCurrentPageNumber($page); 
    return $paginator; 


}


}

the question is - how in Zend can I run a query this complex? It doesn't seem to fit within the Models I usually use which need a root Table or View? — Andrew, Nov 04 '12 at 07:09
I've added the Model Code I'm currently trying to see if that helps! — Andrew, Nov 04 '12 at 08:10

score 1 · Accepted Answer · answered Nov 04 '12 at 12:12

1

    $config = array (
        'dbname' => 'test',
        'password' => 'passwd',
        'username' => 'user'
    );

    $database = new Zend_Db_Adapter_Mysqli ($config);

    $query_1 = new Zend_Db_Select ($database);
    $query_1->from ('Tab', array (
        'Tab AS table_name',
        'TabId AS id',
        'TabTitle` as title',
        new Zend_Db_Expr ('(MATCH(`TabTitle`,`TabSubTitle`) AGAINST (@target)) as relevance')
    ));

    $query_2 = new Zend_Db_Select ($database);
    $query_2->from ('Tab2', array (
        'Tab2 AS table_name',
        'Tab2Id AS id',
        'Tab2Title` as title',
        new Zend_Db_Expr ('(MATCH(`Tab2Title`,`TabDesc`) AGAINST (@target)) as relevance')
    ));

    $query = $database->select ()
        ->union (array (
        $query_1,
        $query_2
    ));

    echo $database->select ()->from ($query)->where ('relevance>0')
        ->order ('relevance DESC');

answered Nov 04 '12 at 12:12

akond

15,865
4
35
55

Thanks Akond - that looks great. WHich file should this go in - the Model file? And if so, how do I get around needing to specify the default table for the model? class Application_Model_Search extends Zend_Db_Table { – Andrew Nov 04 '12 at 15:30
It does not have to be a descendant of Zend_Db_Table, does it? – akond Nov 04 '12 at 17:23
I don't think it has to be, but as a noob, that's pretty much the only way I start Models! What would you put around this code to make it a Model? – Andrew Nov 07 '12 at 19:25
I don't even think this code should be in a model. Rather in a gateway. But nevermind. – akond Nov 07 '12 at 21:12

ZEND search with MySQL Match and Union

1 Answers1