No worries - it helps that I had someone else dealing with the same issue on
Flying Sphinx recently :)
On 29/12/2012, at 4:06 PM, John Barker wrote:
> Hi Pat,
>
> Thanks for such a prompt response! So moving the conditions from the User
> model to the Relationship model and changing the attributes to
> relationships.model(:column) seems to work exactly as I need it to. I tried a
> similar thing earlier, but must have overlooked something.
>
> Thanks for such a great library (and a great service, Flying Sphinx)!
>
> Cheers
>
> On Friday, December 28, 2012 10:51:16 PM UTC-6, Pat Allan wrote:
> Hi John
> I would avoid using has_many :through shortcuts in your index definition -
> so, you're doing the right thing with fields, but not with attributes.
> Thinking Sphinx uses ActiveRecord to generate the SQL, and it appears
> ActiveRecord isn't as smart as it could be with joins for those associations.
>
> Of course, then you have the catch that you're applying conditions to the
> has_many :through versions of the associations… I would probably define those
> as additional associations on Relationship instead if necessary?
>
> --
> Pat
>
> On 29/12/2012, at 1:15 PM, John Barker wrote:
>
> > I have an index in my User model that is creating redundant joins for
> > attributes I have defined. My model looks as follows:
> >
> > class User < ActiveRecord::Base
> > has_many :relationships
> > has_many :groups, through: :relationships, conditions: Proc.new {
> > ['relationships.type = ?', 'Group'] }
> > has_many :organizations, through: :relationships, conditions: Proc.new
> > { ['relationships.type = ?', 'Organization'] }
> > has_many :roles, through: :relationships
> >
> >
> > define_index do
> > # fields
> > indexes first_name, sortable: true
> > indexes last_name, sortable: true
> > indexes email, sortable: true
> > indexes relationships.group(:name), as: :groups, sortable: true
> > indexes relationships.role(:name), as: :roles
> >
> > set_property field_weights: {
> > first_name: 15,
> > last_name: 15,
> > email: 7,
> > groups: 10
> > }
> >
> > has relationships(:type), as: :relationship_type, crc: true
> > has groups(:id), as: :group_id
> > has organizations(:id), as: :organization_id
> > has roles(:name), as: :role_name, crc: true
> > has created_at, updated_at
> > end
> >
> > This produces the following query in the sphinx configuration:
> >
> > SELECT
> > SQL_NO_CACHE `users`.`id` * CAST(5 AS SIGNED) + 4 AS `id` ,
> > `users`.`first_name` AS `first_name`
> > `users`.`last_name` AS `last_name`,
> > `users`.`email` AS `email`,
> > GROUP_CONCAT(DISTINCT IFNULL(`groups`.`name`, '0') SEPARATOR ' ') AS
> > `groups`,
> > GROUP_CONCAT(DISTINCT IFNULL(`roles`.`name`, '0') SEPARATOR ' ') AS
> > `roles`,
> > `users`.`id` AS `sphinx_internal_id`, 0 AS `sphinx_deleted`,
> > 765557111 AS `class_crc`, IFNULL('User', '') AS
> > `sphinx_internal_class`,
> > IFNULL(`users`.`first_name`, '') AS `first_name_sort`,
> > IFNULL(`users`.`last_name`, '') AS `last_name_sort`,
> > IFNULL(`users`.`email`, '') AS `email_sort`,
> > GROUP_CONCAT(DISTINCT IFNULL(IFNULL(`groups`.`name`, ''), '0')
> > SEPARATOR ' ') AS `groups_sort`,
> > GROUP_CONCAT(DISTINCT IFNULL(CRC32(`relationships`.`type`), '0')
> > SEPARATOR ',') AS `relationship_type`,
> > GROUP_CONCAT(DISTINCT IFNULL(`groups_users`.`id`, '0') SEPARATOR ',')
> > AS `group_id`,
> > GROUP_CONCAT(DISTINCT IFNULL(`organizations`.`id`, '0') SEPARATOR ',')
> > AS `organization_id`,
> > GROUP_CONCAT(DISTINCT IFNULL(CRC32(`roles_users`.`name`), '0')
> > SEPARATOR ',') AS `role_name`,
> > UNIX_TIMESTAMP(`users`.`created_at`) AS `created_at`,
> > UNIX_TIMESTAMP(`users`.`updated_at`) AS `updated_at`
> > FROM `users`
> > LEFT OUTER JOIN `relationships` ON `relationships`.`user_id` =
> > `users`.`id`
> > LEFT OUTER JOIN `groups` ON `groups`.`id` =
> > `relationships`.`relationship_id`
> > LEFT OUTER JOIN `roles` ON `roles`.`id` = `relationships`.`role_id`
> > LEFT OUTER JOIN `relationships` `relationships_users_join` ON
> > `relationships_users_join`.`user_id` = `users`.`id`
> > LEFT OUTER JOIN `groups` `groups_users` ON `groups_users`.`id` =
> > `relationships_users_join`.`relationship_id` AND relationships.type =
> > 'Group'
> > LEFT OUTER JOIN `relationships` `relationships_users_join_2` ON
> > `relationships_users_join_2`.`user_id` = `users`.`id`
> > LEFT OUTER JOIN `organizations` ON `organizations`.`id` =
> > `relationships_users_join_2`.`relationship_id` AND relationships.type =
> > 'Organization'
> > LEFT OUTER JOIN `relationships` `relationships_users_join_3` ON
> > `relationships_users_join_3`.`user_id` = `users`.`id`
> > LEFT OUTER JOIN `roles` `roles_users` ON `roles_users`.`id` =
> > `relationships_users_join_3`.`role_id`
> > WHERE (`users`.`id` >= $start AND `users`.`id` <= $end)
> > GROUP BY `users`.`id` ORDER BY NULL
> >
> > Everything is great up until the joins; it creates three extra unnecessary
> > joins (relationships_users_join_n) for the Group, Organization, and Roles
> > attributes. I have tried changing the attribute syntax from groups(:id) to
> > relationships.group(:id) but it fails to acknowledge the conditions that is
> > applied to the join that is defined by the has_many in the model. Other
> > than failing to acknowledge the condition, it solves my redundant join
> > issue.
> >
> > Any help is greatly appreciated!
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Thinking Sphinx" group.
> > To view this discussion on the web visit
> > https://groups.google.com/d/msg/thinking-sphinx/-/JvcwXKlb4MgJ.
> > 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 view this discussion on the web visit
> https://groups.google.com/d/msg/thinking-sphinx/-/IJE3e6jUKT8J.
> 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.
