[Dbix-class] text search

[Dami Laurent (PJ)]

 =


 =


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, Us=
er), a body (Text) and a "reason" (more text).

What I need to do is enable my users to search for stories of an owner (typ=
ing the username/part of username they want) or for matching keywords insid=
e 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 nee=
d, considering the possibility of changing DB backend (SQLite to MySQL or p=
gsql probably...)



 =


Hi Paolo,

 =


>From what you describe, it seems indeed that you need to use fulltext sear=
ch. 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 defin=
ing "special operators" within SQL::Abstract.  There is an example in the d=
oc that defines a special operator called "match", which generates a fullte=
xt 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::Abs=
tract::new(). I  don't really know where the hooks are for doing this withi=
n the DBIx::Class framework, but I'm sure some DBIC experts on this list wi=
ll know the answer.

 =


Good luck with your project,

 =


Laurent Dami

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120216/ef3=
42ea4/attachment.htm