Limiting search results depending on authorization level

[Gedeon]

Hello!

I'm adding a search feature in a project and just started using Sphinx
and Thinking Sphinx about a week ago. I'm seeking some help from
Sphinx Gurus here, can any of you help me? I would be extremely
grateful, as I've been stuck on this for a couple of days already.

I basically have 3 models involved in this:

Project:
has_many :members, :through => :memberships, :source => :user

Membership, which links users to projects

User:
has_many :projects, :through => :memberships

Users should be able to search for projects. However, depending on
their access level, they can see more or less projects:

- Admin users can see all projects (public and private). So I got
something like this:
Project.search options[:q], :page => 1, :per_page => 10
Simple and easy, I can handle that.

- Normal users can only see projects they are member of. How can I do
this?
I guess (but i could be completely wrong) I need this in my Project
index:
has members(:id), :as => :member_ids
But how can I use it? Or what should I use?
Project.search options[:q], :page => 1, :per_page => 10,
:with => { } # member_ids.include?(@user.id)

- I kept the best for the end : Moderators can view all public
projects and all projects they are a member of.It's basically the same
as a normal user, with some "OR" condition cream on top of it:
Project.search options[:q], :page => 1, :per_page => 10,
:with => { :private => 0 } # OR member_ids.include?
(@user.id)

I'm already using the "OR" trick to search within my contacts but I
cant apply it here as it only works when the permission is within the
same model:
has "IF(view_permission = 0, 0, contact_owner)", :as => :owner, :type
=> :integer

Any idea how to deal with moderators and normal users searches?
Thanks a lot to everyone for at least reading this. Extra thanks for
those who can help!

PS: In the worst case scenario, is there a way of "unpaginating" the
results i get from TS, removing projects that don't match the
conditions, then re-paginating? I know that's a terrible solution and
I want to avoid it at all costs. However, we'll never have more than
100 projects in the database and I'd rather have a that than no search
at all.

[Pat Allan]

Hi Gedeon

I think the best way forward is to create another custom SQL snippet - if the project is public, return 0, otherwise, return the member ids... and then you can filter on :member_ids => [0, @user.id] for moderators, and :member_ids => @user.id for normal users. I think this will work, but haven't thought about it too much.

Keep in mind you'll want to set the attribute's type as :multi.

Hope this helps

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
>

[Gedeon]

Hi Pat,

Thanks for your reply!
The :multi attribute type is something i was missing!
I'm going to try that now.

Gedeon

[Gedeon]

Hmm one more thing. This might be silly and mysql related more than
TS, but how can I retrieve member_ids from an IF snippet?
I can't see anything like that in the MYSQL api.

I'd need "IF(private = 0, 0, *some way of retrieving member ids from
the memberships table*)"

If you have any idea, please let me know. In the mean time, i'm
searching and will post any solution I find.

Thanks a lot!

Gedeon

On Apr 9, 8:35 am, Pat Allan <p...@freelancing-gods.com> wrote:

[Pat Allan]

You will need to force the join to the members table using another (normal) attribute or field, if you're not doing that already...

has members(:id), :as => :normal_member_ids

In your SQL snippet, you'll want to merge all ids together into a string, separated by commas. GROUP_CONCAT or CONCAT_WS will be helpful for that.

Cheers

--
Pat

[adamcooper]

Just wanted to chime in that we do this approach in almost all our
models and you can do very complex authorizations.

We use a custom sql query for the MVA rather than forcing the join in
the main index. I guess it depends on how complex it is to get your
authorization_ids. In our case we have to join three separate tables
to get the ids we are looking for. I think pulling it out into a
separate query is a little bit easier on the database.

Here is a sample complex case for you example: (Even expanding if you
had admins/moderators/members)

has WRITE_ID_SQL, :as => :write_ids, :type => :multi
has READ_ID_SQL, :as => :read_ids, :type => :multi

