Yup, conditions in joins use the standard sanitise_sql in the related
model - in your case, UserRelation - so it doesn't have the context of
the join itself. I can't spot a quick way of fixing Rails to allow for
this at the moment, unfortunately.
As an alternative, you could keep the joins as they are, but generate
the sphinx file using ts:conf, and then run ts:index with
INDEX_ONLY=true *after* making the necessary modifications to the SQL
query. Yes, it's messy, but that's the best solution I've got at the
moment, unfortunately.
--
Pat
On 03/03/2009, at 6:37 PM, Pat Allan wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
