In the meantime I've solved the problem by using associations directly... indexes station.categories.name, :as => 'index_category_names' indexes station.categories.abbreviation, :as => 'index_category_abbreviations' indexes station.categories.parent.parent.name, :as => 'index_state_name' indexes station.categories.parent.parent.abbreviation, :as => 'index_state_abbrevation'
categories is a polymorphic acts_as_tree of with "country -> state -> municipality -> city" where my records have a classification of one city, but I also want to index on the name of the state (but not the municipality). The parent.parent association is used to traverse up the categories tree from city to state. On 2 dec, 19:32, Klaas Jan Wierenga <[EMAIL PROTECTED]> wrote: > Surely this won't work, since it will result in a WHERE clause > starting with "LEFT OUTER JOIN".... > > I know that the SQL that TS produces in development.sphinx.conf is OK. > If I copy paste it and run it against the database by hand, it > produces the expected result, i.e. a text column with the words > separated by spaces. > > Op 2 dec 2008, om 17:37 heeft Henrik Nyh het volgende geschreven: > > > > > Possibly it works better with something like > > > define_index do > > indexes "(SELECT GROUP_CONCAT(s.name SEPARATOR ' ') FROM assets AS > > a", :as => 'state_classification' > > where "LEFT OUTER JOIN ... all the rest" > > end > > > Have a look in the config/<environment>.sphinx.conf file that Thinking > > Sphinx generates when you run the rake tasks. That will show the full > > SQL query used. > > > On Tue, Dec 2, 2008 at 5:21 PM, KJ <[EMAIL PROTECTED]> wrote: > > >> I'm trying a rather complicated sql fragment with the indexes method > >> of the Builder: > > >> indexes "(SELECT GROUP_CONCAT(s.name SEPARATOR ' ') > >> FROM assets AS a > >> LEFT OUTER JOIN classifications AS cl ON (a.station_id = > >> cl.station_id) > >> LEFT OUTER JOIN categories as c on (cl.category_id = c.id) > >> LEFT OUTER JOIN categories as m on (c.parent_id = m.id) > >> LEFT OUTER JOIN categories as s on (m.parent_id = s.id) > >> WHERE a.id = assets.id > >> )", :as => 'state_classification' > > >> The SQL produces a comma separated list of words on which the record > >> needs to be indexed, but searching on these words does not produce > >> the > >> records. > > >> Indexing with 'rake ts:index' works fine (no errors) and I restart > >> searchd to make sure. > > >> Is this supported in thinking sphinx? The example from the > >> documentation 'indexes "age < 18", :as => :minor' is rather simple > >> and > >> returns a boolean, so are strings not supported for indexing? > > >> Am I missing something? Any help would be appreciated. > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en -~----------~----~----~----~------~----~------~--~---
