What are the filter keywords for Custom SQL query filters

What are the filter keywords for Custom SQL query filters

by James Henestofel -
Number of replies: 9

There are five options to choose filters from when building a custom SQL query.

  1. Courses - %%FILTER_COURSES:
  2. Categories - %%FILTER_CATEGORIES:
  3. Start Time/End Time - %%FILTER_STARTTIME: AND %%FILTER_ENDTIME:
  4. User Field Filter - ?
  5. User Field Search Box - ?

What are the keywords for #4 and #5 to put in the sql query?

I'm trying to add a User Field Filter of the 'idnumber' and I can't get this to work.  I've tried the below and some other variations of keywords

SELECT
    *
FROM
    prefix_user as u
%%FILTER_FUSERFIELD_IDNUMBER:u.idnumber%%

Average of ratings: Useful (3)
In reply to James Henestofel

Re: What are the filter keywords for Custom SQL query filters

by Tim Obezuk -

Hey everyone,

I also have not been able to find detailed documentation on this functionality.

Does anyone have more information?

Average of ratings: -
In reply to James Henestofel

Re: What are the filter keywords for Custom SQL query filters

by Michael E -
Picture of Core developers Picture of Testers

I finally figured it out for search text. You have to use:

 

%%FILTER_SEARCHTEXT:u.lastname:~%%

 

Replace u.lastname with the field you want to search.

The ~ is the operator for "like"

 

I will also add this to the documentation.

Average of ratings: Useful (1)
In reply to James Henestofel

Re: What are the filter keywords for Custom SQL query filters

by James Henestofel -

My apologies first off.  The version of the block I'm using is 2.2.  For that version I've figured out my answer as below.

  1. Courses - %%FILTER_COURSES:
  2. Categories - %%FILTER_CATEGORIES:
  3. Start Time/End Time - %%FILTER_STARTTIME: AND %%FILTER_ENDTIME:
  4. User Field Filter - %%FILTER_USERS:u.idnumber%%  Change "idnumber" to the field that you choose on the filter page.
  5. User Field Search Box - Looks to be the same as "User Field Filter" in the code except using LIKE but can't seem to get to work.  Might be something incorrect in the coding.
Average of ratings: Useful (1)
In reply to James Henestofel

Re: What are the filter keywords for Custom SQL query filters

by James Henestofel -

I've come up with the 2.3 keywords by looking through the code.  In no way is this complete but it is a pretty good start on what should probably be converted to the docs for the plugin.  Hope this helps someone

  1. Categories - searches by id of category

    1. %%FILTER_CATEGORIES:columnname

  2. Category-Sub Categories - Uses LIKE and CONCAT to add “%/SEARCHITEM%” and uses category id for path

    1. %%FILTER_SUBCATEGORIES:columnname

  3. Course Module - Couldn't figure out a way to explain how this works

    1. %%FILTER_COURSEMODULEID:columnname

    2. %%FILTER_COURSEMODULEFIELDS:columnname

    3. %%FILTER_COURSEMODULE:columnname

  4. Course User(id) - Builds dropdown list from current course users and searches by user id

    1. %%FILTER_COURSEUSER:columnname

  5. Courses

    1. %%FILTER_COURSES:columnname

  6. Enrolled Students

    1. %%FILTER_COURSEENROLLEDSTUDENTS:columnname

  7. Role

    1. %%FILTER_ROLE:columnname

  8. Search Text - If it finds :~ uses LIKE otherwise uses custom operator such as =, <, >, <=, >=

    1. %%FILTER_SEARCHTEXT:columnname:symbol

  9. Semester(Hebrew) - Uses LIKE comparison

    1. %%FILTER_SEMESTER:columnname

  10. Start/End date

    1. %%FILTER_STARTTIME:columnname

    2. %%FILTER_ENDTIME:columnname

  11. System User(id) - Builds dropdown list from all users  in system and searches by user id

    1. %%FILTER_SYSTEMUSER:columnname

  12. User Field - Uses LIKE comparison and adds % to beginning and end of search item

    1. %%FILTER_USERS:columnname

  13. Year (Hebrew) - Uses LIKE comparison and adds % to beginning and end of search item

    1. %%FILTER_YEARHEBREW:columnname

  14. Year (Numeric) - Uses LIKE comparison and adds % to beginning and end of search item

    1. %%FILTER_YEARNUMERICcolumnname

Average of ratings: Useful (4)
In reply to James Henestofel

Re: What are the filter keywords for Custom SQL query filters

by Ajit surendar R -

I am not able to search with %%FILTER_SEARCHTEXT:u.lastname:~%%

I have tried to use the below query given in the moodle ad-hoc reports page. I am trying to develop a custom SQL report with the search text as a search field. I have added User field search box in the filter and selected the field as a lastname.

I am using the below query. Please suggest some way to achieve text search in configurable SQL report. Thanks!


SELECT u.firstname, u.lastname

FROM prefix_user u

%%FILTER_SEARCHTEXT:u.lastname:~%%


Average of ratings: -
In reply to Ajit surendar R

Re: What are the filter keywords for Custom SQL query filters

by Michael E -
Picture of Core developers Picture of Testers
Ajit,
I recall something that you have to have a WHERE clause. Otherwise the filters do not work.
If you do not have any criteria, just do one which is always true (for example: 1=1). So this should work;

SELECT u.firstname, u.lastname

FROM prefix_user u

WHERE 1=1

%%FILTER_SEARCHTEXT:u.lastname:~%%


Average of ratings: -
In reply to Michael E

Re: What are the filter keywords for Custom SQL query filters

by Ajit surendar R -
Thanks for your reply Mike. Still i am facing the same issue. I couldnt refine my search results by using the below sad


SELECT u.firstname, u.lastname

FROM prefix_user u

WHERE 1=1

%%FILTER_SEARCHTEXT:u.lastname:~%% 

Average of ratings: -
In reply to Ajit surendar R

Re: What are the filter keywords for Custom SQL query filters

by Michael E -
Picture of Core developers Picture of Testers

Strange. It is working fine for me. Are you sure you are using the right filter (Search Text)?

Do you get any error message?

Average of ratings: -
In reply to Michael E

Re: What are the filter keywords for Custom SQL query filters

by Ajit surendar R -

Yes Mike. Using this filter User field search box  and i am not getting any error, but am not able to refine my search result.


Below is the thing that i have given in the filter and searching with the last name of the user.



ID numberNameSummaryEdit
c1User field search boxlastnameeditdelete
and below is the query:


SELECT u.firstname, u.lastname

FROM prefix_user u

where 1=1

%%FILTER_SEARCH:u.lastname%%

Average of ratings: -