Hi Pat,
So, to begin, I have an application that is membership-centric; users can
have 'memberships' to both 'groups' and 'organizations', which is handled
through a 'relationships' table/model to handle all the
associations/relationships.
The relationships table handles all the relationships for the user for both
organization and group, and simply distinguishes between which by the
'type' column. For example, here's my current 'Relationship' model (you'll
see I have the conditioned commented out):
class Relationship < ActiveRecord::Base
> belongs_to :role
> belongs_to :user
> belongs_to :organization, foreign_key: 'relationship_id'#, conditions: {
> relationships: { type: 'Organization', active: true } }
> belongs_to :group, foreign_key: 'relationship_id'#, conditions: {
> relationships: { type: 'Group', active: true } }
>
> has_many :lists
>
> attr_accessible :user_id, :role_id, :relationship_id, :role, :user,
> :created_at, :updated_at, :type, :begin_date, :end_date, :active
>
> validates_uniqueness_of :relationship_id, scope: [ :type, :role_id,
> :user_id ]
>
> scope :page_with_cached_total_count, lambda { |page_number,
> total_count_value |
> page(page_number).extending {
> define_method(:total_count) { total_count_value }
> }
> }
>
> define_index do
> indexes user.first_name, as: :first_name, sortable: true
> indexes user.middle_name, as: :middle_name, sortable: true
> indexes user.last_name, as: :last_name, sortable: true
> indexes user.email, as: :email, sortable: true
> indexes group(:name), as: :group, sortable: true
>
> set_property field_weights: {
> first_name: 15,
> last_name: 15,
> middle_name: 2,
> email: 7,
> groups: 10
> }
>
> has organization(:id), as: :organization_id
> has created_at, updated_at
> has role(:name), as: :role_name, crc: true
> has :type, crc: true
> end
>
> # Override ActiveRecord's inheritance column method since we use a
> `type` column
> def self.inheritance_column
> nil
> end
> end
I need those conditions on the User, Organization and other models so that
they will respect the conditions when I call something like
Organization.find(1).members, which would do a join on the relationships
table and search by type 'organization' and the and ensure that the
relationship is in fact active (active=1).
For example, my Organization model's relationship definition looks like
this:
has_many :relationships, foreign_key: 'relationship_id', conditions: {
> relationships: { type: 'Organization', active: true } }
> has_many :members, through: :relationships, source: :user
If I remove the conditions from the Organization model, and simply leave it
on the Relationships model, then it doesn't respect the condition when I
call something like Organization.find(1).members. There are some other
errors, weirdness I experience as well, but that is the major reason.
If you have any help, or insight as to how I could better structure this
and retain the needed index performance with TS, I would be forever
grateful! I hope I made this clear enough.
Thank you.
On Tuesday, January 1, 2013 9:30:49 PM UTC-6, Pat Allan wrote:
>
> Why do the conditions need to be in the other models? Can you run me
> through the associations in all models related to this issue?
>
> On 02/01/2013, at 11:34 AM, John Barker wrote:
>
> > So I thought this problem was fixed, after moving the needed conditions
> to the relationship table, but come to find out, I can't have the
> conditions there; they have to be in my other models. Consider me stumped.
> >
> > On Friday, December 28, 2012 11:11:07 PM UTC-6, Pat Allan wrote:
> > 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 view this discussion on the web visit
> https://groups.google.com/d/msg/thinking-sphinx/-/c1sORwQ8_ssJ.
> > To post to this group, send email to
> > [email protected]<javascript:>
> .
> > To unsubscribe from this group, send email to
> [email protected] <javascript:>.
> > 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/-/BgBwJl6miUwJ.
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.
