Create variable where clause parameters from User Meta (similar to $USERID$ )

  • Resolved trkaushik

    (@trkaushik)


    2 years, 1 month ago

    I have a requirement to filter records dynamically using the a meta-key / meta-value from usermeta data table . It is dynamic because each user has a “team_id” assigned as metakey, so when the report is viewed by the logged in user, it auto-filters and shows only information of all the members of the users team dynamically. So if user belongs to “TEAM_A” (meta-key is “team_id” and meta-value is “TEAM_A”) and if in the table in context there is column called “team”, i want to filter all rows from “team” column, which satisfies the condition of the team that the user belongs.

    Kindly advise how we can achieve this, if possible with a sample.

    Also, Peter you are doing a great job..i could sucesfully create a star-schema view out of a fact table of records and dimension tables. (I am going to try extended star schema ?? )

Viewing 5 replies - 1 through 5 (of 5 total)
  • Thread Starter trkaushik

    (@trkaushik)

    Basically, how we can create $TEAM_ID$ or $$TEAM_ID$$ for use in our where clause. thanks

    Thread Starter trkaushik

    (@trkaushik)

    There was a similar question earlier, but i guess is not open to continue. https://www.ads-software.com/support/topic/how-to-interact-with-wordpress-user-metadata/

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @trkaushik,

    WP Data Access stores the WordPress user ID in a database session variable for any database connection, including remote connections. You can use this variable in a subquery in your where clause. This feature is explained here:

    https://wpdataaccess.com/2022/08/18/wordpress-user-id-in-sql/

    Please be aware that you can also use the WordPress user ID directly in views. This requires a database function as it is not possible to use session variables in view. Such a function needs to be be added explicitly. This is also explained on the page mentioned above.

    Hope this helps,
    Peter

    Thread Starter trkaushik

    (@trkaushik)

    yes, the logged in user is available as the variable…but can we have some extrapolation of the logic, so metakey/metavalue combinations can be also filters…that will make it super userful, as the logic for metakey/metavalue can be used to filter.. folks having same last name, or folks belonging to same school, or folks belonging to same city, can all be filtered…as these all can be metakeys/metavalue combination. $$metakey|metavalue$$ can be filter variables based on user logged in. (all from the underlying usermeta table)

    thanks!

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi @trkaushik,

    That is possible if you write your own CPT query and store it as a custom query. But it won’t work for the custom post type data source.

    What is the data source of your table? Would it help if we make the query available (copy/paste) to allow further customizing? Any other ideas…?

    Sound like a candidate for a possible enhancement… ??

    Thanks,
    Peter

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Create variable where clause parameters from User Meta (similar to $USERID$ )’ is closed to new replies.