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].
>> > 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/-/9_XhUmvM8NQJ.
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.
