SQL Operators for Segment Filter Rules

[andrew_c3]

Hello,

My idea is:

You only have a limited set of textual operators in Mautic segment filter rules which are vague. This limits you to very simple filter rules. You can’t even do a logical AND on a number of tags in a single rule which is frustrating. Mautic’s “Including” rule is a logical OR.

However SQL which is underneath the Symfony and Doctrine platform Mautic is built on you can use standard mathematical operators. Those should be available to Mautic segment filter rules as well so we can create complex rules easily.

The way you could do that is leave the current operators as is for existing segment filters and allow the use of the following additional mathematical operators as well so new filter rules can created to take advantage of the power of the SQL engine underneath.

These are available but not limited to those in SQL

Used to compare two expressions.

Operator	Description
=	Equal to
!= or <>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<=>	NULL-safe equal to

Logical Operators

Used to combine multiple conditions.

Operator	Description
AND	Returns true if both are true
OR	Returns true if either is true
NOT	Negates a condition
XOR	True if only one is true

---

Arithmetic Operators

Used in mathematical expressions.

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
% or MOD	Modulo (remainder)
DIV	Integer division (floor)

---

Bitwise Operators

Work at the bit level.

Operator	Description
&	Bitwise AND
`	`	Bitwise OR
^	Bitwise XOR
~	Bitwise NOT
<<	Left shift
>>	Right shift

---

Assignment Operators

Used to assign values to variables.

Operator	Description
=	Assignment (for SET, etc.)
:=	Assignment in expressions

---

Other Operators / Clauses

Operator / Keyword	Description
IN	Value in a list
NOT IN	Value not in a list
BETWEEN	Within a range
NOT BETWEEN	Not within a range
LIKE	Pattern matching
NOT LIKE	Pattern not matching
IS NULL	Value is NULL
IS NOT NULL	Value is not NULL
REGEXP or RLIKE	Regular expression matching
NOT REGEXP	Not matching a regex
CASE	Conditional logic
CAST()	Type conversion
COALESCE()	First non-NULL value

---

Thanks

[andrew_c3]

Hello,

Follow-up post.

Simple example segment filter rule using operators based on what is available in SQL. This notation is precise and flexible so you can understand what it means even when complex.

Filer Rule
Tag == (Equals) Tag1 AND Tag2 AND Tag3
AND
Tag == (Equal) Tag4 OR Tag5 OR Tag6
AND
etc…

Thanks.

[joeyk]

Hi,
Great idea. We actually use this, and it’s really capable:

Unlimited SQL Plugin for Mautic | Mautic Extendee

Joey

[andrew_c3]

Hello joeyk,

Thanks for the sharing details about the “Unlimited SQL Plugin”. it might be the very thing I’m looking for to write more complex SQL segment queries.

Do you know if there are any plans to add this great feature to the Mautic base code or will it just exist as a paid for plugin ?

The feature is definitely needed in Mautic out of the box.

Thanks.

[andrew_c3]

Hello,

Forgot to ask:

Do you know if there is a central location were all these great Mautic paid for plugins are listed ? Or is it just a case of doing a google search ?

I did not come across it when I recently tried

Thanks.

[joeyk]

There is no central place, we they pop up, disappear, get unsupported, etc.
I’m planning to add them to my website, but I think testing should be also included. Maybe even videos.

Regarding the Unlimited SQL plugin…
This is a ‘very dangerous’ plugin if you don’t know what you are doing. But a real blessing if you know Mautic will enough.

Mautic is for all skill levels, and you can super easily do great damage to your DB if you don’t know how MySQL works. Even if you kinda know - you can create querries, that slow down your instance to unacceptable levels.

For this reason we don’t offer it to users, just for administrators, integrators.

The Unlimited MySQL plugin allows you to do things which otherwise would be hard to achieve, (like we use it for creating social network functionality with password protection, or in an other case affiliate marketing functions). It’s the missing tool for total control of the DB.

In my opinion Mautic should offer more and more segment filters with warnings to the performance if needed. You mentioned many very good examples, I’m totally behind them.

[andrew_c3]

Hello joeyk,

I’m sure the community would welcome you putting a plugin resource page on your website as it would certainly help save time searching the internet. And an independent review of each would be fantastic.

I have spent years writing C and SQL code for Oracle on Unix / Linux commercial projects so I have no issues with the the technology.

However I’m getting up to speed with Symfony, Doctrine and the Mautic database schema etc And of course I continually backup everything.

Yes the standard Mautic segment filter rules badly need updating as I outlined above. I hope the developers review them soon. I think the Unlimited SQL plugin is my solution right here and now as I need to make progress.

As a user of the Unlimited SQL plugin, do you know if it can call stored SQL procedures on Mariadb ? Or do you know if Doctrine can call stored procedures ?

Thanks.