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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
