Symfony Zend Lucene Search Multiple Tables

Question

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.

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.

Is there a way to make it look through all 8 tables for results?

public function getForLuceneQuery($query)
{
  $hits = self::getLuceneIndex()->find($query);

  $pks = array();
  foreach ($hits as $hit)
  {
    $pks[] = $hit->pk;
  }

  if (empty($pks))
  {
    return array();
  }
  $alltables = Doctrine_Core::getTable('Car');
  $q = $alltables->createQuery('j')
     ->whereIn('j.token', $pks)
     ->orderBy('j.endtime ASC')
     ->andwhere('j.endtime > ?', date('Y-m-d H:i:s', time()))
     ->andWhere('j.activated = ?', '1')
     ->limit(21);

  return $q->execute();
}

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!

UPDATE (WORKING):

Here is the updated code. This is what I have in the SearchTable.class.php file:

public function getForLuceneQuery($query)
{
  // sort search result by end time
  $hits = self::getLuceneIndex()->find(
    $query, 'endtime', SORT_NUMERIC, SORT_ASC
  );

  $result = array(
    'index' => $hits,
    'database' => array(),
  );

  // group search result by class
  foreach ($hits as $hit)
  {
    if (!isset($result['database'][$hit->class]))
    {
      $result['database'][$hit->class] = array();
    }

    $result['database'][$hit->class][] = $hit->pk;
  }

  // replace primary keys with real results
  foreach ($result['database'] as $class => $pks)
  {
    $result['database'][$class] = Doctrine_Query::create()
      // important to INDEXBY the same field as $hit->pk
      ->from($class . ' j INDEXBY j.token')
      ->whereIn('j.token', $pks)
      ->orderBy('j.endtime ASC')
      ->andwhere('j.endtime > ?', date('Y-m-d H:i:s', time()))
      ->andWhere('j.activated = ?', '1')
      ->limit(21)
      ->execute();

  }

  return $result;
}

Here is what I have in the actions.class.php file for the Search Module:

public function executeIndex(sfWebRequest $request)
  {
    $this->forwardUnless($query = $request->getParameter('query'), 'home', 'index');

    $this->results = Doctrine_Core::getTable('Search') 
      ->getForLuceneQuery($query);

  }

And finally this is my template file indexSuccess.php I have simplified it so it's easier to understand. My indexSuccess.php is more complicated but now that I can call the values, I can customize it further.

  <div class="product_list"
  <ul>
    <?php foreach ($results['index'] as $hit): ?>
      <li class="item">
      <?php if (isset($results['database'][$hit->class][$hit->pk])) ?>
        <span class="title">
            <?php echo $results['database'][$hit->class][$hit->pk]->getTitle() ?>
        </span>
      </li>
    <?php endforeach ?>
  </ul>
  </div>

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!

Answer 1

OK. I'll try to give you some hint, with code :)

First of all you should add these fields to the index:

$doc->addField(Zend_Search_Lucene_Field::Keyword('class', get_class($record)));
$doc->addField(Zend_Search_Lucene_Field::UnIndexed('endtime', strtotime($record->get('endtime'))));

Than you should use these new fields:

public function getForLuceneQuery($query)
{
  // sort search result by end time
  $hits = self::getLuceneIndex()->find(
    $query, 'endtime', SORT_NUMERIC, SORT_ASC
  );

  $result = array(
    'index' => $hits,
    'database' => array(),
  );

  // group search result by class
  foreach ($hits as $hit)
  {
    if (!isset($result['database'][$hit->class]))
    {
      $result['database'][$hit->class] = array();
    }

    $result['database'][$hit->class][] = $hit->pk;
  }

  // replace primary keys with real results
  foreach ($result['database'] as $class => $pks)
  {
    $result['database'][$class] = Doctrine_Query::create()
      // important to INDEXBY the same field as $hit->pk
      ->from($class . ' j INDEXBY j.token')
      ->whereIn('j.token', $pks)
      ->orderBy('j.endtime ASC')
      ->andwhere('j.endtime > ?', date('Y-m-d H:i:s', time()))
      ->andWhere('j.activated = ?', '1')
      ->limit(21)
      ->execute();

    // if you want different query per table
    // you should call a function which executes the query
    //
    // if (!method_exists($table = Doctrine_Core::getTable($class), 'getLuceneSearchResult'))
    // {
    //   throw new RuntimeException(sprintf('"%s::%s" have to be exists to get the search results.', get_class($table), 'getLuceneSearchResult'));
    // }
    //
    // $results[$class] = call_user_func(array($table, 'getLuceneSearchResult'), $pks);
  }

  return $result;
}

After that in the template you should iterate over $result['index'] and display results from $result['database']

foreach ($result['index'] as $hit)
{
  if (isset($result['database'][$hit->class][$hit->pk]))
  {
    echo $result['database'][$hit->class][$hit->pk];
  }
}

---

And there are same alternate (maybe better) solutions that I can think of:

Alternate solution #1:

You can store data in the index and this data will be accessible in the search result. If you not need too much data when displaying the results and can update the index frequently I think this is a good option. This way you can use pagination and no SQL queries needed at all.

$doc->addField(Zend_Search_Lucene_Field::Text('title', $content->get('title')));
...
$hit->title;

Alternate solution #2:

As you wrote, your tables are very similar, so you maybe could use column aggregation inheritance. In this way all data stored in one table so you can query them all together and can order and paginate as you want.