/** * Fultext search on columns * * @param string $text * @param array $columns * @return $this */ public function search(WaxModel $model, $text, $columns = array()) { // First up try to add the fulltext index. Do nothing if errors $cols = array_keys($columns); $index_name = implode("_", $cols); foreach ($cols as $col) { $sql = "ALTER TABLE `" . $model->table . "` ADD FULLTEXT " . $col . " ({$col});"; $stmt = $this->db->prepare($sql); $this->exec($stmt, array(), true); } $text = $this->db->quote($text); // Run the query adding the weighting supplied in the columns array $model->select_columns = "SQL_CALC_FOUND_ROWS * ,("; foreach ($columns as $name => $weighting) { $model->select_columns .= "({$weighting} * (MATCH({$name}) AGAINST ({$text})) ) +"; } $model->select_columns = rtrim($model->select_columns, "+"); $model->select_columns .= ") AS relevance "; $model->filter("MATCH(" . implode(",", $cols) . ") AGAINST ({$text} IN BOOLEAN MODE)"); $model->having = "relevance > 0"; $model->order = "relevance DESC"; // Add an arbitrary limit to force found_rows to run if (!$model->limit) { $model->limit(1000); } return $model; }