WRITE_ID_SQL = <<-END select project.id * 3 + 2 as id,
IF((memberships.level = #{Privacy::MODERATOR} OR memberships.level =
#{Privacy::ADMIN}, IFNULL(memberships.member_id, 0), 0) as write_ids
from projects
inner join memberships on projects.id = memberships.project_id
END

READ_ID_SQL = <<-END select project.id * 3 + 2 as id,
memberships.member_id as read_ids
from projects
inner join memberships on projects.id = memberships.project_id
END

Then when you do your search all you have to do is pass in the user_id
of the current_user into the read_ids and write_ids depending on which
type of permission you checking for.

Cheers,
Adam

[Gedeon]

Hi!
Thanks a lot Pat and Adam, I'm going to study that a bit and see how I
can use it properly in my case.

[Gedeon]

Hi again,

I just tried this, following Pat's recommendations ,but i can't get it
to work... Can you let me know what I'm doing wrong?

I have:

has members(:id), :as => :member_ids, :type => :multi
has "IF(private = 0, 0, GROUP_CONCAT(memberships.id))", :as
=> :members_or_public, :type => :multi

Which should return 0 if the project is public or a list of member_ids
if it is private.

Then, in my search:

res = ThinkingSphinx.search options[:q], :classes =>
[Project], :page => 1, :per_page => 10,
:with => { :members_or_public => [0, @user.id] }

This does not get me any result. However, If I remove the "With"
condition, I can get search results.
I cannot see any example of ...
:with => { :multi_value_attribute => [ array_of_values] }
... in the documentation, so i'm not sure how to do it..

Thanks again for your time and help!

Gedeon

[Pat Allan]

You can use arrays for any integer filter - it will return documents that have any of the values of the array in that attribute (NOT necessarily *all* of the values - use :with_all for that behaviour).

Also, you probably want to return the public members_or_public value as a string, not an integer - '0' instead of 0. Not sure if that's the cause of the problem, though.

And have you restarted Sphinx as well as re-indexing? rake ts:rebuild does this all in one shot.

Cheers

--
Pat

[Gedeon]

Hi Pat!

Thanks a lot for your help! It works just fine now! i guess the
mistake i did was the one you noticed: 0 instead of '0'!

I really appreciate it :-)


Gedeon

> >>>  inner join memberships onprojects.id = memberships.project_id

> >>>  END
>
> >>> READ_ID_SQL = <<-END select project.id * 3 + 2 as id,
> >>> memberships.member_id as read_ids
> >>>  fromprojects

> >>>  inner join memberships onprojects.id = memberships.project_id

> >>>>>>> Users should be able to search forprojects. However, depending on

> >>>>>>> their access level, they can see more or lessprojects:
>

> >>>>>>> - Admin users can see allprojects(public and private). So I got

> >>>>>>> something like this:
> >>>>>>> Project.search options[:q], :page => 1, :per_page => 10
> >>>>>>> Simple and easy, I can handle that.
>

> >>>>>>> - Normal users can only seeprojectsthey are member of. How can I do

> >>>>>>> this?
> >>>>>>> I guess (but i could be completely wrong) I need this in my Project
> >>>>>>> index:
> >>>>>>> has members(:id), :as => :member_ids
> >>>>>>> But how can I use it? Or what should I use?
> >>>>>>> Project.search options[:q], :page => 1, :per_page => 10,
> >>>>>>>           :with     => {  }  # member_ids.include?(@user.id)
>
> >>>>>>> - I kept the best for the end : Moderators can view all public

> >>>>>>>projectsand allprojectsthey are a member of.It's basically the same

> >>>>>>> as a normal user, with some "OR" condition cream on top of it:
> >>>>>>> Project.search options[:q], :page => 1, :per_page => 10,
> >>>>>>>           :with     => { :private => 0 }  # OR  member_ids.include?
> >>>>>>> (@user.id)
>
> >>>>>>> I'm already using the "OR" trick to search within my contacts but I
> >>>>>>> cant apply it here as it only works when the permission is within the
> >>>>>>> same model:
> >>>>>>> has "IF(view_permission = 0, 0, contact_owner)", :as => :owner, :type
> >>>>>>> => :integer
>
> >>>>>>> Any idea how to deal with moderators and normal users searches?
> >>>>>>> Thanks a lot to everyone for at least reading this. Extra thanks for
> >>>>>>> those who can help!
>
> >>>>>>> PS: In the worst case scenario, is there a way of "unpaginating" the

> >>>>>>> results i get from TS, removingprojectsthat don't match the

> >>>>>>> conditions, then re-paginating? I know that's a terrible solution and
> >>>>>>> I want to avoid it at all costs. However, we'll never have more than

> >>>>>>> 100projectsin the database and I'd rather have a that than no search