Ah... found it.
The problem is your extra conditions on the has_one associations... for both
brand and model you refer to the product_attributes table - but this table is
joined to twice in the Sphinx SQL query, and so gets aliased as models_products
on the second join.
However, you can't work around this by just using the alias in the extra
conditions - because then you won't be able to use the association within your
own code.
I guess one way around this is to create a duplicate of the association, and
use that just for index definition? Not absolutely sure it'll work, but worth a
try:
has_one :ts_model, :class_name => "AttributeProduct", :conditions =>
"model_products.attribute_id IN (Select id from attributes where name =
'Model')"
# in your index:
indexes ts_model(:attribute_value), :as => :model, :facet => true
Let us know how it goes.
Cheers
--
Pat
On 09/07/2010, at 2:00 PM, gaurav wrote:
> Hey Pat,
>
> Thanks for responding. Here is the sql that is getting generated:
>
> SELECT SQL_NO_CACHE `products`.`id` * 1 + 0 AS `id` ,
> `products`.`name` AS `name`, `products`.`custom_name` AS
> `custom_name`, `products`.`description` AS `description`,
> `products`.`custom_description` AS `custom_description`,
> `product_attributes`.`attribute_value` AS `brand`,
> `models_products`.`attribute_value` AS `model`, `products`.`id` AS
> `sphinx_internal_id`, 485965105 AS `class_crc`, 0 AS `sphinx_deleted`,
> CRC32(`product_attributes`.`attribute_value`) AS `brand_facet`,
> CRC32(`models_products`.`attribute_value`) AS `model_facet`,
> `amazon_stores`.`id` AS `amazon_store_id`,
> `products`.`product_category_id` AS `category_id` FROM `products`
> LEFT OUTER JOIN `product_attributes` ON product_attributes.product_id
> = products.id AND product_attributes.attribute_id IN (Select id from
> attributes where name = 'Brand') LEFT OUTER JOIN
> `product_attributes` models_products ON models_products.product_id =
> products.id AND product_attributes.attribute_id IN (Select id from
> attributes where name = 'Model') LEFT OUTER JOIN
> `product_categories` ON `product_categories`.id =
> `products`.product_category_id LEFT OUTER JOIN `amazon_stores` ON
> `amazon_stores`.id = `product_categories`.amazon_store_id WHERE
> `products`.`id` >= $start AND `products`.`id` <= $end AND
> products.online = 1 AND product_attributes.attribute_id IN
> (SELECT id FROM attributes WHERE name IN ('Brand', 'Model'))
> AND products.price_lowest IS NOT NULL AND
> products.product_category_id IN (SELECT id FROM product_categories
> WHERE product_categories.parent_category_id IS NOT NULL) GROUP BY
> `products`.`id` ORDER BY NULL
>
> define_index do
> indexes :name
> indexes custom_name
> indexes description
> indexes custom_description
> has product_category.amazon_store(:id), :as => :amazon_store_id
>
> indexes brand.attribute_value, :as => :brand, :facet => true
> indexes model.attribute_value, :as => :model, :facet => true
> has product_category_id, :as => :category_id, :type
> => :integer, :facet => true
>
> where "products.online = 1 AND \
> product_attributes.attribute_id IN (SELECT id FROM
> attributes WHERE name IN ('Brand', 'Model')) AND \
> products.price_lowest IS NOT NULL AND \
> products.product_category_id IN (SELECT id FROM
> product_categories WHERE product_categories.parent_category_id IS NOT
> NULL)"
>
> end
>
> O/P:
>>> Product.facets(:conditions => {:brand => "Calvin"})
> Sphinx Querying: '@brand Calvin'
> Sphinx (0.003586s) Found 1 results
> Product Load (0.2ms) SELECT * FROM `products` WHERE
> (`products`.`id` IN (46788))
> ProductAttribute Load (0.1ms) SELECT * FROM `product_attributes`
> WHERE (`product_attributes`.product_id = 46788 AND
> (product_attributes.attribute_id IN (Select id from attributes where
> name = 'Brand'))) LIMIT 1
> Sphinx Querying: '@brand Calvin'
> Sphinx (0.003979s) Found 1 results
> Product Load (0.2ms) SELECT * FROM `products` WHERE
> (`products`.`id` IN (46788))
> ProductAttribute Load (1.1ms) SELECT * FROM `product_attributes`
> WHERE (`product_attributes`.product_id = 46788 AND
> (product_attributes.attribute_id IN (Select id from attributes where
> name = 'Model'))) LIMIT 1
> Sphinx Querying: '@brand Calvin'
> Sphinx (0.004161s) Found 2 results
> Product Load (0.2ms) SELECT * FROM `products` WHERE
> (`products`.`id` IN (3787,46788))
> Sphinx Querying: '@brand Calvin'
> Sphinx (0.004333s) Found 1 results
> Product Load (8.6ms) SELECT * FROM `products` WHERE
> (`products`.`id` IN (46788))
> {:brand=>{"Calvin
> Klein"=>7}, :model=>{"U2716"=>7}, :category_id=>{6=>1, 2=>6}}
>>> Product.facets(:conditions => {:model => "U2716"})
> Sphinx Querying: '@model U2716'
> Sphinx (0.003485s) Found 0 results
> Sphinx Querying: '@model U2716'
> Sphinx (0.002976s) Found 0 results
> Sphinx Querying: '@model U2716'
> Sphinx (0.004659s) Found 0 results
> Sphinx Querying: '@model U2716'
> Sphinx (0.002868s) Found 0 results
> {:brand=>{}, :model=>{}, :category_id=>{}}
>
>
> Let me know your thoughts.
>
> Thanks
> Gaurav
>
> On Jul 7, 7:05 am, Pat Allan <[email protected]> wrote:
>> Hi Gaurav
>>
>> Something definitely is going wrong here, but it's failing even before the
>> facets - a normal search query for U2716 should return something...
>>
>> What's the sql_query value in config/development.sphinx.conf for your
>> product source?
>>
>> --
>> Pat
>>
>> On 01/07/2010, at 4:00 PM, gaurav wrote:
>>
>>> I have an application in which I need to implement the faceted search
>>> functionality on one of the fields of the associated models and it
>>> does not seem to be working. Here is a brief context: There are 3
>>> models I am working: 1. Product 2. Attributes 3. AttributeProduct
>>
>>> class Product < ActiveRecord::Base
>>> ...
>>> has_many :product_attributes
>>> has_many :prod_attrs, :through => :product_attributes, :class_name
>>> => 'Attribute'
>>
>>> has_one :brand, :class_name => "AttributeProduct", :conditions =>
>>> "product_attributes.attribute_id IN (Select id from attributes where
>>> name = 'Brand')"
>>> has_one :model, :class_name => "AttributeProduct", :conditions =>
>>> "product_attributes.attribute_id IN (Select id from attributes where
>>> name = 'Model')"
>>> ....
>>
>>> define_index do
>>> indexes :name
>>> indexes description
>>> indexes product_category(:name), :as => :sub_category
>>
>>> indexes brand(:attribute_value), :as => :brand, :facet => true
>>> indexes model(:attribute_value), :as => :model, :facet => true
>>
>>> where "products.online = 1 AND products.product_category_id IN
>>> (SELECT id FROM product_categories WHERE
>>> product_categories.parent_category_id IS NOT NULL)"
>>> end
>>
>>> end
>>> -----------------------------------------------------------------------
>>> class AttributeProduct < ActiveRecord::Base
>>> # => Since attribute is already taken, renaming the association
>>> method to prod_attr
>>> belongs_to :attribute
>>> belongs_to :product
>>> ...
>>> end
>>> ------------------------------------------------------------------
>>> class Attribute < ActiveRecord::Base
>>> has_many :attribute_products
>>> has_many :products, :through => :product_attributes
>>> ....
>>> end
>>> ------------------------------------------------------------------
>>
>>> I have defined 1:1 relationship between product and a brand & between
>>> product and a model. Then I define indexes on both and make them
>>> facets.
>>
>>> Now in the Product model, the search for the brand returns results but
>>> the search for the model name does not return any result.
>>
>>> For example:
>>> Product.search(:conditions => {:model => "U2716"})
>>> Sphinx Querying: '@model U2716'
>>> Sphinx (0.002063s) Found 0 result
>>> -------------------------------------------------------------------
>>> Product.search(:conditions => {:brand => "Calvin"})
>>> Sphinx Querying: '@brand Calvin'
>>> Sphinx (0.004142s) Found 7 results
>>> -------------------------------------------------------------------
>>
>>> In my understanding "model" facets content is getting indexed. The
>>> results are below:
>>
>>> Product.facets(:conditions => {:brand => "Calvin"})
>>> {:model=>{"U2716"=>7}, :product_category_id=>{6=>1,
>>> 2=>6}, :cost=>{1=>3, 2=>4}, :brand=>{"Calvin Klein"=>7}}
>>> ---------------------------------------------------------------------
>>>>> Product.facets(:conditions => {:model => "U2716"})
>>> {:model=>{}, :product_category_id=>{}, :cost=>{}, :brand=>{}}
>>
>>> I was wondering if guys have any thoughts opinions on how I can get
>>> this issue that get me back the results i.e.
>>> "Product.facets(:conditions => {:model => "U2716"})". # => some
>>> results back.
>>
>>> --
>>> 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
>>> athttp://groups.google.com/group/thinking-sphinx?hl=en.
>
> --
> 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.
>
--
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.
