Query Builder SQL Query Type

• From Table. In this drop-down menu, you will see all the SQL tables that are available to you. Choose the one from which you would like to take the data;
• Use Join, Where, Order & Order By. These three sections need more attention, so you will find a detailed overview later on;
• Show/Per Page Limit. Here you can define how many items will be shown in the Listing Grid at the same time. If you add the JetSmartFilters Pagination, this feature will define the number of items on one page;

• Total Query Limit. Insert the maximum number of items that can be taken to the query. This is also a number that will be shown if you use the Query Count dynamic tag. If you plan to add a Pagination widget or block to the Listing Grid, this feature will define the maximum number of items in the query;
• Offset. Type the number of first items you would like to skip from start;
• Include columns. By default, items from all columns of the database table are added to the query. If you want to take values only from some certain columns of the table, list them here;
• Columns for filters. In this section, you will see the names of all columns that will be added to the list. If you are going to filter the resulting Listing Grid with JetSmartFilters widgets or blocks, you will need those values to put them into the Query Variable field. In that case, the filter will be sorting items according to the values in a certain column.

Use Join

After you enable that toggle, a Join Tables section appears. It allows you to create a query that takes values from several different database tables. Every time you press the “Add New” button, a new table is added.

• Join Type. In this drop-down list, you can choose how the tables will be combined. There are four main types: Inner, Left, Right, and Outer Join. Read more about each of them in this article;
• Join Table. Select the table you would like to add;
• When current table column. If you don’t want to compare two whole tables, you can choose certain columns of the table you are adding here. The values from the initial table will be compared to the values from the columns you choose;
• Is equal to base table column. You can choose certain columns of the initial table here. The values from the table you are adding will be compared to the values from the initial table’s columns you choose.

Where (query clauses)

This tab allows you to add items to the list according to the values of some meta fields. You set a value in the Value text area, and then the data from the database table column that you choose in the Column bar is compared to that value. If they match, the item is added to the list. You can add as many clauses as you want by clicking on the “Add New” button.

• Column. Here you can choose one of the database table columns. The items will be queried according to the values in that column;
• Value. The value you set here will be compared to the value of the meta field you have chosen in the Field key/name bar;
• Compare. Here you can choose how the values will be compared:
  • Equal – displays items having an equal value;
  • Not Equal – shows items that don’t have an equal value;
  • Greater than – shows items that have a greater value. It works with numeric values only;
  • Greater or equal – displays items that have a greater or equal value. It also works only for numeric values;
  • Less than – shows items that have a lesser value. For numeric values only;
  • Less or equal – displays items that have a lesser or equal numeric value;
  • Like – shows items that have the value you’ve set in the Value field mentioned in the content;
  • Not like – displays only the items that don’t have the value you’ve set in the Value field mentioned in the content;
  • In the list – after you add several values to the Value field, it shows the items that have at least one of them in their content;
  • Not in the list – displays items that have any values except the ones you typed into the Value field;
  • Between – displays items that contain the value between two, which are set in the Value field; 
  • Not between – shows all the items except the ones that contain a value that matches the set interval;
  • Exist – shows all the items that contain the Key. You don’t need to set the Value in this case;
  • Not exist – displays all the items that don’t contain the Key. You don’t need to set the Value either;
• Type. This drop-down allows you to specify what type of data is stored in the chosen column:
  • Char – alphabetic data that is not sensitive to case;
  • Numeric – whole numbers only;
  • Date – date values;
  • Datetime – date and time;
  • Decimal – fraction number data;
  • Time – time values;
  • Binary – data that is sensitive to case;
  • Signed – whole numbers, both positive and negative;
  • Unsigned – whole numbers, only positive.

Order & Order By

Here you can add sorting parameters. There could be several of them for every query. To add a new sorting condition, click on the “Add new sorting parameter” button. 

Every sorting parameter item has additional features:

• Order By. In this drop-down menu, you can choose the parameter by which the items will be sorted;
• Order. Here you can choose ascending (from lowest to highest) or descending (from highest to lowest) order;
• Type. Specify the type of data, stored in the column. All the options here are the same as in the Where section.