"~" in query

[ShakyShip]

Hey thanks for this great API, now I'm kinda used to it after 1 month coding
ok back to my question, can where clause use "~" ? in terminal we use it like this

/ip firewall mangle> print where connection-mark~"_2"
Flags: X - disabled, I - invalid, D - dynamic 
 0   chain=prerouting action=mark-packet new-packet-mark=5UP_2 passthrough=no 
     connection-mark=5UC_2 

 1   chain=prerouting action=mark-packet new-packet-mark=5DP_2 passthrough=no 
     connection-mark=5DC_2 

 2   chain=prerouting action=mark-packet new-packet-mark=5UJP_2 passthrough=no 
     connection-mark=5UJC_2 

 3   chain=prerouting action=mark-packet new-packet-mark=5DJP_2 passthrough=no 
     connection-mark=5DJC_2 

but when I try on API it doesn't work like on terminal, here's my code

$nbr = '2'

$req = new RouterOS\Request('/ip/firewall/mangle/print');
$req->setArgument('.proplist', '.id');
$req->setQuery(RouterOS\Query::where('new-connection-mark','_' . $nbr));
$ncm = $client->sendSync($req)->getProperty('.id');// Get new-connection-mark user id

$removeRequest = new RouterOS\Request('/ip/firewall/mangle/remove');
$removeRequest->setArgument('numbers', $ncm);
$client->sendSync($removeRequest);// Delete by new-connection-mark user

Or maybe something wrong with my code ? I don't know
But if it can use "~" like in terminal, it can be very helpful
Thank you.

[boenrobot]

Yes, it would be very helpful indeed, but sadly, you can't. The API protocol doesn't allow it.

See this topic in the MikroTik forums.

If it was allowed by the protocol, then you would've been able to do it by adding the "~" operator as a 3rd argument on Query::where(), i.e. RouterOS\Query::where('new-connection-mark','_' . $nbr, '~').

In the meantime, the only way you can filter items by a regex is to get all items (or at least all items matching other criteria), and then filter them in PHP by looping over them, and calling preg_match() on each item.

If the purpose of filtering is to then apply another command (e.g. remove), then this is most easily done by passing a callback to the appropriate Util method (in this case Util::remove()), e.g.:

$util = new RouterOS\Util($client);
$util->setMenu('/ip firewall mangle')->remove(
    function (RouterOS\Response $item) use ($nbr) {
        return preg_match(
            '#_' . preg_quote($nbr, '#') . '#',
            $item->getProperty('connection-mark')
        );
    }
);

[ShakyShip]

My apologize, I accidentally unsubscribe this issue and didn't see your reply
then I'm working this code

$util->setMenu('/ip firewall mangle');
$util->remove(RouterOS\Query::where('new-connection-mark', $bwt . "UC_" . $nbr));
$util->remove(RouterOS\Query::where('connection-mark', $bwt . "UC_" . $nbr));
$util->remove(RouterOS\Query::where('new-connection-mark', $bwt . "DC_" . $nbr));
$util->remove(RouterOS\Query::where('connection-mark', $bwt . "DC_" . $nbr));
$util->remove(RouterOS\Query::where('new-connection-mark', $bwt . "UJC_" . $nbr));
$util->remove(RouterOS\Query::where('connection-mark', $bwt . "UJC_" . $nbr));
$util->remove(RouterOS\Query::where('new-connection-mark', $bwt . "DJC_" . $nbr));
$util->remove(RouterOS\Query::where('connection-mark', $bwt . "DJC_" . $nbr));

it works, but it seems not effective like your code. because the first idea is to make a simple code to running. Well I'm not a programmer though so I didn't know how to use those function
Thanks for your help, now I can simplify program with your code.

and one more question, can I use like this ?

$util->setMenu('/ip firewall mangle')->remove(0)->remove(1)->remove(2)

[boenrobot]

and one more question, can I use like this ?

$util->setMenu('/ip firewall mangle')->remove(0)->remove(1)->remove(2)

No, but you can use

$util->setMenu('/ip firewall mangle')->remove(0, 1, 2);

The results of remove() and most other Util methods are a ResponseCollection that allows you to check for errors if you want to OR if MikroTik decide to return some useful info in later versions, you could extract that too.

In your case, you can chain all matches in a single query:

