possibility to apply complex visibility settings for each search?

[snusmu]

hi all,

on 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
4 is current user id

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)
AND (messages.restricted=0
OR messages.creator_id=4
OR groups_messages.group_id is not NULL)
GROUP BY messages.id

is it possible somehow to apply this visibility setting to thinking
sphinx results?
if yes, please tell me how, if not, it would be cool if you could show
me another way to do it

thanks.

[snusmu]

sorry typed AND instead of WHERE,

actual query is

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 messages.creator_id=4 OR

groups_messages.group_id is not NULL)
GROUP BY messages.id

(same result as)

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

[snusmu]

in fact query can be further simplified,

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

so the question is

can i somehow apply this OR and IN to Message.search "test" with/
with_all? please let me know if it is not possible

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

[Pat Allan]

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.

--
Pat

[snusmu]

Thanks for quick and explanative answer, it helped me understand
better how to do it.

I didn't know about :type => :multi directive, is it documented
somewhere? i'll check out the group.

The information i skipped in my initial message is that the Message
can also be connected to multiple Users, on the same principle as with
Groups, with a similar left join.
and if there is such a connection, User can see it too (even if he
doesn't belong to any Groups Message belongs to).

I'll try to think on solution myself, i imagine that it needs some
further extending of your example with user_ids.

or is it possible to do something like

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

(users_messages.user_id SEPARATOR ','))", :as => :user_ids, :type
=> :multi

and for search

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

OR
:user_ids => [0]+current_user.id
}

but as i understand, "OR" in search is not available, so i'd have to
combine user_ids and group_ids into one somehow, right?

Thanks,
Pavel

[Pat Allan]

Hi Pavel

:type => :multi isn't strongly documented, but whenever you're
defining a manual attribute, you need an explicit type, whether it be
multi, float, integer, etc.

You're correct on each attribute filter in :with being joined with
ANDs, not ORs - it's just how Sphinx works. So, I'm not quite sure how
you can mix user ids into the mix as well... that's tricky.

Good luck finding a solution :)

--
Pat

[snusmu]

here's what i tried

has "if(messages.restricted=0,'0',concat_ws(',',group_concat
(groups_messages.group_id SEPARATOR ','),group_concat(-
messages_users.user_id SEPARATOR ','),-messages.creator_id))", :as
=> :visibility, :type => multi

so basically creating array (f.ex. [-5,-2,-1,0,8,10]) where negative
numbers are user ids[-5,-2,-1], 0 for not restricted and positive nums
for group ids [8,10]
(i know it's weird, but ..)

and for search

Message.search "foo", :with => {

:visibility => [-current_user.id]+[0] +
current_user.message_group_ids
}

but apparently "with" doesn't see a difference between negative and
positive numbers, or there is some conversion which makes it loose the
"-" sign
is there a way to change this behaviour?

also, using the above "has" produced "ERROR: index 'message_core':
sql_range_query: Unknown column 'groups_messages.group_id' in 'field
list' (DSN=mysql://mysql:***@localhost:3306/
simplecommunities_development)."

but this was fixed with

has groups_messages.group_id
has messages_users.user_id

any advice on negative/positive numbers?

i also tried 'u1','u2','u3','u5',0,1,2,4 pattern (same as minus but
with 'u'), but with same result

thanks

[snusmu]

ie when i create a restricted Message, and attach it to user with id 4

and then search

Message.search "foo", :with => { :visibility => [-4] }

i don't find a message

but when i search

Message.search "foo", :with => { :visibility => [4] }

it's there. even though it's not attached to a group with id number 4

so i guess sphinx indexes it correctly, but somewhere there in the
code there is an "abs"

or maybe i have to define somehow the type of the data in multi array?

i'll try digging in the code but an advice would be helpful

thanks,
Pavel

[James Earl]

I'm new to sphinx, so this may be wrong, but I believe sphinx
attributes cannot be negative.

James

[Pat Allan]

Yeah, that's correct - Sphinx only deals in unsigned integers - it's a
pain, but that's just the way things are.

--
Pat

[snusmu]

Thanks for that information,

Do you know what is the maximum size of the integer which can be used?
ie bit size?

i could try to do signing manually, ids range would be halved but it
would solve my problem.

Thanks,

Pavel

[snusmu]

so i found out that number limitation for sending a request to sphinx
is 4294967295 (32 bits - maybe depends on a system)

if i try higher numbers it raises error message trying to convert to
big endian longint in line

[int].pack('N')

at

/vendor/plugins/thinking-sphinx/vendor/riddle/lib/riddle/client/
message.rb

saying

"bignum too big to convert into `unsigned long'"

i think it's more than enough for storing ids on the product i
develop.

but it's going to be a weirdest hack i ever applied in practice.

( Basically the array [-5,-2,-1,0,8,10] is going to be
[2147483648+5,2147483648+2,2147483648+1,0,8,10] instead)

Maybe there is better solution, but i currently don't see any except
writing my own search engine (for what there is unfortunately no
time).

Thanks once again for all the help!