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.