$util->setMenu('/ip firewall mangle')->remove(
    RouterOS\Query::where('new-connection-mark', $bwt . "UC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "UC_" . $nbr)
        ->orWhere('new-connection-mark', $bwt . "DC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "DC_" . $nbr)
        ->orWhere('new-connection-mark', $bwt . "UJC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "UJC_" . $nbr)
        ->orWhere('new-connection-mark', $bwt . "DJC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "DJC_" . $nbr)
);

This should be more efficient than your code above, because it's a single remove() call and one implicit find() call. Actual calls to RouterOS are the most performance costly part of working with the RouterOS API. I haven't done detailed benchmarks, but 2 calls are definitely going to be more efficient than 16.

The above is equivalent to the terminal:

/ip firewall mangle remove \
    [find where new-connection-mark=($bwt . "UC_" . $nbr) \
        or connection-mark=($bwt . "UC_" . $nbr) \
        or new-connection-mark=($bwt . "DC_" . $nbr) \
        or connection-mark=($bwt . "DC_" . $nbr) \
        or new-connection-mark=($bwt . "UJC_" . $nbr) \
        or connection-mark=($bwt . "UJC_" . $nbr) \
        or new-connection-mark=($bwt . "DJC_" . $nbr) \
        or connection-mark=($bwt . "DJC_" . $nbr) \
    ]

[ShakyShip]

Thanks for your reply, I've been tried this code with loop that you give me previously

$util->setMenu('/ip firewall mangle')->remove(
                function (RouterOS\Response $item) use ($nbr) {
                        return preg_match(
                        '#_' . preg_quote($nbr) . '#',
                 $item->getProperty('new-connection-mark')
                        );
                }
        );
$util->setMenu('/ip firewall mangle')->remove(
                function (RouterOS\Response $item) use ($nbr) {
                        return preg_match(
                        '#_' . preg_quote($nbr) . '#',
                 $item->getProperty('connection-mark')
                        );
                }
        );

The output is same with that single query code but, I don't need to write another specific variable like $bwt and "UC_" or something. Just using the $nbr on connection-mark and new-connection-mark can be removed. Well I don't know exactly which is faster (Single-query or loop) but surely faster than 16 call.

I think my problem was solved from the first reply, but you give me an amazing explanation and some code advice. I really appreciate that, Thank you very much.

[boenrobot]

You can make it more efficient by using a single callback instead of two:

            $util->setMenu('/ip firewall mangle')->remove(
                function (RouterOS\Response $item) use ($nbr) {
                    $pattern = '#_' . preg_quote($nbr) . '#';
                    return preg_match(
                        $pattern,
                        $item->getProperty('new-connection-mark')
                    ) || preg_match(
                        $pattern,
                        $item->getProperty('connection-mark')
                    );
                }
            );

Otherwise, you have not one, but two loops over all mangle rules (well, the second without the new-connection-mark matches, since they were previously removed, but still... most of your mangle rules).

As for whether a callback or a query would be more efficient... I think it's a slope... For a query with few criteria, a query is definitely more efficient. For a query with tons of criteria, a single loop over the whole thing with a callback MAY be more efficient (again, I haven't done benchmarks). I don't know about your criteria set though. I'll be curious to see any measurements you make.

[ShakyShip]

tried to benchmarks using microtime with 2 entry
5DP_7 and 10DP_6

while (....){
$util->setMenu('/ip firewall mangle')->remove(
                function (RouterOS\Response $item) use ($nbr) {
                    $pattern = '#_' . preg_quote($nbr) . '#';
                    return preg_match(
                        $pattern,
                        $item->getProperty('new-connection-mark')
                    ) || preg_match(
                        $pattern,
                        $item->getProperty('connection-mark')
                    );
                }
            );
}
1.434210062027

while (.....) {
$util->setMenu('/ip firewall mangle')->remove(
         RouterOS\Query::where('new-connection-mark', $bwt . "UC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "UC_" . $nbr)
        ->orWhere('new-connection-mark', $bwt . "DC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "DC_" . $nbr)
        ->orWhere('new-connection-mark', $bwt . "UJC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "UJC_" . $nbr)
        ->orWhere('new-connection-mark', $bwt . "DJC_" . $nbr)
        ->orWhere('connection-mark', $bwt . "DJC_" . $nbr)
); };
1.4148988723755

a little different, in millisecond maybe.
if there is 100 entry in process it might have a significant difference.