De : Paolo Gianrossi [mailto:[email protected]] Envoyé : jeudi, 16. février 2012 02:04 À : DBIx::Class user and developer list Objet : [Dbix-class] text search Hi List, I have a Schema/Result class holding user stories. These user stories have various fields, most importantly an Owner (foreign key to another class, User), a body (Text) and a "reason" (more text). What I need to do is enable my users to search for stories of an owner (typing the username/part of username they want) or for matching keywords inside body and/or reason. This sounds to me very close to fulltext search, but I was wondering if there is a standard/best practice way to manage this need, considering the possibility of changing DB backend (SQLite to MySQL or pgsql probably...) Hi Paolo, >From what you describe, it seems indeed that you need to use fulltext search. >But as you mention, the problem is that there is no standard SQL syntax for >fulltext, so SQL clauses written for one specific DBMS vendor will not work >for another. One way to avoid this problem is to implement an abstraction layer by defining "special operators" within SQL::Abstract. There is an example in the doc that defines a special operator called "match", which generates a fulltext search for MySQL. Then your application can write searches like $schema->resultset('Story')-> search( { Owner => $owner, Body => {-match => $some_text}, Reason => {-match => $other_text} }, ); Later on, if you change the DBMS vendor, you just need to redefine this "match" operator. For defining such special operators, you need to pass arguments to SQL::Abstract::new(). I don't really know where the hooks are for doing this within the DBIx::Class framework, but I'm sure some DBIC experts on this list will know the answer. Good luck with your project, Laurent Dami
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
