[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
I have site in production right now that is using tsearch2. What I
did to accommodate the results with SA was to simply not map the
tsearch2 column to the SA object. I have a view that creates the
tsvector objects based on the source table. (I actually created a
materialized view and indexed it if you wanted to google for
materialized views and PG),
Next, I simply join the SA object with a custom query when I do the
search.
Example
tsearch=engine.text('my tsearch query that returns the ids of the
objs').
If you want objects returned, you can use the mapper.instances
function
myobj=MyObj.mapper.instances(tsearch.execute())
-Dennis
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
Thanks a lot, guys. Will try a sample application and post in the wiki. Might take some time - meanwhile, if somebody gets some more stuff, posting it would help. thankssanjay Really sorry - I could not touch this yet. Have got suddenly busy in preparing for some training, which suddenly creeped in. It might take some more time. Till then if somebody does this, kindly post in the wiki. thanks Sanjay --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
Thanks a lot, guys. Will try a sample application and post in the wiki. Might take some time - meanwhile, if somebody gets some more stuff, posting it would help. thanks sanjay --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
On Thursday 18 January 2007 07:11, Sanjay wrote: Hi All, Trying to implement full text search with PostgreSQL and tsearch2, being a beginner, I am facing some basic hurdles: 1. How to declare a table in SA, I mean, what SA datatype should tsvector correspond to? 2. Can the index be defined using SA, or we need to do it in the backend? 3. Can the trigger for auto update be defined using SA, or we need to do it in the backend? 4. How to frame the query in SA? SA doesn't support tsvectors, as far as I know. The simplest thing in my mind is to build one table that contains the tsvector (and is not mapped to SA) and a key to the original record. Then, you can do whatever queries you like (using the underlying database engine) to get keys to the data in the source table and use SA to grab these data. Not ideal, but I think it will work. Alternatively, you can create a new type for SA; there is a recent message in the archives about doing that (not with tsvector, but). As for querying, I'm not sure how that would be best implemented in SA. Sean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
i dont know much about tsvector, but as to the various can the trigger/index be defined in SA, no, you have to use literal postgres commands to do that (which you can of course issue from SA as textual statements). since they are pg-specific anyway, theres not much advantage to SA supporting expressions for those. as to the datatype, create your own datatype. as to the query, i dont know what a tsvector query looks like so youd have to show me that. if it involves special operators, use the op() method on the column. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
I'd be interested in how you work this out as I want to do something similar. Would you be willing to write it up and perhaps post it to the wiki? I was able to get MySQL's fulltext search working more quickly than PostgreSQL's and that's what my customer is used to so that's what I'm going to use for them. I haven't yet gotten to integrate it with SA. Down the road, I'd like to know how to do the same with PG. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
On Thursday 18 January 2007 14:19, Chris Shenton wrote: I'd be interested in how you work this out as I want to do something similar. Would you be willing to write it up and perhaps post it to the wiki? I was able to get MySQL's fulltext search working more quickly than PostgreSQL's and that's what my customer is used to so that's what I'm going to use for them. I haven't yet gotten to integrate it with SA. Down the road, I'd like to know how to do the same with PG. See here for at least an overview http://www.devx.com/opensource/Article/21674 There are other similar sites available via google. Sean --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
