Search with MySQL Match and Union into Paginator

[andrew-l]

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:

Code: Select all

    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!

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,"

Code: Select all

<?php

    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;
      
      
   }

   
    }

[facingwest]

What happens when you pass in a Zend_Db_Select object?

Is your query literally a string like that? try something like the following.

Code: Select all


$db  = <whatever your db adapter is>

$tabOne = $db->select()
    ->from(array('t1' => 'tab')
        , arrray('table_name' => 'Tab'
            , 'id' => 'TabId'
            , 'title' => 'TabTitle'
            , 'relevance' => new Zend_Db_Expr('(MATCH(`TabTitle`,`TabSubTitle`) AGAINST (@target))')
    ));

$tabTwo = $db->select()
    ->from(array('t2' => 'tab2')
        , arrray('table_name' => 'Tab2'
            , 'id' => 'Tab2Id'
            , 'title' => 'Tab2Title'
            , 'relevance' => new Zend_Db_Expr('(MATCH(`Tab2Title`,`Tab2Desc`) AGAINST (@target))')
    ));

$combined = $db->select()
    ->union(array($tabOne, $tabTwo))
    ->where('relevance > 0')
    ->order('relevance');


Now i didn't test this and it's all off the top of my head, but it should be enough for you to get the idea. Try something along those lines, also you MIGHT be able to just do $db->select($query); and pass that in but i've never tried that before.

Hope this helps.

[jiangkool]

Code: Select all

protected $_name = ‘Search’;
   protected $_primary = 'SearchId';

you make a mistake here