List prefiltering based on user group membership

[bmarkus]

Hi,

I have a table with sensor data for different user groups. I want to show data in a list only owned by the user based on his membership prefiltered dynamically. Sensor data records has an 'owner' field to identify group. This is not necessarily the same as Joomla membership, I need a lookup table with the relationship between Joomla and sensor data ownership. How can I filter the list?

Thanks... B?la

 

[troester]

You can add queries in the prefilter.

Something like
owner IN
value:
SELECT sensor_owner FROM your-lookup-table WHERE joomla-user = {$my->id}
Type: query

 

[cheesegrits]

Depending on the size of the data sets, it might be more efficient to use a pre-query, like...

SELECT sensor_owner FROM lookup_table WHERE joomla_user = {$my->id}

Then in the prefilter, owner equals {$q>sensor_owner}, type 'text'.

Either way would work, but doing the subquery could be slower if it's a huge dataset (which sensor data sounds like it might be).

Also, make sure you have indexes on both fields in the lookup table. Fabrik will automatically build an index on fields you use directly in the prefilter settings, but not on anything you build as your own query.

Sent from my HTC6545LVW using Tapatalk

 

[paulsteigel]

Hi Cheesegrits
My case is typical similar and I would like you to show me how to do:
I have a table name products where:
+ A user can view this entire list (also products belonged to other members);
+ A user can only edit a product that he created, one product created and be approved by Administrator, deletion is disabled and can be done only by Administrator;
In the product table, I have a field user_id to store id of that user.

Can you bright me how to do this properly?
Yours very faithfully
Dang Dinh ngoc

 

[cheesegrits]

You can do most of that with the built in access controls in the list setting, with "or use field" for edit permissions (specifying your user element).

For the approval you could create a checkbox element, with access set so only admins can use it, and have a prefilter for that.

Sent from my HTC6545LVW using Tapatalk

 

[paulsteigel]

Many thanks to Cheesegrits,
So, be a bit more specific, let's me put into steps and please help correcting me.
I have a Products (ID, Name, ProfileLink, Approve, User_Id) table in a database named traceabilily while joomla user is in a database named inforDb

1. I created a list name Products with a field Approved hidden/ unpublished
>> So user will create product and wait for approval from Administrator (here Provincial Authority in Agriculture);
In Prefilter of the list I apply a prefilter with: Where User_id EQUALS to {$my->id} (I know this would be wrong as this will only list product created by this user)
I dont know what to do with Pre-Filter Query

A walk-around
2. I make a menu for this list and make it only to interact available to registered user
3. I copy this list and named as Admin List with field Approve published and make it link to a menu that only available to administrator and remove the WHERE at prefilter section of the list.

With this, I can have administrator fully access to all product and have it approved.
I know the approach is wrong but I dont know how to walk around it. Can you please show me how?
I have to keep the products stay in other database as it link to a mobile application.
Thank you very much for your supports.
Yours very faithfully
Dang Dinh NGoc

 

[cheesegrits]

You can do most of that with the built in access controls in the list setting, with "or use field" for edit permissions (specifying your user element).

In the access controls for the list, set view list to "Registered" (or whatever). Set "View records" to "Administrators" (or whatever) and "Edit records" to "Administrators" (or whatever) with the "or use field" set to your User_id field. That way, both Administrators and the owner of the record will be able to edit it.

-- hugh

 

[p38]

"Depending on the size of the data sets, it might be more efficient to use a pre-query, like..."

Hi Hugh,

Is there perhaps a reason why this option is not available in the menu prefilter. I use this pre-query a lot in the list prefilter, but would really like to have this option in the menu prefilter as well as it gives me complete control over the prefilter via the menu.

Not sure why it would be omitted there?

Paul

 

[cheesegrits]

Because it's a lot of work to add the menu override for a list setting, so we only add ones that are commonly needed / often requested. You are literally the only person ever to ask for the pre-query to be a menu setting, so it's hard to justify the half a day or so of work to code and test it.

Sent from my HTC6545LVW using Tapatalk

 

[p38]

Thanks Hugh, what would you charge to add it in?

 

[cheesegrits]

Hmmm. I'll have to remind myself of what all is involved. I just recall the last time I added something to the menu overrides, it was kind of a pain in the ass. Probably a couple of hours work. If you don't hear back from me within a day, bump this thread. I have a bunch of other stuff I'm in the middle of atm.

-- hugh