Thanks for the clarification Jorge, I guess that makes some sense.
My one suggestion at the moment is to change the table references in
the sql_query as well - but if indexing still doesn't work after that,
I'm not sure what to do.
What is the setting you've added to Rails to allow this to all work?
--
Pat
On 29/11/2009, at 2:17 PM, scriptbox wrote:
> When I try the SELECT in postgresql it tells me the relation doesn't
> exist. So it's in fact a problem that comes from postgresql I think.
>
> About the schemas, I don't know if you work with postgresql, but in
> this database engine you can create some "folders" to organise your
> tables inside. So it needs a special declaration in the rails' config
> file.
>
> But when we do a query we can specify the schema where the table is
> inside. And that's what I've done. (please pay attention juste after
> the FROM instruction, the schema name is amv)
>
> So I've done: SELECT COALESCE(MIN("id"), 1::bigint), COALESCE(MAX
> ("id"),
>> 1::bigint) FROM "amv"."dishes";
>
> this, in command line, brings me the result normally (no error), so I
> inserted the same instruction in the development.sphinx.conf file, and
> launch the indexing with rake thinking_sphinx:index INDEX_ONLY=true,
> but it brings the same old error.
>
> I'll continue to try, but if you have any idea please just tell.. :-)
>
> On 29 nov, 03:59, Pat Allan <[email protected]> wrote:
>> So, as far as Rails is concerned, the table is there, but according
>> to
>> Sphinx it isn't...
>>
>> Let's see what Postgres says...
>>
>> * Connect into postgresql using "psql -U db_username"
>> * Retry the SELECT statement from earlier...
>> SELECT COALESCE(MIN("id"), 1::bigint), COALESCE(MAX("id"),
>> 1::bigint) FROM "dishes";
>>
>> What's the output?
>>
>> Also, what do you mean by having different schema paths?
>>
>> --
>> Pat
>>
>> On 29/11/2009, at 1:50 PM, scriptbox wrote:
>>
>>
>>
>>> I'm new to rails, so I'm doing everything in a standard way.
>>
>>> All models points to the same database, and I have only one
>>> connection. All table are in the same database, but I have diferents
>>> schema path. The models work nicely, and everything is going ok. I
>>> would like to add sphinx + thinking to improve search response etc.
>>
>>> So the Dishes and categories tables are in the same schema, and
>>> works
>>> normally.
>>
>>> Any clue? :-)
>>
>>> ----
>>> Jorge
>>
>>> On 29 nov, 03:41, Pat Allan <[email protected]> wrote:
>>>> No, it's not your fault, it's Google's.
>>
>>>> Looks like the table's there. Are you doing anything particularly
>>>> different with database connections? Are all the models in the same
>>>> database? Are the connection details in the dish source the same as
>>>> those in the category source?
>>
>>>> --
>>>> Pat
>>
>>>> On 29/11/2009, at 1:24 PM, scriptbox wrote:
>>
>>>>> Thank you again for your help;
>>
>>>>> The answer is:
>>
>>>>>>> Dish.connection.select_all('SELECT COALESCE(MIN("id"),
>>>>>>> 1::bigint),
>>>>> COALESCE(MAX("id"), 1::bigint) FROM "dishes"')
>>>>> => [{"coalesce"=>"10337"}]
>>
>>>>> (I'm sorry but I don't know if it is my mistake if the mail is
>>>>> missing, but I'm new to google groups)
>>
>>>>> --
>>>>> Jorge
>>
>>>>> On 29 nov, 03:19, Pat Allan <[email protected]> wrote:
>>>>>> Doesn't look like Thinking Sphinx is doing anything odd...
>>
>>>>>> Can you try the following in script/console?
>>>>>> Dish.connection.select_all('SELECT COALESCE(MIN("id"),
>>>>>> 1::bigint),
>>>>>> COALESCE(MAX("id"), 1::bigint) FROM "dishes"')
>>
>>>>>> (And your email was missing again from google's moderation queue,
>>>>>> but
>>>>>> I've now flagged your account as not needing moderation, so
>>>>>> hopefully
>>>>>> that'll fix that problem).
>>
>>>>>> --
>>>>>> Pat
>>
>>>>>> On 29/11/2009, at 1:09 PM, scriptbox wrote:
>>
>>>>>>> Hi Pat Alan;
>>
>>>>>>> First of all, thank you very much for helping me. I just don't
>>>>>>> know
>>>>>>> what to do.
>>
>>>>>>> I checked the content for this file, and the user, pass, host,
>>>>>>> db
>>>>>>> seems nice, but there is no schema. Here is the content for
>>>>>>> dish_core_0:
>>
>>>>>>> source dish_core_0
>>>>>>> {
>>>>>>> type = pgsql
>>>>>>> sql_host = localhost
>>>>>>> sql_user = **********
>>>>>>> sql_pass =
>>>>>>> sql_db = amv
>>>>>>> sql_query = SELECT "dishes"."id" * 3 + 1 AS "id" ,
>>>>>>> "dishes"."label"
>>>>>>> AS "label", "dishes"."id" AS "sphinx_internal_id", 894378886 AS
>>>>>>> "class_crc", '894378886' AS "subclass_crcs", 0 AS
>>>>>>> "sphinx_deleted"
>>>>>>> FROM "dishes" WHERE "dishes"."id" >= $start AND "dishes"."id"
>>>>>>> <=
>>>>>>> $end GROUP BY "dishes"."id", "dishes"."label", "dishes"."id"
>>>>>>> sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint),
>>>>>>> COALESCE
>>>>>>> (MAX
>>>>>>> ("id"), 1::bigint) FROM "dishes"
>>>>>>> sql_attr_uint = sphinx_internal_id
>>>>>>> sql_attr_uint = class_crc
>>>>>>> sql_attr_uint = sphinx_deleted
>>>>>>> sql_attr_multi = uint subclass_crcs from field
>>>>>>> sql_query_info = SELECT * FROM "dishes" WHERE "id" = (($id -
>>>>>>> 1) /
>>>>>>> 3)
>>>>>>> }
>>
>>>>>>> On 29 nov, 03:01, Pat Allan <[email protected]> wrote:
>>>>>>>> Hi Jorge
>>
>>>>>>>> This is a new issue, but let's start with the generated config
>>>>>>>> file,
>>>>>>>> config/development.sphinx.conf. What's the sql_query value for
>>>>>>>> your
>>>>>>>> dish_core_0 source?
>>
>>>>>>>> --
>>>>>>>> Pat
>>
>>>>>>>> On 29/11/2009, at 11:06 AM, Pat Allan wrote:
>>
>>>>>>>>> (Was in the moderation queue according to my email, but not
>>>>>>>>> Google's
>>>>>>>>> site. Have forwarded this on. Originally from scriptbox/
>>>>>>>>> [email protected]
>>>>>>>>> )
>>
>>>>>>>>> Hello,
>>
>>>>>>>>> For three days I'm facing a very weird problem. I have a
>>>>>>>>> script
>>>>>>>>> that
>>>>>>>>> works fine, so I started using Thinking Sphinx to improve
>>>>>>>>> performance.
>>>>>>>>> So I installed and added two indexes, as following:
>>
>>>>>>>>> class Category < ActiveRecord::Base
>>
>>>>>>>>> # relation entre les tables
>>>>>>>>> belongs_to :macrocategory
>>
>>>>>>>>> define_index do
>>>>>>>>> indexes terms
>>>>>>>>> end
>>
>>>>>>>>> end
>>
>>>>>>>>> class Dish < ActiveRecord::Base
>>
>>>>>>>>> # Rélation entre les tables
>>>>>>>>> belongs_to :partner
>>>>>>>>> has_one :decomposition
>>>>>>>>> has_many :dish_combos
>>
>>>>>>>>> define_index do
>>>>>>>>> indexes label
>>>>>>>>> end
>>
>>>>>>>>> # Named Scopes
>>>>>>>>> named_scope :get_distinct_macrocategories, :select =>
>>>>>>>>> 'DISTINCT ON
>>>>>>>>> (combo_macrocategory) combo_macrocategory'
>>>>>>>>> named_scope :not_decomposed_dishes, :select => 'id,
>>>>>>>>> label', :conditions => ['combo_categories IS NULL']
>>>>>>>>> end
>>
>>>>>>>>> The problem is that everytime I launch the indexing action
>>>>>>>>> with
>>>>>>>>> rake
>>>>>>>>> thinking_sphinx:index the first model table is indexed, but
>>>>>>>>> not
>>>>>>>>> the
>>>>>>>>> second one. And I got the following output:
>>
>>>>>>>>> Generating Configuration to /Users/jorgegomes/Sites/amv/
>>>>>>>>> config/
>>>>>>>>> development.sphinx.conf
>>>>>>>>> Sphinx 0.9.9-rc2 (r1785)
>>>>>>>>> Copyright (c) 2001-2009, Andrew Aksyonoff
>>
>>>>>>>>> using config file '/Users/jorgegomes/Sites/amv/config/
>>>>>>>>> development.sphinx.conf'...
>>>>>>>>> indexing index 'category_core'...
>>>>>>>>> collected 151 docs, 0.0 MB
>>>>>>>>> collected 0 attr values
>>>>>>>>> sorted 0.0 Mvalues, 100.0% done
>>>>>>>>> sorted 0.0 Mhits, 100.0% done
>>>>>>>>> total 151 docs, 13516 bytes
>>>>>>>>> total 0.015 sec, 894862 bytes/sec, 9997.35 docs/sec
>>>>>>>>> distributed index 'category' can not be directly indexed;
>>>>>>>>> skipping.
>>>>>>>>> indexing index 'dish_core'...
>>>>>>>>> ERROR: index 'dish_core': sql_query_range: : range-query
>>>>>>>>> failed:
>>>>>>>>> ERROR: relation "dishes" does not exist
>>>>>>>>> (DSN=pgsql://gomesjorge:*...@localhost:5432/amv).
>>>>>>>>> total 0 docs, 0 bytes
>>>>>>>>> total 0.061 sec, 0 bytes/sec, 0.00 docs/sec
>>>>>>>>> distributed index 'dish' can not be directly indexed;
>>>>>>>>> skipping.
>>>>>>>>> indexing index 'macrocategory_core'...
>>>>>>>>> collected 23 docs, 0.0 MB
>>>>>>>>> collected 0 attr values
>>>>>>>>> sorted 0.0 Mvalues, 100.0% done
>>>>>>>>> sorted 0.0 Mhits, 100.0% done
>>>>>>>>> total 23 docs, 443 bytes
>>>>>>>>> total 0.012 sec, 35318 bytes/sec, 1833.69 docs/sec
>>>>>>>>> distributed index 'macrocategory' can not be directly indexed;
>>>>>>>>> skipping.
>>>>>>>>> total 10 reads, 0.000 sec, 13.8 kb/call avg, 0.0 msec/call avg
>>>>>>>>> total 18 writes, 0.000 sec, 2.3 kb/call avg, 0.0 msec/call avg
>>
>>>>>>>>> But the table exists and the model works fine, can you help
>>>>>>>>> please
>>>>>>>>> I'm
>>>>>>>>> really stucked in this problem.
>>
>>>>>>>>> Thank you very much!
>>
>>>>>>>>> --
>>
>>>>>>>>> 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
>>> 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.
