SQLalchemy, PostgreSQL/tsearch/ts_rank_cd

[Adam Tauno Williams]

I'm stumped how to build the following query in SQLalchemy:

SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC LIMIT 10;

I can do tsearch operations; but I can't figure out the syntax for this
non-join FROM clause.

If I setup using ...

class TextSearchVector(types.UserDefinedType):
def get_col_spec(self):
return 'tsvector'

class SearchVector(ORMEntity):
__tablename__ = 'vista_vectors'
object_id = Column('object_id', Integer, primary_key=True)
entity = Column('entity', String)
version = Column('version', Integer)
event_date = Column('version', UTCDateTime)
keywords = Column('keywords', ARRAY(String))
vector = Column('vector', TextSearchVector,
nullable=False)

... then I can query with the following -

tsq = func.to_tsquery('english', 'adam')
z = db.query(SearchVector.object_id).filter(SearchVector.vector.op('@@'
)(tsq)).all()

But -
z = db.query(SearchVector.object_id,
func.ts_rank_cd(SearchVector.vector, tsq)).\
filter(SearchVector.vector.op('@@' )(tsq)).all()
- doesn't produce a query with ranking [ the FROM clause only mentions
the table ].

And -
z = db.query(SearchVector.object_id,
func.ts_rank_cd(SearchVector.vector, tsq)).\
join(tsq),
filter(SearchVector.vector.op('@@' )(tsq)).all()
- fails (rather expectedly) with a no-relationship between a & b error.

How does one insert a function call into the FROM???

[Michael Bayer]

On Dec 23, 2011, at 9:32 PM, Adam Tauno Williams wrote:

> I'm stumped how to build the following query in SQLalchemy:
>
> SELECT title, ts_rank_cd(textsearch, query) AS rank
> FROM apod, to_tsquery('neutrino|(dark & matter)') query
> WHERE query @@ textsearch
> ORDER BY rank DESC LIMIT 10;
>
> I can do tsearch operations; but I can't figure out the syntax for this
> non-join FROM clause.

That exact statement appears to be a PG-specific format I've never seen before. "query" is declared as a table alias yet referenced as a column expression in the WHERE and columns clause.

A simple statement such as:

test=> select foo FROM lower('HI') foo WHERE foo = 'HI';
foo
-----
(0 rows)

fine in PG. But SQLite:

sqlite> select foo FROM lower('HI') foo WHERE foo = 'HI';
Error: near "(": syntax error
sqlite>

MySQL:

mysql> select foo FROM lower('HI') foo WHERE foo = 'HI';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('HI') foo WHERE foo = 'HI'' at line 1
mysql>

So problem #1 is that this is some nonstandard Postgresql format. Normally the way this is done is:

SELECT lower('HI') WHERE lower('HI') = 'hi'

that is, state it twice.

Or you can select from a subquery:

SELECT foo FROM (select lower('HI') AS foo) as anon where anon.foo='hi'

that's likely what FROM <function> <label> evaluates to in PG.

So here you can set up the statement as a FROM:

subq = select([func.to_tsquery('neutrino|(dark & matter)').label('value')]).alias('ts')

Then refer to subq as another selectable:

q = s.query(
SearchVector.title,
func.ts_rank_cs(SearchVector.vector, subq.c.value)
).\
filter(subq.c.value.op('&&')(SearchVector.vector))

> ... then I can query with the following -
>
> tsq = func.to_tsquery('english', 'adam')
> z = db.query(SearchVector.object_id).filter(SearchVector.vector.op('@@'
> )(tsq)).all()
>
> But -
> z = db.query(SearchVector.object_id,
> func.ts_rank_cd(SearchVector.vector, tsq)).\
> filter(SearchVector.vector.op('@@' )(tsq)).all()
> - doesn't produce a query with ranking [ the FROM clause only mentions
> the table ].
>
> And -
> z = db.query(SearchVector.object_id,
> func.ts_rank_cd(SearchVector.vector, tsq)).\
> join(tsq),
> filter(SearchVector.vector.op('@@' )(tsq)).all()
> - fails (rather expectedly) with a no-relationship between a & b error.
>
> How does one insert a function call into the FROM???
>
>
>
>
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>