Skip to main content

Filter function based on related data

Here, we will introduce how to use the filter function to search for related data as a condition. By creating a filter in the format of :R(%module_name%:q|%filter_query%|), you can specify filtering conditions for related information, and you can search flexibly by combining keywords and tags.

info

:R() search can only be performed for the primary language. Please note that even if the Multi-language setting is enabled, the associated content will still be based on the primary language.

How to set up​

Preparation​

In order to narrow down related information, first set the modules that are allowed to be used with filter_request_allow_list. Write filter_request_allow_list in the following format.

:q|%module_name%:[%column_name1%,%column_name2%]

Example: :q|topics:[topics_group_id,subject]
-> Allow to specify topics_group_id, subject in subquery.

When specifying extended content fields in a subquery, please also specify the content structure ID.

Example: :q|topics[topics_group_id=10]:[ext_1,relation]
-> Allows specifying the extended content fields ext_1 and relation in the subquery.

You can also specify a subquery with :ALL like :q|tag:[:ALL]. For subqueries that can be specified, refer to Available items.

Image from Gyazo

Change in　filtering conditions​

For Topics, Tag, and Inquiry, the default filtering conditions change according to the allow_list settings.

  • Topics, Tags
    • If the allow_list does not include open_flg, it searches only open content/tags.
    • If the allow_list includes open_flg (or :ALL), it searches including private contents/tags.
  • Inquiries
    • If the allow_list does not include status, it searches only active forms.
    • If the allow_list includes status (or :ALL), it searches including inactive forms.

Example of filter specification​

Get the tag associated with the contents of topics_group_id=1 using Tag::list API​

filter: :R(topics:q|topics_group_id eq 1|)
filter_request_allow_list: :q|topics:[topics_group_id]
Image from Gyazo

Get the content associated with the tag with tag_id=50 using the Topics::list API​

filter: :R(tag:q|tag_id in [50]|)
filter_request_allow_list: :q|tag:[tag_id]
Image from Gyazo

Perform an AND search with a keyword and tag using the Topics::list API​

filter: search_keyword contains "test" AND :R(tag:q|tag_id in [50]|)
filter_request_allow_list:[search_keyword,:q|tag:[tag_id]]
Image from Gyazo

Perform an OR search within a specific tag using the Topics::list API, and then perform an AND search for each of the results.​

filter: :R(tag:q|tag_id in [7,8,9]|) AND :R(tag:q|tag_id in [5,6]|)
filter_request_allow_list: :q|tag:[tag_id] Image from Gyazo

Get the content without tags set in the Topics::list API​

filter: :R(tag:empty)

Image from Gyazo

Get content that has no related content matching a condition in the Topics::list API​

Use :NR() to retrieve data that has no related data matching the :R() condition.
While :R() means "related data matching the condition exists", :NR() is its negation.
The syntax and the way to specify filter_request_allow_list are the same as for :R().

filter: :NR(topics[topics_group_id=25]:q|topics_group_id eq 25|)
filter_request_allow_list: :q|topics[topics_group_id=25]:[topics_group_id]
-> Retrieves content that is not related to any content with topics_group_id=25.

caution

To filter only by whether related data exists, you cannot use :empty with a content structure ID such as :R(topics[topics_group_id=25]:empty). With :empty, the content structure ID is ignored and the check is made against the whole module (in this example, all content). To filter by whether a relation to a specific content structure exists, combine :NR() with a subquery as shown above.

Retrieve tags associated with content where topics_group_id=1,2, ext_1="val", and checkbox="1" using the Tag::list API​

caution

When specifying an extended item (ext_X) in a subquery, be sure to also specify the content structure ID.

filter: :R(topics[topics_group_id=1,2]:q|ext_1 = "val" AND checkbox = "1"|)
filter_request_allow_list: :q|topics[topics_group_id=1,2]:[ext_1,ext_2,text,checkbox]

Image from Gyazo

Notes​

When filtering based on the extended fields of related content, you can specify the content structure ID as topics_group_id=1 or in a shortened form as 1. However, you must ensure that the parameters specified within the brackets in allow_list and filter match.

  • Working patterns
    • filter_request_allow_list: topics[topics_group_id=1]
    • filter: topics[topics_group_id=1]
  • Non-working pattern (1)
    • filter_request_allow_list: topics[topics_group_id=1]
    • filter: topics[1]
  • Non-working pattern (2)
    • filter_request_allow_list: topics[1,2]
    • filter: topics[2,1]

Available items​

The available module_name and its subqueries (column_name) are as follows.

Topics​

topics_id
topics_group_id
contents_type
subject
open_flg
topics_flg
regular_flg
inst_ymdhi
update_ymdhi
ext_x(slug)

Member​

member_id
group_ids
inst_ymdhi
update_ymdhi

Inquiry​

inquiry_id
inquiry_name
status
inst_ymdhi
update_ymdhi

Tag​

tag_id
tag_nm
open_flg
inst_ymdhi
update_ymdhi
tag_category_id
ext_col_01
ext_col_02
ext_col_03
ext_col_04
ext_col_05
ext_col_06
ext_col_07
ext_col_08
ext_col_09
ext_col_10

Support

If you have any other questions, please contact us or check out Our Slack Community.

Contact form
Join Kuroco Slack community