Hi, I need a little bit of support again if you can.
This is my situation: - digitalassets (table) / Digitalasset (Model) - products (table) / Product (Model) - product_digitalassets (table) / ProductDigitalasset (Model) class ProductDigitalasset < ActiveRecord::Base attr_accessible :digitalasset_id, :product_id, :code_12nc belongs_to :digitalasset belongs_to :product end The Join table above has also the code_12nc that I need other than the two foreign keys How can I set a correct attribute in order to filter by the 12NC (product)? I've several attributes like: has .. has ... What should I put at the end of the list? *has :product_digitalassets.code_12nc* not works. I red the documentation and I've tried different combinations but I'm not able to understand what I've to do exactly to catch the values in the join table. Thanks again. Yuri. Il giorno martedì 14 gennaio 2014 14:43:21 UTC+1, [email protected] ha scritto: > > Hi Pat, > > yes there is a join table in order to link the digitalassets table and the > products (12NC) table. > > I was wrong. It's a Many to Many association and not 1 to Many but I think > there's no difference about the problem. > > Thank you for your clarification about the search against Sphinx and not > MySQL. I will try to adopt your suggestion. > > Yuri. > > Il giorno martedì 14 gennaio 2014 13:00:46 UTC+1, Pat Allan ha scritto: >> >> Hi Yuri >> >> When you run the ts:index (or ts:rebuild) rake task, the Sphinx indexer >> captures all the data from your MySQL database - search queries then talk >> to Sphinx, not to MySQL. So, there’s no SQL query for performing the search >> (though Sphinx has its own query language called SphinxQL, which is almost >> identical to SQL, but it’s for talking to Sphinx, not databases). >> >> With your has_12ncs string - is this also in the database as foreign keys >> via a has_many association? If so, it’ll very likely be better to create an >> attribute in your Sphinx index which stores those foreign keys and then you >> can filter on that. >> >> Also, perhaps you’re already aware, but for Sphinx, fields and attributes >> are different things: >> http://pat.github.io/thinking-sphinx/sphinx_basics.html >> >> — >> Pat >> >> On 14 Jan 2014, at 10:55 pm, [email protected] wrote: >> >> Thank you for your answer. >> >> I pass a :conditions like this: >> >> *conditions: { has_12ncs: _12nc_string }* >> >> where *has_12ncs* is a *TEXT* field in MySQL filled with a denormalized >> structure of 1 to Many relationship between Digitalassets record and 12NC >> (product) >> >> So the value in *has_12ncs* field can be *,123,456,789,* >> >> the string passed to conditions: { has_12ncs: _12nc_string } is something >> like: >> >> ",851322311100, | ,851304211100, | ,851340001000, | ,851343701000, | >> ,851343801000, | ,851343901000, | ,851344001000, | ,858758938790, | >> ,858759001790, | ,858759038790, | ,858759701790, | ,851343801020, | >> ,851343701020, | ,851344001020, | ,851343901020, | ,858759901740, | >> ,858759701740, | ,857542038000, | ,857594938000, | ,857542438000, | >> ,850120596000, | ,850120696000, | ,850120796000, | ,850122601020, | >> ,850122701000, | ,850122701010, | ,850122801000, | ,850122801020, | >> ,851315001000, | ,851321801000, | ,851535101040, | ,858600015000, | >> ,858600096000, | ,858600096010, | ,850122901010, | ,850123001000, | >> ,850123001020, | ,851315001010, | ,850123001010, | ,851328301000, | >> ,851328301010, | ,851328401010, | ,851385101080, | ,854001638000, | >> ,854071038080, | ,854071038090, | ,854074638070, | ,856070001080, | >> ,856079838070, | ,857532008400, | ,857532108400, | , ," >> >> That field has a FULLTEXT index. >> >> What's the query SQL triggered against this field and values? I'm not >> able to print the pure SQL. >> >> Thanks again. >> >> >> Il giorno martedì 14 gennaio 2014 10:46:14 UTC+1, Pat Allan ha scritto: >>> >>> Hi Yuri >>> >>> I don’t think I’ve come across this problem before - that’s a *really* >>> slow query! >>> >>> Can you share your index definition with us? And are you also passing in >>> a search term, or field/attribute filters (the :conditions and :with >>> options)? >>> >>> -- >>> Pat >>> >>> On 14 Jan 2014, at 8:40 pm, [email protected] wrote: >>> >>> > Hi all, >>> > for first sorry for my english. >>> > >>> > I've got a problem with RoR and Thinking Sphinx gem. >>> > >>> > I mean: everything works fine but when I try to get a search result >>> > passing a lot of parameter values through Active Record and TS API my >>> > application slow down and become unusable. >>> > >>> > I need to send queries on a MyISAM table with over 600.000 records >>> > >>> > There's a query field that I can match with 0 or N values >>> > If I set 0 or few values the query is fast. >>> > If I set 1000-2000 or more values for this field the query occurs over >>> >>> > 10 minutes. >>> > >>> > I'm not sure about the query slowness because the problem occurs only >>> > when I try to get (for example) the total_count value. >>> > >>> > The query is something like: >>> > Model.search match_mode: :extended, page: page, per_page: per_page, >>> > :order => :updated_at, :sort_mode => :desc >>> > >>> > If I look in the searchd.query.log file I can find this informations >>> > >>> > [Tue Jan 14 09:42:08.394 2014] 774.749 sec [ext/2/attr- 64637 (0,20)] >>> > [digitalasset_core,digitalasset_delta] @has_countries ,98, | ,88, | >>> > ,ALL, | , , @has_12ncs ,851322311100, | ,851304211100, | ,851340001000, >>> >>> > | ,851343701000, | ,851343801000, | ,851343901000, | ,851344001000, | >>> > ,858758938790, | ,858759001790, | ,858759038790, | ,858759701790, | >>> > ,851343801020, | ,851343701020, | ,851344001020, | ,851343901020, | >>> > ,858759901740, | ,858759701740, | ,857542038000, | ,857594938000, | >>> > ,857542438000, | ,850120596000, | ,850120696000, | ,850120796000, | >>> > ,850122601020, | ,850122701000, | ,850122701010, | ,850122801000, | >>> > ,850122801020, | ,851315001000, | ,851321801000, | ,851535101040, | >>> > ,858600015000, | ,858600096000, | ,858600096010, | ,850122901010, | >>> > ,850123001000, | ,850123001020, | ,851315001010, | ,850123001010, | >>> > ,851328301000, | ,851328301010, | ,851328401010, | ,851385101080, | >>> > ,854001638000, | ,854071038080, | ,854071038090, | ,854074638070, | >>> > ,856070001080, | ,856079838070, | ,857532008400, | ,857532108400, | >>> > ,857565108200, | ,857565738010, | ,857575738010, | ,857576838010, | >>> > ,857582338080, | ,857584738000, | ,857586738000, | ,857586838080, | >>> > ,857588838080, | ,857597838010, | ,857599938030, | ,854060038010, | >>> > ,854070038010, | ,854080038010, | ,857580000110, | ,856087638010, | >>> > ,857597838020, | ,857586838020, | ,857576838020, | ,854076738020, | >>> > ,857597838030, | ,857587838010, | ,856010038020, | ,856087838020, | >>> > ,857500038020, | ,857500138020, | ,857500238020, | ,857500338020, | >>> > ,854020038020, | ,856010048020, | ,856010058020, | ,857500348020, | >>> > ,857500358020, | ,857500368020, | ,857580001100, | ,851330038000, | >>> > ,851330138000, | ,851330238000, | ,851330338000, | ,851330438000, | >>> > ,851330538000, | ,851330601000, | ,851330701000, | ,851330801000, | >>> > ,851330901000, | ,851331101000, | ,851331201000, | ,851331301000, | >>> > ,851331401000, | ,851331501000, | ,851331838000, | ,851331938000, | >>> > ,851338038000, | ,851338138000, | ,851338201000, | ,851338301000, | >>> > ,851338401000, | ,851338501000, | ,851345638000, | ,851345738000ÿ >>> > >>> > the 12NCS are the values that I want to match in order to limit the >>> > resultset. >>> > >>> > Can anybody help me? >>> > Thanks in advance. >>> > >>> > >>> > -- >>> > You received this message because you are subscribed to the Google >>> Groups "Thinking Sphinx" 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 >>> > thinkin...@googlegroups.<http://googlegroups.com/> >>> com <http://googlegroups.com/>. >>> > Visit this group at http://groups.google.com/group/thinking-sphinx. >>> > For more options, visit https://groups.google.com/groups/opt_out. >>> >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "Thinking Sphinx" 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 http://groups.google.com/group/thinking-sphinx. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" 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 http://groups.google.com/group/thinking-sphinx. For more options, visit https://groups.google.com/groups/opt_out.
