rails - apply complex visibility settings to thinking sphinx results

Question

in the product i'm developing, i have a Message model.

Message can be restricted to groups, or not restricted (available to everyone).

If user belongs to one of Message's groups OR message is not restricted, user can see the message.

here is the query selecting visible messages (in hope that it can clarify what i mean)

(2,3,4,5,6,1) are the groups user belongs to, they are different for each user

SELECT `messages`.* FROM `messages`

LEFT JOIN groups_messages ON
messages.id=groups_messages.message_id AND groups_messages.group_id in (2,3,4,5,6,1)

 WHERE (messages.restricted=0 OR groups_messages.group_id is not NULL)

GROUP BY messages.id

here is analogical query using a subquery, in hope it helps to clarify what is needed

SELECT * FROM `messages` WHERE 
(
 restricted=0 OR id in ( select distinct message_id from groups_messages where group_id in  (2,3,4,5,6,1) )
 )

is it possible somehow to apply this visibility setting to thinking sphinx results? meaning to apply this OR and IN to

Message.search "test" with/with_all

?

if it is not possible, another question would be - is it somehow possible to get ids of all objects found in search,

so that i could perform query myself, just adding AND to my WHERE condition

SELECT * FROM `messages` WHERE
(
 restricted=0 OR id in ( select distinct message_id from groups_messages where group_id in (2,3,4,5,6,1) )
)
AND id in (ids_of_the_messages_found_by_thinking_sphinx)

i imagine both the query without LEFT JOIN and adding AND to WHERE will be a bit resource intensive for mysql, but if other solutions are not possible, then this would do

thanks,

Pavel K

Answer 1

received a response from Pat Allan, developer of Thinking Sphinx,

link text

I think the best way is to build a string that includes 0 if the
message is unrestricted, otherwise returns the group ids, concatenated
together with commas... ie: "2,3,4,5,6" or "0"

So, you'll want to build a SQL snippet for an attribute, something
vaguely like:

has "IF(messages.restricted = 0, '0', GROUP_CONCAT (groups_messages.group_id SEPARATOR ','))", :as => :group_ids, :type => :multi

And then for searching:

Message.search "foo", :with => {
 :group_ids => [0] + current_user.message_group_ids
}

The SQL snippet will have to be different if you're using PostgreSQL, though... let me know if that's the case.

will try that