title: Symfony Zend Lucene Search Multiple Tables
body: <p>I have a Symfony project and I used Zend Lucene Search framework to integrate a search on the site. It works beautifully but it's limited to searching 1 table.</p>
  
  <p>I need my users to be able to search the whole site (8 select tables) and return the results all together. Each table has the same fields indexed. This is the code that specifies the table and calls the query.</p>
  
  <p>Is there a way to make it look through all 8 tables for results?</p>
  
  <pre><code>public function getForLuceneQuery($query)
  {
    $hits = self::getLuceneIndex()-&gt;find($query);
  
    $pks = array();
    foreach ($hits as $hit)
    {
      $pks[] = $hit-&gt;pk;
    }
  
    if (empty($pks))
    {
      return array();
    }
    $alltables = Doctrine_Core::getTable('Car');
    $q = $alltables-&gt;createQuery('j')
       -&gt;whereIn('j.token', $pks)
       -&gt;orderBy('j.endtime ASC')
       -&gt;andwhere('j.endtime &gt; ?', date('Y-m-d H:i:s', time()))
       -&gt;andWhere('j.activated = ?', '1')
       -&gt;limit(21);
  
    return $q-&gt;execute();
  }
  </code></pre>
  
  <p>To give a bit of background on the 8 tables, they are all basically similar.   They all have title, make, model, etc so I need to run a single query on all of them and return all results (regardless of which table it is in) in Ascending order.  The Doctrine_core::getTable command doesn't seem to like multiple tables or even arrays (unless I'm not doing it right).  Thanks!</p>
  
  <p>UPDATE (WORKING):</p>
  
  <p>Here is the updated code.  This is what I have in the <code>SearchTable.class.php</code> file:</p>
  
  <pre><code>public function getForLuceneQuery($query)
  {
    // sort search result by end time
    $hits = self::getLuceneIndex()-&gt;find(
      $query, 'endtime', SORT_NUMERIC, SORT_ASC
    );
  
    $result = array(
      'index' =&gt; $hits,
      'database' =&gt; array(),
    );
  
    // group search result by class
    foreach ($hits as $hit)
    {
      if (!isset($result['database'][$hit-&gt;class]))
      {
        $result['database'][$hit-&gt;class] = array();
      }
  
      $result['database'][$hit-&gt;class][] = $hit-&gt;pk;
    }
  
    // replace primary keys with real results
    foreach ($result['database'] as $class =&gt; $pks)
    {
      $result['database'][$class] = Doctrine_Query::create()
        // important to INDEXBY the same field as $hit-&gt;pk
        -&gt;from($class . ' j INDEXBY j.token')
        -&gt;whereIn('j.token', $pks)
        -&gt;orderBy('j.endtime ASC')
        -&gt;andwhere('j.endtime &gt; ?', date('Y-m-d H:i:s', time()))
        -&gt;andWhere('j.activated = ?', '1')
        -&gt;limit(21)
        -&gt;execute();
  
    }
  
    return $result;
  }
  </code></pre>
  
  <p>Here is what I have in the <code>actions.class.php</code> file for the Search Module:</p>
  
  <pre><code>public function executeIndex(sfWebRequest $request)
    {
      $this-&gt;forwardUnless($query = $request-&gt;getParameter('query'), 'home', 'index');
  
      $this-&gt;results = Doctrine_Core::getTable('Search') 
        -&gt;getForLuceneQuery($query);
  
    }
  </code></pre>
  
  <p>And finally this is my template file <code>indexSuccess.php</code>  I have simplified it so it's easier to understand.  My <code>indexSuccess.php</code> is more complicated but now that I can call the values, I can customize it further.  </p>
  
  <pre><code>  &lt;div class="product_list"
    &lt;ul&gt;
      &lt;?php foreach ($results['index'] as $hit): ?&gt;
        &lt;li class="item"&gt;
        &lt;?php if (isset($results['database'][$hit-&gt;class][$hit-&gt;pk])) ?&gt;
          &lt;span class="title"&gt;
              &lt;?php echo $results['database'][$hit-&gt;class][$hit-&gt;pk]-&gt;getTitle() ?&gt;
          &lt;/span&gt;
        &lt;/li&gt;
      &lt;?php endforeach ?&gt;
    &lt;/ul&gt;
    &lt;/div&gt;
  </code></pre>
  
  <p>This works beautifully.  I was able to customize it by calling each of the fields in the search results and it works perfect.   I added an item to each of the tables with the same title and the search result pulled them all.   Thank you so much!</p>