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

[Christopher H. Laco]

Alan Hicks 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

I suspect one could also do the same using ResultSetManager...(rough
suedo code)...

package MyDatabase::Main::Articles;
...
sub search_matches {
    my $self = shift;
    my $cond = shift;
    my $attrs = shift || {};
    ...magick to get -or stuffs into MATCH in $cond...
    $self->search($cond, $attrs);
};
...
$schema->resultset->('Articles')->search_matches({
  -or => {
    title => 'foo',
    body => 'foo'
  }
});


Having not used either ResultSetManager, or MATCH/AGAINST, this is
purely thought speculation...

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: OpenPGP digital signature
Url : http://lists.rawmode.org/pipermail/dbix-class/attachments/20060801/9418aca8/attachment.pgp