MVA attributes

[Ahsan Ali]

Hi all,

I'm having trouble with multi-value attributes ....

class Title
  define_index do
    indexes [name, subtitle], :as => :complete_title, :sortable => true
    indexes [isbn, isbn10], :as => :isbn_ref
    has categorizations.category.bic_code
  end
end

The generated sql query is:

----sql_query        = SELECT `titles`.`id` * 2 + 1 AS `id` , CAST(CONCAT_WS(' ', `titles`.`name`, `titles`.`subtitle`) AS CHAR) AS `complete_title`, CAST(CONCAT_WS(' ', `titles`.`isbn`, `titles`.`isbn10`) AS CHAR) AS `isbn_ref`, IFNULL(CONCAT_WS(' ', `titles`.`name`, `titles`.`subtitle`), '') AS `complete_title_sort`, GROUP_CONCAT(`categories`.`bic_code` SEPARATOR ' ') AS `bic_code`, `titles`.`id` AS `sphinx_internal_id`, 3942078319 AS `class_crc`, '3942078319' AS `subclass_crcs`, 0 AS `sphinx_deleted` FROM titles    LEFT OUTER JOIN `categorizations` ON categorizations.title_id = titles.id   LEFT OUTER JOIN `categories` ON `categories`.id = `categorizations`.category_id  WHERE `titles`.`id` >= $start   AND `titles`.`id` <= $end    GROUP BY `titles`.`id`  ORDER BY NULL
sql_query_range  = SELECT MIN(`id`), MAX(`id`) FROM `titles`
sql_query_info   = SELECT * FROM `titles` WHERE  `id` = (($id - 1) / 2)
sql_attr_str2ordinal = complete_title_sort
  sql_attr_multi       = uint bic_code from field
  sql_attr_uint        = sphinx_internal_id
  sql_attr_uint        = class_crc
  sql_attr_multi       = uint subclass_crcs from field
  sql_attr_uint        = sphinx_deleted

}
----

While indexing, note that Mvalues == 0:

---
indexing index 'author_core'...
collected 2228 docs, 0.0 MB
collected 0 attr values
sorted 0.0 Mvalues, 100.0% done
sorted 0.0 Mhits, 100.0% done
total 2228 docs, 29897 bytes
total 0.052 sec, 570183.46 bytes/sec, 42491.51 docs/sec
distributed index 'author' can not be directly indexed; skipping.
indexing index 'title_core'...
collected 2630 docs, 0.1 MB
collected 0 attr values
sorted 0.0 Mvalues, 100.0% done
sorted 0.0 Mhits, 100.0% done
total 2630 docs, 145473 bytes
total 0.050 sec, 2937780.18 bytes/sec, 53112.00 docs/sec
distributed index 'title' can not be directly indexed; skipping.
---

Title.search(:with => {:bic_code => 'F'}) returns nothing. (where a bic_code is = "F FG TR T")

The generated sql contains uint bic_code - perhaps that's my problem ? Does MVA support strings via str2ordinal ?

Thanks,

highandwild / Ahsan Ali

[Pat Allan]

Hi Ahsan

Unfortunately, MVA's are really only useful for integers at the moment in Sphinx - arguably for boolean and timestamp values as well, but that's not officially supported (I think) - and TS needs some work to make sure the workarounds are okay anyway. Definitely not strings, anyway.

If you change it to a field, could you do something more like the following?

Title.search :conditions => {:bic_code => "F | FG | TR | T"}

-- 

Pat

[Ahsan Ali]

Hi Pat,

Hmm, I want to search by one attribute only: :conditions => {:bic_code => 'F'} ... How does that work in the search ? Does it match 'FD' too ? (It shouldn't :D)

I could also index the ids of the categories, and while searching do a Category.find() before I called TS ..... O(n+1) I guess ...

- highandwild

[Pat Allan]

On 03/11/2008, at 1:10 PM, Ahsan Ali wrote:

Hmm, I want to search by one attribute only: :conditions => {:bic_code => 'F'} ... How does that work in the search ? Does it match 'FD' too ? (It shouldn't :D)

If you're not using min_prefix_len, then it should be okay... although the only catch is that Sphinx likes to get smart about english, and thus makes guesses about plurals, etc. (So if there's F and FS, F *might* match both).

I could also index the ids of the categories, and while searching do a Category.find() before I called TS ..... O(n+1) I guess ...

This would definitely be the better option, and then you can have those ids as an MVA instead.