Different WHERE for database join for front-end/admin?

[chris.paschen]

We've got a database join element on a form.

We have a joins where of:

WHERE {thistable}.user_id = {$my->id} AND {thistable}.state = 1

This works perfectly in the front-end. The users can only select options from the lookup table that matches their user id.

However, we need to have administrators access the same form on the admin-side but they should not have that filter applied.

We've tried to use the Apply Where To (user access level); however, the administrators are members of the front-end group (authors) that needs to access this with the filter, so it is being applied to administrators as well.

Is there any way to apply the filter on the front-end only, no mater what the level of user?

Would there be a way to do that with an Eval option code?

 

[chris.paschen]

FYI ... I did manage to get this to work by using an Eval option code snippet that uses
$user->authorise
to check to see if the current user is an admin and then manually filter the list there and only return the appropriate ones.

 

[cheesegrits]

I'd have to add an "Apply Where Where: front/back/both" option to achieve that built in.

-- hugh

 

[chris.paschen]

Just curious ... would the way you add the functionality (at the element level) be at all faster (less processing time) than me doing it with an Eval bit of code?
If there's no specific reason to change it (other than ease), it might be better if I just add a sample of my code to the wiki (it took me quite a bit of 'testing' to get my code right, and might help save others time).

 

[cheesegrits]

Well, yes it would be faster, in that it's always faster to hand off filtering to MySQL, so you then only post-process what you need, and don't waste time loading rows you don't need and doing your own filtering at the application level. But "faster" is relative. In this case, probably measured in milliseconds.

-- hugh

 

[chris.paschen]

OK ... we can live with miliseconds for now; however, at some point this database join might involve lots of records (potentially thousands), but we'll revisit this when that time comes. The Eval should be fine for now.
Thanks.

 

[cheesegrits]

Well yes, if thousands of rows are involved, then milliseconds can indeed add up into seconds.

-- hugh
--
"I was told there wouldn't be math"

 

[chris.paschen]

Well, we're still only at under 100, so we can live with this for a while.
Thanks for the help with the math!

 

[cheesegrits]

Looks like you Pro sub expired a while back.

-- hugh