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