On Sat, May 7, 2016 at 12:48 AM, Mike Bayer <[email protected]> wrote:
> > > On 05/06/2016 02:31 AM, Dev Mukherjee wrote: > >> On Thu, May 5, 2016 at 11:54 PM, Mike Bayer <[email protected] >> <mailto:[email protected]>> wrote: >> >> most expedient way would be to use CREATE INDEX and specify it >> within DDL(): >> >> ddl = DDL("CREATE FULLTEXT INDEX some_idx ON table(colname)") >> >> you can then just invoke it: >> >> >> conn.execute(ddl) >> >> >> Ah OK. That will ensure that we don't miss out on creating the indexes. >> >> I did find your post on Stackoverflow answering the same question >> >> http://stackoverflow.com/questions/14971619/proper-use-of-mysql-full-text-search-with-sqlalchemy >> >> Been querying like so >> >> session.query(myapp.models.Customer).filter(text("MATCH(name) AGAINST >> (\"[:value]\" IN BOOLEAN MODE)", bindparams=[bindparam("value", >> ["+Anomaly", "+soft"])])).all() >> >> Can I make this better or this should get me out of trouble for now? >> > > That array value is odd, I'm not sure what that does, if I'm understanding > correctly you can use our own match() operator and just pass that as one > string: > > session.query(Customer).filter(Customer.name.match("+Anomaly +soft")) > > I swear I looked around for a match operator before I asked :- > I can't find in MySQL's docs what the brackets here mean but you can add > those to the literal match value. I've updated the SO answer to include > that for a single column, just use match(). > > > If I create the full text and include multiple fields in the index CREATE FULLTEXT INDEX `keyword_search` ON customer(name,physical_address_street,physical_address_suburb, physical_address_state,physical_address_post_code); I can only execute a search with the same set of fields SELECT * FROM customer WHERE MATCH(name,physical_address_street,physical_address_suburb,physical_address_state, physical_address_post_code) AGAINST ("+Anomaly" IN BOOLEAN MODE); If I try this in SQLAlchemy session.query(Customer).filter(Customer.name.match("+Anomaly"), Customer.physical_address_street.match("+Anomaly"),Customer.physical_address_suburb.match("+Anomaly"),Customer.physical_address_state.match("+Anomaly"),Customer.physical_address_post_code.match("+Anomaly")).all() MySQL complains about no matching column list for Full text search. Is is possible to express multiple fields in the match operator? Or should I not be creating a full text index amongst multiple fields? PS for my use case I want to search against all the fields. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
