No worries about the delay. All work and no play is no good. :-) As usual
your suggestion was right on the spot. Thank you!
Cheers,
Roger
kl. 20:22:58 UTC+2 tirsdag 28. august 2012 skrev Pat Allan følgende:
>
> Sorry for not getting back to you sooner Roger. My inbox has been
> suffering some serious neglect over the last month. I blame the Edinburgh
> Fringe.
>
> One thing that may be worth trying is being a little more explicit with
> your association references:
>
> indexes doctors_practices.practices.name, :as => :practice_name
> indexes doctors_practices.counties.name, :as => :counties
>
> This should hopefully avoid the doubling up on joins. Also, I'd suggest
> there's not much point making these columns sortable, given they're
> aggregated values.
>
> If that doesn't help, let me know - although the SQL joins are something
> managed by Rails, so there's limitations in how much they can be modified.
>
> Cheers
>
> --
> Pat
>
> On 25/08/2012, at 8:10 AM, Roger Kind Kristiansen wrote:
>
> > Sorry to drag this up again, but I still haven't managed to find a
> solution. Is my explanation still unclear, or is there no easy way around
> this?
> >
> > Cheers,
> > Roger
> >
> >
> > kl. 21:50:17 UTC+2 tirsdag 31. juli 2012 skrev Roger Kind Kristiansen
> følgende:
> > Sure!
> >
> > I've narrowed down the problem slightly by eliminating all parts of my
> index which are unnecessary. I'll start with the parts of the datamodel I
> suppose are relevant:
> >
> > Doctor
> > has_many :doctors_practices, :inverse_of => :doctor
> > has_many :practices, :through => :doctors_practices
> > has_many :counties, :through => :doctors_practices
> >
> > * I've got an explicit doctors_practices relation to be able to access
> various fields on the relation.
> > * The doctors_practices table contain the county id for convenience, to
> minimize the number of joins necessary to get to the counties a doctor has
> practices in.
> >
> > Perhaps this design is just stupid and I should just be stopped right
> here. But if not, here goes...
> >
> > If I set up the index in Doctor like this:
> >
> > indexes [lastname, firstname], :as => :name, :sortable => true
> > indexes practices.name, :as => :practice_name, :sortable
> => true
> >
> > And rebuild the index, the SQL of my doctor_core_0 index is something
> like this:
> >
> > CAST(COALESCE("doctors"."lastname"::varchar, '') as varchar) || ' ' ||
> CAST(COALESCE("doctors"."firstname"::varchar, '') as varchar) AS "name",
> > array_to_string(array_agg(COALESCE("practices"."name", '0')), ' ') AS
> "practice_name",
> > FROM "doctors"
> > LEFT OUTER JOIN "doctors_practices" ON "doctors_practices"."doctor_id" =
> "doctors"."id"
> > LEFT OUTER JOIN "practices" ON "practices"."id" =
> "doctors_practices"."practice_id"
> > GROUP BY "doctors"."id",
> > "doctors"."lastname",
> > "doctors"."firstname",
> > "doctors"."id",
> > "doctors"."lastname",
> > "doctors"."firstname"
> >
> >
> > Which works just fine. If a doctor is connected to one practice, a
> single practice name is returned. If connected to multiple practices, each
> practice name is displayed only once.
> >
> > But once I include an index on another relation through my join table,
> for example county, so my index looks something like:
> >
> > indexes [lastname, firstname], :as => :name, :sortable => true
> > indexes practices.name, :as => :practice_name, :sortable
> => true
> > indexes counties.name, :as => :county_name, :sortable
> => true
> >
> > Then the resulting joins in the doctor_core_0 index start playing tricks
> on me. SQL is as follows:
> >
> > SELECT
> > CAST(COALESCE("doctors"."lastname"::varchar, '') as varchar) || ' ' ||
> CAST(COALESCE("doctors"."firstname"::varchar, '') as varchar) AS "name",
> > array_to_string(array_agg(COALESCE("practices"."name", '0')), ' ') AS
> "practice_name"
> > array_to_string(array_agg(COALESCE("counties"."name", '0')), ' ') AS
> "county_name"
> > FROM "doctors"
> > LEFT OUTER JOIN "doctors_practices" ON "doctors_practices"."doctor_id" =
> "doctors"."id"
> > LEFT OUTER JOIN "practices" ON "practices"."id" =
> "doctors_practices"."practice_id"
> > LEFT OUTER JOIN "doctors_practices" "doctors_practices_doctors_join" ON
> "doctors_practices_doctors_join"."doctor_id" = "doctors"."id"
> > LEFT OUTER JOIN "counties" ON "counties"."id" =
> "doctors_practices_doctors_join"."county_id"
> > GROUP BY "doctors"."id",
> > "doctors"."lastname",
> > "doctors"."firstname",
> > "doctors"."id",
> > "doctors"."lastname",
> > "doctors"."firstname"
> >
> > Now, if doctors are connected to multiple practices, all practice names
> and county names are repeated twice. See?
> >
> > Not being very proficient in neither thinking sphinx or rails I'm having
> trouble seeing how to fix this. Perhaps you see a sensible solution? Or
> perhaps I need to rethink my design.. or both. :-)
> >
> > Cheers,
> > Roger
> >
> >
> >
> > kl. 22:52:28 UTC+2 mandag 30. juli 2012 skrev Pat Allan følgende:
> > Hi Roger
> > Not sure exactly what you're referring to - can you provide the SQL
> statement and point out what you think could be more efficient?
> >
> > Cheers
> >
> > --
> > Pat
> >
> > On 29/07/2012, at 5:06 PM, Roger Kind Kristiansen wrote:
> >
> > > Hi again,
> > >
> > > I just noticed that the SQL generated to my sphinx config returns a
> bit more than I expected. I'll not dig to deep into my data model or
> concrete indices first, as I assume this is yet another silly newbie
> mistake which is easy to pinpoint.
> > >
> > > I've got one model which has a few associations (most are through a
> join table, but not all). I've set up some indices and some attributes
> making use of these associations. Now when I manually run the SQL generated
> by TS, I notice the same values from the associated models are repeated
> multiple times in each column. Is this expected behaviour, or is there some
> obvious thing I'm missing which TS doesn't do for me automatically, like
> some explicit grouping or unique constraints or something?
> > >
> > > This hasn't caused me any trouble up until now, but now I'm trying to
> sort by counting the number of entries in one of the associated models and
> the numbers get all screwy. As far as I can understand it's related to the
> mentioned duplication.
> > >
> > > Cheers,
> > > Roger
> > >
> > > --
> > > 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/-/C_gW49zbm6kJ.
> > > 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/-/9_XhUmvM8NQJ.
> > 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/-/SC_h0tYZnJ0J.
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.
