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
-~----------~----~----~----~------~----~------~--~---
