Search with MySQL Match and Union into Paginator

For programming and general questions on Zend Framework

Search with MySQL Match and Union into Paginator

Postby andrew-l on Sun Nov 04, 2012 11:21 am

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

   
    }
andrew-l
 
Posts: 2
Joined: Fri Mar 30, 2012 9:28 pm

Re: Search with MySQL Match and Union into Paginator

Postby facingwest on Wed Nov 07, 2012 3:23 pm

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.
facingwest
 
Posts: 18
Joined: Fri Sep 23, 2011 4:46 pm

Re: Search with MySQL Match and Union into Paginator

Postby jiangkool on Sun Nov 18, 2012 5:42 am

Code: Select all
protected $_name = ‘Search’;
   protected $_primary = 'SearchId';
you make a mistake here
jiangkool
 
Posts: 1
Joined: Thu Nov 15, 2012 10:31 am


Return to Zend Framework

Who is online

Users browsing this forum: No registered users and 6 guests