This is the resulting sql id place_id food_type_facet 40 40 3615477584,206447654
About the irb I don't seem able to place accented chars (bash v.4 does show them). Will have to dig a bit, those solutions on web to solve this are not pretty. Pat, what also I don't understand is why normal columns which I'm indexing (as facets too) display correctly the accented chars, but if they are associations like this case "Indonésia" they just give null. Best regards Pedro On Jan 8, 2:49 am, Pat Allan <[email protected]> wrote: > 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]. > > ... > > read more » -- 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.
