How to implement PostgreSQL "ts_rank" function for full text search?

[Lisandro]

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!

[Niphlod]

there's no support for full-text queries on DAL, you have to resort to executesql.

[Massimo Di Pierro]

This is an interest example. Let me think if we can add an API like we did for PostGIS

[Johann Spies]

I am also using Postgresql's full text search.  Any development in Web2py to accommodate that will we very much appreciated.

Regards
Johann

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

--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Alex]

is there anything new regarding this topic? I'd like to do full text search in Postgres using DAL:
http://www.postgresql.org/docs/current/static/textsearch-tables.html

maybe it's possible to manually extend the Query class somehow? or do I have to use executesql instead?

Alex

[Johann Spies]

maybe it's possible to manually extend the Query class somehow? or do I have to use executesql instead?

I use executesql increasingly as DAL sometimes causes delaying side-effects especially in combination with large tables (and I have one of more than 1 000 000 000 records).  If you use executesql in combination with views you can then run DAL on the view and use stuff like the grid etc.

Regards
Johann