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

[Alan Hicks]

Hideo Kimura wrote:
> 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

Much easier, thanks.

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