Hi Pat!
Thanks a lot for the advice! Caching the values in a new column seems
the best solution by far. Handling all that in rails will be a lot
easier and save much trouble!
I am still confused about the "without" not working as I expected, but
since I'm working and not making a research, I'll have to leave it
behind...
FYI, I made some progress here also, grouping all queries into a big
monster that i added in the config file. You might not want to read it
since it will most likely not be needed after all.
CONCAT_WS(',', `people`.`contact_owner`, GROUP_CONCAT(DISTINCT
`shared_contacts`.`user_id` SEPARATOR ','),
GROUP_CONCAT(DISTINCT `user_work_fors`.`user_id` SEPARATOR ','),
IF(people.view_permission = 0, '0', NULL)) AS 'viewers',
CONVERT(GROUP_CONCAT(DISTINCT
IFNULL(`shared_removed_contacts_people`.`user_id`, '0') SEPARATOR ',')
USING latin1) AS `removed_owners`
FROM `people`
LEFT OUTER JOIN `shared_contacts` ON shared_contacts.person_id =
people.id AND shared_contacts.flag = 1
LEFT OUTER JOIN `users` ON `users`.id = `people`.contact_owner
LEFT OUTER JOIN `user_work_fors` ON user_work_fors.work_for_id =
users.id AND relationship_type = 0
LEFT OUTER JOIN `shared_contacts` shared_removed_contacts_people ON
shared_removed_contacts_people.person_id = people.id AND
shared_removed_contacts_people.flag = 0
Thanks again Pat, you're really of a great help!
Gedeon
.
On Jul 24, 7:53 pm, Pat Allan <[email protected]> wrote:
> Hi Gedeon
>
> (Wrote this offline yesterday, before your new response... will reply to that
> too)
>
> Sounds like an MVA is the best fit for what you're trying to do - but yeah,
> with all those queries, it does complicate things. Shifting the queries out
> to a stored procedure sounds like a good approach to me - although it will
> slow things down quite a bit...
>
> Maybe it's possible to combine all the queries into one? Here's a guess,
> though I don't know how accurate it is:
>
> SELECT contact_owner, shared_contacts.user_id, uwfs.user_id
> FROM people
> LEFT OUTER JOIN shared_contacts ON people.id = shared_contacts.person_id
> LEFT OUTER JOIN users AS owner ON owner.id = people.contact_owner
> LEFT OUTER JOIN user_work_fors AS uwfs ON owner.id = uwfs.work_for_id
> LEFT OUTER JOIN shared_contacts AS assistants ON assistants.person_id =
> owner.id
> WHERE people.id = 92
> AND shared_contacts.flag = 1
> AND assistants.flag = 1;
>
> Of course, you'd need to concatenate all those values together, and separate
> them with commas for the MVA attribute output.
>
> Anyway, wrap it all in a stored procedure, then add the new attribute with
> something like:
> has "viewers_for(contact_owner)", :as => :viewers, :type => :multi
>
> An alternative, which may end up being cleaner, is to cache the stored
> procedure value in a new column, and just set it using ruby code instead.
>
> Hope this is helpful :)
>
> --
> Pat
>
> On 20/07/2010, at 5:54 PM, Gedeon wrote:
>
>
>
>
>
>
>
> > Hello Pat,
>
> > Thinking Sphinx is doing a great job for us lately, thanks to your
> > help a couple of months ago!
> > I am now coming back with a question, as I need would some advice from
> > the expert...
>
> > We have a Contacts database that was indexed fairly easily until
> > recently. A new feature was added in our project which allows users to
> > share contacts.
> > I have to make that searchable and it turns out to be some kind of
> > nightmare.
>
> > I would need to replace our current:
> > has "IF(view_permission = 0, 0, contact_owner)", :as => :owner, :type
> > => :integer
> > into a "has viewers" parameter to filter the results. Instead of
> > 'contact_owner',I would have a list of user ids whom can view the
> > contact.
> > That list of ids is basically a concatenation of the results from 4
> > queries (at the end of this message).
>
> > I don't see this fitting in a "has" parameter. Could you recommend me
> > a way of doing this?
>
> > I was thinking of using a stored procedure, but AFAIK it's not
> > possible to call a stored procedure from a "has"...
> > Linking the queries using "union"? But AFAIK, I cannot concatenate the
> > result set from UNIONs into a string that can be used by TS.
> > Tweaking the config file? I'd rather not do that as it would cause
> > changes in many other places (deployments, cron jobs, new instances of
> > the project, team notification,...) but if it's the only solution, I
> > can give it a try.
> > Something else?
>
> > The 4 queries, for person 92 in this case:
>
> > - The contact owner:
> > SELECT contact_owner from people where id = 92;
>
> > - Users with whom the contact is manually shared:
> > SELECT shared_contacts.user_id
> > FROM people, shared_contacts
> > WHERE people.id = 92 AND people.id = shared_contacts.person_id AND
> > flag = 1;
>
> > - The owner's assistants:
> > select uwfs.user_id
> > FROM people, users owner, user_work_fors uwfs
> > WHERE people.id =92
> > AND owner.id = people.contact_owner
> > AND owner.id = uwfs.work_for_id
>
> > - From all this, I need to remove assistants from whom the contact is
> > manually hidden:
> > SELECT shared_contacts.user_id
> > FROM people, shared_contacts
> > WHERE people.id = 92 AND people.id = shared_contacts.person_id AND
> > flag = 0;
>
> > Thank you,
> > Best regards
>
> > Gedeon
>
> > --
> > 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
> > athttp://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.
