[Dbix-class] Is there a way to a full-text MATCH (cols) AGAINST(%s) SORTED

[Alan Hicks]

Hi,

Though I would share my solution to using DBIx with a mysql FULL-TEXT 
database.

Create the table
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);

Add some records
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

Select the records using mysql
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+

Create the DBIx::Class::Schema class
package MyDatabase::Main::Articles;
use base qw/DBIx::Class/;
__PACKAGE__->load_components(qw/Core/);
__PACKAGE__->table('articles');
__PACKAGE__->add_columns(qw/ title body /);
__PACKAGE__->set_primary_key('title');
1;

Get a resultset from a search

my ($match, $against);
$against = $c->req->param('search');
$against =~ tr/'/''/; #needed as DBIx does not escape our literals

$match = 'MATCH (title, body) AGAINST (' . $against . ')';

my $rs = $schema->resultset('Articles')->search(undef, {where => $match});

Enjoy,
Alan

Alan Hicks wrote:
> Hi,
> 
> Is there a preferred way to search against a mysql full-text search? 
> I've looked at the manuals and some of the archives and haven't found 
> any references.
> 
> Thanks,
> Alan
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>