Using Named Queries in Studio Server – Help Center

Named Queries are pre-defined queries with which users of Studio and Studio for InDesign and InCopy can quickly perform a search in Studio Server to locate particular files.

Users of Studio and Studio for InDesign and InCopy do this by choosing the Named Query in the Search box (Studio) or the Studio panel (Studio for InDesign and InCopy). After selection, the query is automatically executed.

Optionally, fields for further filtering the search results can also be made available.

Named Queries can be seen as static search templates that are made available by the system administrator to all users and which cannot be changed by the end user.

Components

A Named Query consists of the following components:

Query name. Name of the query as it should appear in the client applications:
The Search menu of the Studio panel in InDesign or InCopy.
The list in the Search box of Studio

Comment. Optional administrator comment about the query, for internal reference only.
Interface. List of query parameters to appear as filter options in the interface for further narrowing down the search results. Each parameter must start on a new line, respecting the following syntax:

name as it appears in the interface,object type,default value,list value 1/list value 2/...

Example: Here, two fields are added: one field of type 'string' in which a user can type custom text, and another of type 'integer' with a default value of 72:

name,string,,

dpi,int,72,

This will appear as follows in the interface (shown is Studio):

Example of filter fields

Name. (Mandatory.) An object of type 'string', used as the label for the field in the interface.
Object type. (Mandatory.) Must be one of the following:
string
bool
int
list

Default value. (Optional.) Initial value of the parameter.

List values (Optional.) Can be used when the object type is 'list'. Holds the values of the list. Each entry must be separated by a forward slash ( / ). When no default is set, the first entry is pre-selected.

Example: Here, a list named 'list of items' contains 3 items of which item 2 is the default:

list of items,list,item 2,item 1/item 2/item 3

