there's no support for full-text queries on DAL, you have to resort to
executesql.
On Tuesday, April 9, 2013 12:59:31 AM UTC+2, Lisandro wrote:
>
> I've succesfully implemented postgresql fulltext search (or at least a
> basic implementation).
> In the model I have:
>
> from gluon.dal import SQLCustomType
> tsv = SQLCustomType(type='text', native='tsvector')
> db.define_table('anuncios', Field('titulo', 'string'), Field('tsv', tsv))
>
> Then I manually created a before update trigger on the table to keep "tsv"
> field updated (when the row is updated, the new tsv value is calculated and
> stored).
> In the controller, this example of searching by a given query:
>
> resultados = db("tsv @@ plainto_tsquery('%s')" %request.vars.q).select()
>
> where request.vars.q has a simple text query. This is working perfectly.
>
> Now, I want to order the results of the query by "relevance", that is, I
> want to show first those results that are more relevant with the query..
> For that, PostgreSQL full text search comes with everything I apparently
> need:
>
> http://www.postgresql.org/docs/9.1/static/textsearch-controls.html#TEXTSEARCH-RANKING
>
> But now I'm stucked in making the query via DAL sintax. The SQL sentence I
> want to execute is this:
>
> SELECT titulo, ts_rank_cd(tsv, query) AS rank
> FROM anuncios, plainto_tsquery('my search words') query
> WHERE query @@ tsv
> ORDER BY rank DESC;
>
> ¿Could somebody help me to translate this SQL sentence to DAL sintax?
> Thanks in advance!
>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
