[Dbix-class] text search

[Paolo Gianrossi]

2012/2/16 Dami Laurent (PJ) <laurent.dami at justice.ge.ch>

>  ** **
>
> ** **
>
> *De :* Paolo Gianrossi [mailto:paolino.gianrossi at gmail.com]
> *Envoy=E9 :* jeudi, 16. f=E9vrier 2012 02:04
> *=C0 :* 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 searc=
h,
> but I was wondering if there is a standard/best practice way to manage th=
is
> 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 =3D> $owner,*
>
> *    Body =3D> {-match =3D> $some_text}, *
>
> *   Reason =3D> {-match =3D> $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/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120216/3c2=
ff836/attachment.htm