Hi Pedro
What's the output of the following SQL query:
SELECT "places"."id" * 1 + 0 AS "id",
"places"."id" as "place_id",
array_to_string(array_accum(COALESCE(crc32("food_types"."name"),
'0')), ',') AS "food_type_facet"
FROM "places"
LEFT OUTER JOIN "food_types_restaurants" ON
"food_types_restaurants".restaurant_id = "places".id
LEFT OUTER JOIN "food_types" ON "food_types".id =
"food_types_restaurants".food_type_id
WHERE "places"."id" = 40
AND "places"."type" = 'Restaurant'
GROUP BY "places"."id"
Also, what's the output in script/console of:
"Indonésia".to_crc32
--
Pat
On 05/01/2011, at 10:43 PM, Pedro Cunha wrote:
> sql_query = SELECT "places"."id" * 1 + 0 AS "id" , "places"."name" AS
> "name", "places"."concelho" AS "concelho", "places"."freguesia" AS
> "freguesia", "places"."locality" AS "locality", "places"."distrito" AS
> "distrito", array_to_string(array_accum(COALESCE("zones"."name",
> '0')), ' ') AS "zone",
> array_to_string(array_accum(COALESCE("ideal_types"."name", '0')), ' ')
> AS "ideal_type",
> array_to_string(array_accum(COALESCE("food_types"."name", '0')), ' ')
> AS "food_type", "places"."id" AS "sphinx_internal_id",
> COALESCE(crc32(NULLIF("places"."type",'')), 2764575215::bigint) AS
> "class_crc", 0 AS "sphinx_deleted", crc32("places"."locality") AS
> "locality_facet", crc32("places"."distrito") AS "distrito_facet",
> array_to_string(array_accum(COALESCE(crc32("zones"."name"), '0')),
> ',') AS "zone_facet",
> array_to_string(array_accum(COALESCE(crc32("ideal_types"."name"),
> '0')), ',') AS "ideal_type_facet",
> array_to_string(array_accum(COALESCE(crc32("food_types"."name"),
> '0')), ',') AS "food_type_facet", "places"."average_price_id" AS
> "average_price_id", "places"."has_disabled_access" AS
> "has_disabled_access", "places"."has_live_music" AS "has_live_music",
> "places"."has_animation" AS "has_animation", "places"."has_beach" AS
> "has_beach", "places"."has_outside_tables" AS "has_outside_tables",
> "places"."has_vista" AS "has_vista",
> array_to_string(array_accum(COALESCE("smoking_types"."id", '0')), ',')
> AS "smoking_type_id", "places"."current_configuration_id" AS
> "current_configuration_id", cast(extract(epoch from
> "places"."created_at") as int) AS "created_at", "places"."total_like"
> AS "total_like", "average_prices"."position" AS "average_price" FROM
> "places" LEFT OUTER JOIN "place_zones" ON ("places"."id" =
> "place_zones"."place_id") LEFT OUTER JOIN "zones" ON ("zones"."id" =
> "place_zones"."zone_id") LEFT OUTER JOIN "ideal_types_restaurants"
> ON "ideal_types_restaurants".restaurant_id = "places".id LEFT OUTER
> JOIN "ideal_types" ON "ideal_types".id =
> "ideal_types_restaurants".ideal_type_id LEFT OUTER JOIN
> "food_types_restaurants" ON "food_types_restaurants".restaurant_id =
> "places".id LEFT OUTER JOIN "food_types" ON "food_types".id =
> "food_types_restaurants".food_type_id LEFT OUTER JOIN
> "restaurants_smoking_types" ON
> "restaurants_smoking_types".restaurant_id = "places".id LEFT OUTER
> JOIN "smoking_types" ON "smoking_types".id =
> "restaurants_smoking_types".smoking_type_id LEFT OUTER JOIN
> "average_prices" ON "average_prices".id = "places".average_price_id
> WHERE "places"."id" >= $start AND "places"."id" <= $end AND
> "places"."type" = 'Restaurant' GROUP BY "places"."id",
> "places"."name", "places"."concelho", "places"."freguesia",
> "places"."locality", "places"."distrito", "places"."id",
> "places"."locality", "places"."distrito", "places"."average_price_id",
> "places"."has_disabled_access", "places"."has_live_music",
> "places"."has_animation", "places"."has_beach",
> "places"."has_outside_tables", "places"."has_vista",
> "places"."current_configuration_id", "places"."created_at",
> "places"."total_like", "average_prices"."position", "places"."type"
>
>
> Postgres version:
> PostgreSQL 8.4.5 and 9.0.1
>
>
> Still I'm suspected from the collation I refered on my last post
> (en_US)
>
> Postgres gem:
> pg (0.9.0)
>
>
>
>
> On Jan 5, 2:41 am, Pat Allan <[email protected]> wrote:
>> Hi Pedro
>>
>> In your config/development.sphinx.conf file, what's the sql_query value for
>> your restaurant_core_0 source? Also, what version of PostgreSQL are you
>> using?
>>
>> --
>> Pat
>>
>> On 04/01/2011, at 10:55 PM, Pedro Cunha wrote:
>>
>>
>>
>>
>>
>>
>>
>>>>> Restaurant.find(40).food_types
>>> [#<FoodType id: 26, name: "Indonésia", code: nil, permalink:
>>> "indonesia", created_at: "2011-01-04 11:39:04", updated_at:
>>> "2011-01-04 11:39:04">, #<FoodType id: 27, name: "Internacional",
>>> code: nil, permalink: "internacional", created_at: "2011-01-04
>>> 11:39:04", updated_at: "2011-01-04 11:39:04">]
>>
>>>>> Restaurant.search "Fonte Clotilde Miseráveis"
>>> => [#<Restaurant id: 40, name: "Fonte Clotilde Miseráveis" ...
>>
>>>>> Restaurant.facets "Fonte Clotilde Miseráveis"
>>> => {:locality=>{"Quinta das
>>> Teresinhas"=>1}, :distrito=>{"Lisboa"=>1}, :ideal_type=>{"Cocktails"=>1,
>>> "Descontrair"=>1, "Beira mar"=>1}, :food_type=>{"Internacional"=>1,
>>> nil=>1}}
>>
>>> With:
>>> TS 1.3.20
>>> postgres, database is in UTF8 and collation en_US
>>
>>> Sorry for double posting forgot more specific examples
>>
>>> On Jan 4, 11:33 am, Pedro Cunha <[email protected]> wrote:
>>>> My current index:
>>
>>>> define_index do
>>>> indexes :name, :as => :name
>>>> indexes concelho
>>>> indexes freguesia
>>>> indexes locality, :as => :locality, :facet => true
>>>> indexes distrito, :facet => true
>>>> indexes ideal_types(:name), :as => :ideal_type, :facet => true
>>>> indexes food_types(:name), :as => :food_type, :facet => true
>>
>>>> has average_price_id
>>>> has has_live_music
>>>> has has_animation
>>>> has has_beach
>>>> has has_outside_tables
>>>> has has_vista
>>>> has smoking_types(:id), :as => :smoking_type_id
>>
>>>> has created_at
>>
>>>> # Collections for sorting
>>>> has total_like, :as => :likes, :type => :integer
>>>> has average_price(:position), :as => :average_price, :type
>>>> => :integer
>>
>>>> set_property :min_prefix_len => 3
>>>> end
>>
>>>> Facet search. It includes portuguese words, all words which include
>>>> accented chars are just not displayed / indexed? Can't figure it out
>>>> if I'm doing something wrong
>>
>>>>>> Restaurant.facets ""
>>
>>>> => {:locality=>{"Quinta do Alto das Areias"=>1, "Quinta do
>>>> Montezelo"=>1, "Ajuda"=>1, "Serra da Luz"=>3, "Braço de Prata"=>1,
>>>> "Chelas"=>1, "Campo de Ourique"=>1, "Santa Maria dos Olivais"=>1,
>>>> "Bairro Dona Leonor"=>1, "Quinta do Cartucho"=>1, "Alto da Ajuda"=>1,
>>>> "Quinta das Rosas"=>3, "Charneca"=>1, "Bairro do Menino de Deus"=>1,
>>>> "Olival do Rio"=>2, "Alvalade"=>2, "Bairro das Calvanas"=>1, "Fonte da
>>>> Pipa"=>1, "Bairro da Musgueira Sul"=>1, "Olivais Sul"=>1, "Quinta da
>>>> Fonte da Calça"=>1, "Casa Branca"=>2, "Encarnação"=>2, "Mesquita"=>1,
>>>> "Bairro de Santa Clara"=>1, "Caselas"=>1, "Quinta do Mata Mouros"=>1,
>>>> "Grilo"=>1, "Hipódromo"=>2, "Quinta do Baleeiro"=>1, "Quinta das
>>>> Teresinhas"=>1, "Francelha de Cima"=>1, "Quinta das Lavadeiras"=>2,
>>>> "Quinta dos Azulejos"=>1, "Bairro da Liberdade"=>1, "Quinta do
>>>> Pisany"=>1, "Quinta do Souto"=>2, "Quinta da Barroca"=>1,
>>>> "Alcântara"=>1}, :ideal_type=>{"Vista"=>11, nil=>27, "Ver e ser
>>>> visto"=>16, "Cocktails"=>14, "Descontrair"=>13, "Esplanada"=>8, "Boa
>>>> carta de vinhos"=>15, "Beira mar"=>7, "Fora d'horas"=>13, "Sair a
>>>> dois"=>14}, :distrito=>{"Lisboa"=>50}, :food_type=>{"Sul
>>>> Americana"=>2, "Goesa"=>1, "Francesa"=>2, "Grega"=>3,
>>>> "Internacional"=>2, "Judaica"=>2, "Coreana"=>1, "Vietnamita"=>2,
>>>> "Steak House"=>2, "Indiana"=>2, "Espanhola"=>4, "Brasileira"=>2, "Fast
>>>> Food"=>2, "Light"=>1, "Libanesa"=>2, "Belga"=>4, "Tailandesa"=>1,
>>>> "Americana"=>1, "Sueca"=>2, "Israelita"=>2, "Africana"=>2,
>>>> "Caribenha"=>1, nil=>18, "Italiana"=>2, "Autor"=>4, "Vegetariana"=>4,
>>>> "Nepalesa"=>4, "de Mercado"=>1, "Tibetana"=>2, "Japonesa"=>2, "Fast
>>>> Food Gourmet"=>1, "Cubana"=>1, "Portuguesa"=>3, "Irlandesa"=>1,
>>>> "Chinesa"=>2, "Grelhados"=>3, "Argentina"=>2, "Marroquina"=>2,
>>>> "Tapas"=>3, "Russa"=>1}}
>>
>>>> Cheers
>>
>>>> On Jan 4, 1:19 am, Pat Allan <[email protected]> wrote:
>>
>>>>> Hi Pedro
>>
>>>>> Not quite sure what you mean... can you give me an example of the data
>>>>> you're faceting, and what you expect the results to be?
>>
>>>>> --
>>>>> Pat
>>
>>>>> On 04/01/2011, at 12:09 PM, Pedro Cunha wrote:
>>
>>>>>> Is there any catch to able to get all facets which are refering
>>>>>> accented words?
>>
>>>>>> I get something like nil => 4
>>
>>>>>> Or I need to configure a proper char set table? But that doesn't
>>>>>> really help my problem because I don't want to lose the proper typing
>>>>>> of the words
>>
>>>>>> Cheers,
>>>>>> And really thanks for this awesome gem
>>
>>>>>> --
>>>>>> 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
>>> 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.
