Hallo Massimo, Long ago you reacted to an email by me:
On 1 December 2010 17:53, mdipierro <[email protected]> wrote: > The query is easy: > > db("@@ to_tsquery('%s');" % title_body.replace("'","''")).select() > > to generate the table you need SQLCustomField and the compute > attribute. > Give it a try and please report back. I am very much interested in > this issue. > > > > > On Dec 1, 6:32 am, Johann Spies <[email protected]> wrote: > > How do I handle the following PostgreSQL function in Web2py? > > > > ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector; > > UPDATE pgweb SET textsearchable_index_col = > > to_tsvector('english', coalesce(title,'') || ' ' || > coalesce(body,'')); > > > > I know I can use executesql for creating the indexes but in some cases > > it is preferable to create a column with the type 'tsvector' as in the > > example above or in the one below: > > > > CREATE TABLE messages ( > > title text, > > body text, > > tsv tsvector > > ); > > > > And then how do I handle queries like this? > > > > SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body'); > > > I am making some progress and could get full text search to work in the following way: In Web2py: from gluon.dal import SQLCustomType Add a definition: tsv = SQLCustomType( type ='text', native='tsvector' ) Add field to model: Field('tsv',type=tsv) Then in postgresql: UPDATE akb_articles SET tsv= to_tsvector('simple', coalesce(title,'') || ' ' || coalesce (abstract,'')) where tsv is null; CREATE INDEX art_tsv_idx ON akb_articles USING gin(tsv); CREATE OR REPLACE FUNCTION bou_art_tsv() RETURNS trigger AS $BODY$ begin new.tsv := to_tsvector(coalesce(new.title,'')) || to_tsvector(coalesce(new.abstract,'')); return new; end CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON akb_articles FOR EACH ROW EXECUTE PROCEDURE bou_art_tsv(); Now I can do build and execute a query like this: q = "akb_articles.tsv@@'%s'" s = 'water&(navors | research)' query = q % s l= db(query).select(db.akb_articles.title) But I cannot do ====================================================== In [48]: fields = [db.akb_articles.title] fields = [db.akb_articles.title] In [50]: b = SQLFORM.grid(query, fields=fields) b = SQLFORM.grid(query, fields=fields) --------------------------------------------------------------------------- AttributeError Traceback (most recent call last) /home/js/web2py/applications/akb/models/wbank.py in <module>() ----> 1 b = SQLFORM.grid(query, fields=fields) /home/js/web2py/gluon/sqlhtml.pyc in grid(query, fields, field_id, left, headers, orderby, groupby, searchable, sortable, paginate, deletable, editable, details, selectable, create, csv, links, links_in_grid, upload, args, user_signature, maxtextlengths, maxtextlength, onvalidation, oncreate, onupdate, ondelete, sorter_icons, ui, showbuttontext, _class, formname, search_widget, ignore_rw, formstyle, exportclasses, formargs, createargs, editargs, viewargs) 1543 1544 from gluon import current, redirect -> 1545 db = query._db 1546 T = current.T 1547 request = current.request AttributeError: 'str' object has no attribute '_db' ===================================================================== Is there a way to get this type of query to work with SQLFORM.grid? Regards Johann --