Query SQL. The SQL statement for this Named Query. The parameters can be used inside the SQL with $parametername. For string values, this needs to be surrounded by double quotes ("). The name of the user executing the query can be used in the SQL statement as "$user".

Note: The SQL statement must contain the following:

/*SELECT*/ 
/*FROM*/ 
/*JOINS*/ 
/*WHERE*/ 
comments

Check access. Defines if a check should be made whether or not the user has sufficient rights to access each returned file. If the user does not have sufficient rights, the file is not included in the search results.

Note: Enabling this option can have a negative impact on performance (longer waiting times for the user).

Tip: Disable this option when it is known beforehand that all users are authorized to use the files, such as a query that locates all templates.

Example

Here, a Named Query is created that lets users find files that have a certain DPI value set. The default search is for a DPI value of 72. Users can fine-tune the results by searching for a file name or a different DPI value.

Named Query example

Figure: A Named Query for locating files with a specific DPI value (default is 72).

It looks as follows in Studio:

The DPI Search query in Studio

The following code is used:

Interface:

name,string,,
dpi,int,72,

Query:

/*SELECT*/ select o.`id` as `ID`, o.`type` as `Type`, o.`name` as `Name`, st.`state` as `State`, ul.`fullname` as `LockedBy`, p.`publication` as `Publication`, s.`section` as `Section`, o.`comment` as `Comment`, o.`routeto` as `RouteTo`, un.`fullname` as `Creator`, o.`format` as `Format`, um.`fullname` as `Modifier`, p.`id` as `PublicationId`, s.`id` as `SectionId`, st.`id` as `StateId`, st.`color` as `StateColor`, l.`lockoffline` as `LockForOffline`, o.Dpi
/*FROM*/
from `smart_objects` o
/*JOINS*/
left join `smart_publications` p on (o.`publication` = p.`id`)
left join `smart_publsections` s on (o.`section` = s.`id`)
left join `smart_states` st on (o.`state` = st.`id`)
left join `smart_objectlocks` l on (o.`id` = l.`object`)
left join `smart_users` ul on (l.`usr` = ul.`user`)
left join `smart_users` um on (o.`modifier` = um.`user`)
left join `smart_users` un on (o.`creator` = un.`user`)
/*WHERE*/
where ( o.name like '%$name%') and (o.dpi = $dpi)

Creating a Named Query

Step 1. Access the Overview of Named Queries page by choosing Named Queries in the Maintenance menu on the left side of the screen or on the Home page.

The Overview of Named Queries page

Step 2. Click New Query.

The Named Queries page appears.

The Named Queries page

Step 3. Fill out all details.

Note: Take note of the following:

The first 4 columns returned should always be id, type, name and format (see examples below). These columns are the bare minimum for client applications to have a basic understanding of what kind of objects are listed. (For example, "Format" is used to display the correct file icon and to open the file in an appropriate editor.)

To support status colors and automatic updating of column fields, your query also needs to return brandid, issueid, sectionid, and stateid. Normally these are returned as the last columns.

Before adding new Named Queries to the system, we recommend to first make sure that the SQL is correct by executing it, for example by using phpMyAdmin.

Newly created Named Queries will only be available to users when they log out and log back in to the system. However, changes in the SQL section of an existing Named Query do NOT require users to log out and in again.

Tip: To hide a Named Query client-side, start its name with a dot (for example .MyQuery).

Step 4. Click Update.

Code for the default queries

Below follows the full code for the default Named Queries 'Libraries' and 'Templates'.

Notes:

The notations shown are those for MySQL.

The 'StateColor' option is included so that all client applications can display the correct Workflow Status color.

Libraries

Full code for the Libraries query, used for locating all InDesign Library files:

/*SELECT*/ 
select o.`id` as `ID`, o.`type` as `Type`, o.`name` as `Name`, o.`format` as `Format`, st.`state` as `State`, ul.`fullname` as `LockedBy`, p.`publication` as `Publication`, s.`section` as `Section`, o.`comment` as `Comment`, o.`routeto` as `RouteTo`, un.`fullname` as `Creator`, o.`format` as `Format`, um.`fullname` as `Modifier`, p.`id` as `PublicationId`, s.`id` as `SectionId`, st.`id` as `StateId`, st.`color` as `StateColor`, l.`lockoffline` as `LockForOffline` 
/*FROM*/ 
from `smart_objects` o 
/*JOINS*/ 
left join `smart_publications` p on (o.`publication` = p.`id`) 
left join `smart_publsections` s on (o.`section` = s.`id`) 
left join `smart_states` st on (o.`state` = st.`id`) 
left join `smart_objectlocks` l on (o.`id` = l.`object`) 
left join `smart_users` ul on (l.`usr` = ul.`user`) 
left join `smart_users` um on (o.`modifier` = um.`user`) 
left join `smart_users` un on (o.`creator` = un.`user`) 
/*WHERE*/ 
where (o.`type` = 'Library')

Templates

Full code for the Templates query, used for locating all template files:

/*SELECT*/ 
select o.`id` as `ID`, o.`type` as `Type`, o.`name` as `Name`, o.`format` as `Format`, st.`state` as `State`, ul.`fullname` as `LockedBy`, p.`publication` as `Publication`, s.`section` as `Section`, o.`comment` as `Comment`, o.`routeto` as `RouteTo`, un.`fullname` as `Creator`, o.`format` as `Format`, um.`fullname` as `Modifier`, p.`id` as `PublicationId`, s.`id` as `SectionId`, st.`id` as `StateId`, st.`color` as `StateColor`, l.`lockoffline` as `LockForOffline` 
/*FROM*/ 
from `smart_objects` o 
/*JOINS*/ 
left join `smart_publications` p on (o.`publication` = p.`id`) 
left join `smart_publsections` s on (o.`section` = s.`id`) 
left join `smart_states` st on (o.`state` = st.`id`) 
left join `smart_objectlocks` l on (o.`id` = l.`object`) 
left join `smart_users` ul on (l.`usr` = ul.`user`) 
left join `smart_users` um on (o.`modifier` = um.`user`) 
left join `smart_users` un on (o.`creator` = un.`user`) 
/*WHERE*/ 
where (o.`type` = 'LayoutTemplate' or o.`type` = 'ArticleTemplate' or o.`type` = 'LayoutModuleTemplate')

Additional examples

Files in a Dossier

To reference files in a Dossier, use where type = 'Contained' in the WHERE statement.