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...
signature.asc
Description: OpenPGP digital signature
_______________________________________________ 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/[email protected]/
