Search code examples
sqlruby-on-railsthinking-sphinx

Thinking Sphinx ts:configure generates invalid SQL in sphinx.conf after upgrade

I'm in the process of upgrading a Rails app from 3.2.6 to 3.2.16. The app uses Thinking Sphinx, and in our Company model we used to have the following define_index block

define_index do
    indexes :reg_no, :sortable => true
    indexes :org_no, :sortable => true
    indexes :name, :sortable => true
    indexes :classification_code
    indexes address(:address1), :as => :address1
    indexes address(:address2), :as => :address2
    indexes address(:address3), :as => :address3
    indexes address(:city), :as => :city
    indexes address.postal_area(:code), :as => :postal_code
    indexes address.postal_area(:name), :as => :postal_area_name
    indexes address.country(:name), :as => :country_name
    indexes address.country(:iso2), :as => :country_iso2
    indexes address.country(:iso3), :as => :country_iso3

    has end_date
    set_property :delta => :delayed
 end

which generated a valid development.sphinx.conf.

I have extracted the index definition into indices/company_index.rb as described in the upgrade documentation:

ThinkingSphinx::Index.define :company, :with => :active_record, :delta => ThinkingSphinx::Deltas::DelayedDelta do
    indexes :reg_no, :sortable => true
    indexes :org_no, :sortable => true
    indexes :name, :sortable => true
    indexes :classification_code
    indexes address(:address1), :as => :address1
    indexes address(:address2), :as => :address2
    indexes address(:address3), :as => :address3
    indexes address(:city), :as => :city


    indexes address.postal_area(:code), :as => :postal_code
    indexes address.postal_area(:name), :as => :postal_area_name
    indexes address.country(:name), :as => :country_name
    indexes address.country(:iso2), :as => :country_iso2
    indexes address.country(:iso3), :as => :country_iso3
    indexes address.country(:iso3), :as => :country_iso3

    has end_date
    set_property :delta => :delayed 
end

Before the upgrade the application used Thinking Sphinx version 2.0.13 and Sphinx 0.99, which now is updated to use 3.1.0 and 2.1.4-release.

The ts:configure generates development.sphinx.conf: which among other things contains this line:

sql_query = SELECT SQL_NO_CACHE
        `companies`.`id` * 3 + 0 AS `id`,
        `companies`.`reg_no` AS `reg_no`,
        `companies`.`org_no` AS `org_no`,
        `companies`.`name` AS `name`,
        `companies`.`classification_code` AS `classification_code`,
        addresses.`address1` AS `address1`,
        addresses.`address2` AS `address2`,
        addresses.`address3` AS `address3`, 
        addresses.`city` AS `city`,
        AS `postal_code`,  AS `postal_area_name`,  AS `country_name`,  AS `country_iso2`,  AS `country_iso3`,  AS `country_iso3`, `companies`.`id` AS `sphinx_internal_id`, 'Company' AS `sphinx_internal_class`, 0 AS `sphinx_deleted`,
        UNIX_TIMESTAMP(`companies`.`end_date`) AS `end_date`
    FROM `companies`
    LEFT OUTER JOIN `addresses` ON `addresses`.`id` = `companies`.`preferred_address_id`
    WHERE (`companies`.`id` BETWEEN $start AND $end)
    GROUP BY
        `companies`.`id`,`companies`.`reg_no`, `companies`.`org_no`, `companies`.`name`, `companies`.`classification_code`,
        addresses.`address1`, addresses.`address2`, addresses.`address3`,
        addresses.`city`, , , , , , , `companies`.`id`, `companies`.`end_date`
    ORDER BY NULL

It seems that associations deeper than one is generating invalid SQL. I have read the documentation but I can't find anything that gives a hint about what I'm doing wrong.

Solution

  • I'm not sure if this is the cause, but can you try changing this:

    indexes address.postal_area(:code), :as => :postal_code
indexes address.postal_area(:name), :as => :postal_area_name
indexes address.country(:name), :as => :country_name
indexes address.country(:iso2), :as => :country_iso2
indexes address.country(:iso3), :as => :country_iso3
indexes address.country(:iso3), :as => :country_iso3

    To this:

    indexes address.postal_area.code, :as => :postal_code
indexes address.postal_area.name, :as => :postal_area_name
indexes address.country.name, :as => :country_name
indexes address.country.iso2, :as => :country_iso2
indexes address.country.iso3, :as => :country_iso3
indexes address.country.iso3, :as => :country_iso3

    Besides that, if columns are missing in the generated SQL, that generally means that the association or column names are wrong.

    Also: setting the delta via set_property does nothing - but you are also setting it correctly at the top of the index. This is unrelated to the problem you're seeing, though.