Hi Benjamin
Sorry it's taken so long to get back to you - and I'm afraid the only
information I have at this point is that I think this is a bug in
Rails, not Thinking Sphinx. It seems conditions defined as a hash are
given original table names, instead of aliases, in their
specification. I'll investigate further to confirm, but at this stage,
it's not looking like a quick fix.
Although given how long it's taken me to look into this, it wasn't
going to be quick fix anyway. :|
--
Pat
On 10/02/2009, at 7:01 AM, Benjamin wrote:
>
> Hey, here is my sql_query (irrelevant fields removed to save length)
>
> SELECT
> GROUP_CONCAT(CONCAT_WS(',', `user_relations`.`id`,
> `user_relations_friends_ins_users`.`id`) SEPARATOR ',') AS
> `friend_ids`,
> `users`.`id` AS `sphinx_internal_id`,
> 765557111 AS `class_crc`,
> '765557111' AS `subclass_crcs`,
> 0 AS `sphinx_deleted`
> FROM users
> LEFT OUTER JOIN `businesses` ON businesses.user_id = users.id
> LEFT OUTER JOIN `categories` ON `categories`.id =
> `businesses`.category_id
> LEFT OUTER JOIN `business_categories` ON (`businesses`.`id` =
> `business_categories`.`business_id`) LEFT OUTER JOIN `categories`
> categories_businesses ON (`categories_businesses`.`id` =
> `business_categories`.`category_id`)
> LEFT OUTER JOIN `user_relations` ON user_relations.created_by_id =
> users.id AND `user_relations`.`state` = 'friend'
> LEFT OUTER JOIN `user_relations` user_relations_friends_ins_users ON
> user_relations_friends_ins_users.target_user_id = users.id AND
> `user_relations`.`state` = 'friend'
> WHERE `users`.`id` >= $start
> AND `users`.`id` <= $end
> AND `users`.`delta` = 0 AND `users`.`state` = 'active'
> GROUP BY `users`.`id`
> ORDER BY NULL
>
> As you could see by running the query, the final outer join for
> user_relations_friends_ins_users fails because it uses
> "`user_relations`.`state` = 'friend'" which is ambiguous to either
> joins of the user_relations table. After looking further, it seems
> ActiveRecord isn't returning the correct aliases.
>
> Is there anything I can do to correct this?
>
> Thanks
>
>
>
> On Feb 1, 8:22 am, Pat Allan <[email protected]> wrote:
>> Hi Benjamin
>>
>> This is a confusing issue - ActiveRecord should be giving thosejoins
>> aliases to distinguish between the two in the query (Thinking Sphinx
>> just uses ActiveRecord's SQL join generation). What's the entire
>> sql_query value?
>>
>> As far as I can tell, you're not doing anything wrong.
>>
>> --
>> Pat
>>
>> On 27/01/2009, at 2:03 AM, Benjamin wrote:
>>
>>
>>
>>> I'm having a problem indexing relations with conditions on the
>>> relation. I'll let the example show what I mean...
>>
>>> class User < ActiveRecord::Base
>>
>>> has_many :friends_out, :class_name => 'UserRelation' :foreign_key
>>> =>
>>> 'created_by_id',
>>> :conditions => { :state => 'friend' }
>>> has_many :friends_in, :class_name => 'UserRelation' :foreign_key =>
>>> 'target_id',
>>> :conditions => { :state => 'friend' }
>>
>>> define_index
>>> # fields.....
>>> has [friends_out(:id), friends_in(:id)], :as => :friend_ids
>>> end
>>
>>> end
>>
>>> The problem is the generated sphinx config has incorrectjoinsto
>>> accomplish this..,
>>
>>> LEFT OUTER JOIN `user_relations` user_relations_friends_ins_users ON
>>> user_relations_friends_ins_users.target_user_id = users.id AND
>>> `user_relations`.`state` = 'friend'
>>
>>> LEFT OUTER JOIN `user_relations` ON user_relations.created_by_id =
>>> users.id AND `user_relations`.`state` = 'friend'
>>
>>> The 'state' column is ambiguous. Is this a bug? I am doing something
>>> incorrectly?
>>> Thanks
>>
>>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
