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

[Hideo Kimura]

Hi,

How about this?

my $aginst = 'foo';
my $rs = $schema->resultset('Articles')->search(
    {},
    {order_by => 'id desc', page => 1, limit => 10}
)->search_literal('MATCH (title, body) AGAINST( ? )', $aginst);

hide

On Tue, 01 Aug 2006 18:23:54 +0100
Alan Hicks <ahicks at p-o.co.uk> wrote:

> 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/
> > 
> 
> _______________________________________________
> 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/