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