2012/2/16 Dami Laurent (PJ) <[email protected]> > ** ** > > ** ** > > *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***** > > Hi Laurent, thanks a huge lot for your pointers. Sounds promising. I'll investigate the issue further wrt passing SQL::Abstract oddities to DBIC, but sound doable :)
cheers paolino > _______________________________________________ > 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] >
_______________________________________________ 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]
