[Dbix-class] Is there a way to a full-text MATCH (cols) AGAINST(%s) SORTED
Hideo Kimura
hide at hide-k.net
Tue Aug 1 19:41:22 CEST 2006
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/
More information about the Dbix-class
mailing list