Connectors
LoyJoy provides the option to establish a connection with your external data source (such as Google Sheets) through connectors, allowing you to use this data within LoyJoy.
LoyJoy currently offers two types of connectors.
For assistance with the configuration, please reach out to our support team.
SQL Filter
For connectors it is possible to define customizable filters using Structured Query Language (SQL). This allows you to filter the data from the external source before using it in LoyJoy. Take a look at the following SQL query.
SELECT * FROM products WHERE title = 'Product 1';
In this example, the SQL query filters the data from the external source to only include products with the title "Product 1". You can use this feature to filter the data according to your requirements.
Supported Operators
Equals
SELECT * FROM products WHERE title = 'Product 1';
Not Equals
SELECT * FROM products WHERE title != 'Product 1';
Greater Than
SELECT * FROM products WHERE price_euro_cent > 10099;
Greater Than or Equals
SELECT * FROM products WHERE price_euro_cent >= 10099;
Less Than
SELECT * FROM products WHERE price_euro_cent < 10099;
Less Than or Equals
SELECT * FROM products WHERE price_euro_cent <= 10099;
Like
SELECT * FROM products WHERE title LIKE 'Product%';
Not Like
SELECT * FROM products WHERE title NOT LIKE 'Product%';
In
SELECT * FROM products WHERE title IN ('Product 1', 'Product 2');
Not In
SELECT * FROM products WHERE title NOT IN ('Product 1', 'Product 2');
And
SELECT * FROM products WHERE title = 'Product 1' AND price_euro_cent > 10099;
Or
SELECT * FROM products WHERE title = 'Product 1' OR price_euro_cent > 10099;
Boolean True
SELECT * FROM products WHERE is_active IS TRUE;
Boolean False
SELECT * FROM products WHERE is_active IS FALSE;
Boolean Not True
SELECT * FROM products WHERE is_active IS NOT TRUE;
Boolean Not False
SELECT * FROM products WHERE is_active IS NOT FALSE;
Is Null
SELECT * FROM products WHERE title IS NULL;
Is Not Null
SELECT * FROM products WHERE title IS NOT NULL;
Limit
SELECT * FROM products LIMIT 5;
Cast to Integer
SELECT * FROM products WHERE CAST(price_euro_cent AS INTEGER) > 100;
Cast to BigInteger
SELECT * FROM products WHERE CAST(price_euro_cent AS BIGINT) > 100.99;
Cast to Decimal
SELECT * FROM products WHERE CAST(price_euro_cent AS DECIMAL) > 100.99;
Cast to double
SELECT * FROM products WHERE CAST(price_euro_cent AS DOUBLE) > 100.99;
Cast to char
SELECT * FROM products WHERE CAST(price_euro_cent AS CHAR) = '100.99';
Union
SELECT * FROM products WHERE title = 'Product 1' UNION SELECT * FROM products WHERE title = 'Product 2';
Union All
SELECT * FROM products WHERE title = 'Product 1' UNION ALL SELECT * FROM products WHERE title = 'Product 2';
Distinct on
SELECT DISTINCT ON (group) * FROM products;
Order by
SELECT * FROM products ORDER BY price_euro_cent DESC;
Projection
You may also use projection to select only the columns you need. This can be done as follows:
SELECT title, price FROM products;