Problem with has_one and :with

[elanderholm]

So i have a large table that i don't want to add another column too.  So i made a has_one relationship to another table to hold some interesting info about some of the things in the main table.

I have a vehicle table that has_one used_vehicle_attribute (used_vehicle_attributes table).

indexes on used_vehicle.rb, which is an sti model of vehicle table

  has_one    :used_vehicle_attribute, :foreign_key => :vehicle_id

  ### Validations

  #validates_presence_of :buyer_id, :message => "No buyer ID."

  define_index "used_vehicle" do

    indexes make_name

    indexes model_name

    indexes type

    indexes dealership.zip_code.state_abbr

    has mileage

    has dealership.stars , :as => :stars

    has dealership_id

    has year

    has sticker_price, :type => :float

    has used_vehicle_attribute.trim_description_id, :as => :trim_description_id

    has 'RADIANS(zip_codes.latitude)',  :as => :latitude,      :type => :float

    has 'RADIANS(zip_codes.longitude)', :as => :longitude,     :type => :float

    set_property :latitude_attr   => "latitude"

    set_property :longitude_attr  => "longitude"

  end

The trim_description_id is present in the join created in development.sphinx.conf on a rebuild.  used_vehicle_attributes is left outer joined with vehicles using vehicle_id so that all works fine.

A group by trim_description_id sort of works though the counts and things seem to be off, but it seems to recognize this attribute.

What doesn't work is this:

 UsedVehicle.search("Accord",:with => {:trim_description_id => 10998189385240095927},:per_page => 500)

That is a singular trim_description_id that is in the used_vehicle_attributes table and shows up when i do this:

 UsedVehicle.search("Accord",:per_page => 500)

The column trim_description_id is an big unsigned int.

mysql> desc used_vehicle_attributes;

+---------------------+---------------------+------+-----+---------+----------------+

| Field               | Type                | Null | Key | Default | Extra          |

+---------------------+---------------------+------+-----+---------+----------------+

| id                  | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |

| vehicle_id          | bigint(20)          | YES  | MUL | NULL    |                |

| trim_description_id | bigint(20) unsigned | YES  |     | NULL    |                |

| created_at          | datetime            | NO   |     | NULL    |                |

| updated_at          | datetime            | NO   |     | NULL    |                |

I even tried adding a column that had some text in it and making an index definition for that thinking maybe i couldn't have an attribute without an index, but that still didn't work.

Here are my attributes in my development.sphinx.conf file

  sql_range_step = 16777216

  sql_attr_uint = sphinx_deleted

  sql_attr_uint = class_crc

  sql_attr_uint = mileage

  sql_attr_uint = year

  sql_attr_bigint = sphinx_internal_id

  sql_attr_bigint = dealership_id

  sql_attr_bigint = trim_description_id

  sql_attr_float = stars

  sql_attr_float = sticker_price

  sql_attr_float = latitude

  sql_attr_float = longitude

  sql_query_info = SELECT * FROM `vehicles` WHERE `id` = (($id - 9) / 12)

thanks.

Erik

[elanderholm]

Sorry I forgot to add that i'm also using

UsedVehicle.search("Accord",:with => {:trim_description_id => 10998189385240095927},:index => "used_vehicle_core",:per_page => 50)

i'm using the index specifically used_vehicle_core which i use for all my used car searches.

Thanks

Erik

[elanderholm]

It looks like the number is too big.  I made the ids for trim_description_id smaller and now it works...interesting....

thanks.

erik

On Thursday, June 28, 2012 2:44:02 PM UTC-7, elanderholm wrote:

[Pat Allan]

Hi Erik

That is indeed a pretty massive number - although smaller than 2^64, and Sphinx has figured out it's a bigint rather than a normal 32-bit int. It could be that Sphinx stores bigints as signed (meaning max would be 2^63, a bit saved for the +/- sign), and the integer you've used is above that point - but that would be surprising, given Sphinx uses unsigned 32-bit ints.

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/deAIbcgzP4oJ.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.