Woah, that's a killer query ;)
Good to know you're going to try the cached column option - I only thought of
it while writing the previous email, but definitely feel like it's the best
option.
As for :without not acting properly, that is confusing... but if it's not a
focus for the moment, then let's not worry about it :)
Cheers
--
Pat
On 26/07/2010, at 1:14 PM, Gedeon wrote:
> 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.
>
--
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.
