Prefilter to show records of the login user

[stevelis]

Have the following list Calendar with various elements storing the details for events.
Each event can have up to 3 stewards appointed to an event which is done by the following three database join elements rendered as dropdown boxes from a List called Steward.
The databasejoin for the Steward list uses the List?s id, and concat two elements from the Steward's List fname and lname, to appear in a dropdown box as a full name of the steward.
calendar___chairsteward; label ? Chair of Stewards;
calendar___steward2; label ? Steward;
calendar___steward3; label ? Steward;
The feature I require is display the details of each row of the Calendar List where the logged in user's name shows in any one of the above three elements. The list is to be displayed via a menu option
I have tried using the {$my->id} function as a pre-filter option, but no records are displayed
The 'where' option was not appearing so I tried both And as well as Or with the following
field = Chair of Stewards(raw)
condition: equals
value: {$my->id}
type=text

Any help would be greatly appreciated to get this working

Steve
Australia

 

[troester]

{$my->id} contains the Joomla userId.
So your list steward must contain (or somehow point to) the Joomla userid belonging to the name.

 

[stevelis]

Hi troester - thanks for the clarification
Would part of the problem be the name element in the Steward list is a calculated element based on two field elements fname and lname from the Steward list?
The Steward list also has a field element, J_id and the Steward form has the juser plugin with the following settings
Name > name - as described above
Username > username is a field element
Password > password is a password element
Email > email is a field element
Block Filed > is blank
User ID field > Jid is a field element
User Group field > status name is an access element
All 4 Bypass J Setting and the 4 Sync Settings are all set to Yes.

Can {$my->id} still be used in the Pre Filter for my situation?
Or would it be necessary to write some coding that would display the information.
If it is coding, some pointers would be very much appreciated

Steve
Australia

 

[cheesegrits]

It is inadvisable to filter on names, because names are not unique. Which John Smith should see John Smith's records?

So in this case, you would probably need to use a subquery in the pre-filter, to pick up the steward table id with the matching userid (J_id). So the 'value' of the filter(s) would need to be a query that returned the id of your stewards list which contain the logged on user's id. Which would be something like this:

SELECT id FROM stewards WHERE J_id = '{$my->id}'

And you'd need three filters, using an OR between them, one for each steward join element. Each using the above query, with the values 'type' set to 'Query'.

Modify that query as appropriate for the actual table (stewards) and Primary Key (id, the one you use as the value for your Stewards joins) names.

-- hugh

 

[cheesegrits]

Note that it would be slightly more efficient to use the pre-query mechanism, and lookup the Stewards table id for the matching J_Id there with a similar query, which you could then simply refer to as {$q->id} in your three pre-filters, rather than repeating the same dependent subquery three times. See the tooltip on the pre-query for details.

-- hugh

 

[stevelis]

Thanks Hugh
I will give this a go in the morning (Friday) as I in Melbourne for the F1 and V8 Supercars.
We are not on track until 1430hrs.


Steve

 

[stevelis]

Hi Hugh
Thanks for the above examples and explanations which have enable me to achieve the desired outcomes.
Your first suggestion worked the first time, so currently using that one.
Will explore the second suggestion {$q->id} once I have fully understand how it works from tool tips or wiki

Thanks again
Steve
Australia

 

[stevelis]

SELECT id FROM steward WHERE J_id = '{$my->id}' is working as explained by Hugh above. (i.e. 3 times)
However, when I add another line to the pre-filter to display the same results as above, but before a certain date all records are displayed with the following
AND
Element = Enddate
Condition = Less than or equals
Value = curdate()
Type = No Quotes
When I use the above pre-filter by itself, only, it displays only those records that are before the stipulated date correctly.
Any ideas or suggestions

Thanks
Steve
Australia

 

[troester]

May depend on the grouping options you have (or don't have) set.
You can enable debugging in Fabrik options and then append &fabrikdebug=1 (or ?fabrikdebug=1 if there's no other URL parameter) to your list URL.
A click on "list GetData: your-list" will show you the generated query.

 

[stevelis]

Hi troester
Thanks for your reply and I will have a go with your suggestion.

The above pre-filters are applied via the JMenu Manager>Fabrik List>FabrikList Options>Pre-filters where you don't have the option of grouping as with the normal List pre-filter option.
In this case would it be better to copy and rename the List, apply the pre-filters to that List, and then use the renamed List in the Menu Manager>Fabrik List>
Would I assume correctly to Where for the date filter and then group the 3 id filters

Thanks
Steve
Australia