Hi Pat,
I've tried to make this modification.
Add the attribute:
*has product_digitalassets.product_id, as => :product_ids*
and changed the scope method in this way:
*sphinx_scope :with_12_nc*
*....*
*with: { product_ids: **ids** }*
*....*
*end*
ids = Array of *product_id* (Integer) and not *product_12NC* (string)
the M:N join table *product_digitalassets* has these fields
*id (primary key)*
*product_id*
*code_12nc*
*digitalasset_id*
I've executed the task *rake ts:rebuild *and everything seems ok but
unfortunately I've no matches in my result.
Is it possibile to search through the foreign key product_id and not
primary key id of join table? Or is this the problem?
Thanks again.
Yuri.
Il giorno mercoledì 15 gennaio 2014 13:23:42 UTC+1, Pat Allan ha scritto:
>
> Hi Yuri
>
> The attribute should probably be of the id column, as that’s an integer
> (but I presume code_12nc is a string? Sphinx doesn’t support multi-value
> string attributes, nor does it support filtering on single-value string
> attributes).
>
> So:
>
> has product_digitalassets.id, :as => :product_digitalasset_ids
>
> And then in your search, if you don’t have the id values, you’ll need to
> translate 12NC codes to the ids:
>
> ids = ProductDigitalasset.where(:code_12nc => params[:codes]).pluck(:id)
>
> And then searching:
>
> DigitalAsset.search :with => {:product_digitalasset_ids => ids}
>
> Does this make sense?
>
> --
> Pat
>
> On 15 Jan 2014, at 10:31 pm, [email protected] <javascript:> wrote:
>
> 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]
> 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] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> 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.
