CCv6 - Where Condition Not Equal To

Just a quick question that I am hoping is easy to answer.

I am aware that in ChronoConnectivity v6 you can easily add a WHERE condition to a Read Data function using the following format:

model.field:somevalue

This will then filter the results so that only those records are displayed where the model.field = somevalue.

Does anyone know what the format of a condition would be when you want to have model.field != somevalue?

I know this should be simple, but I'm struggling to identify it.

Thanks in advance for any assitance, Paddy

Sorry to re-open this topic, but I've stumbled across something that I'm struggling to resolve in relation to the condition not equal to.

I identified the solution in the manual that you can use the format:

modelname.id/<>:10

and this would filter all the records except where the id of the record equals 10. Great news!

What I now want to do, is filter the results so that one field is different to another field. e.g.

modelname.origvalue/<>:modelname.newvalue

ie only display the records where the origvalue is different to the newvalue

Again, this was easy to do in CCv5 but the code above doesn't work in CCv6. The debugger displays the query conditions as:

WHERE `modelname`.`origvalue` <> 'modelname.newvalue'

instead of

WHERE `modelname`.`origvalue` <> `modelname`.`newvalue`

and therefore it doesn't work!

Any idea how to get this simple condition to work as expected?

Thanks in advance as always, Paddy

Hi, Paddy, It is easier to use built-in Where Condition form and use built in Condition != in CF6 or CCv6,
where you would put the "origvalue" in the Table field name field, Condition would be !=, and Value would be "newvalue".
If "newvalue" is data field, then you would use {data:newvalue}, if it is a text, just write that text (or number).
In addition, I would not put modelname in any field in this case.
Sincerely, Elita

Hi Elita,

Thanks for your response. I had tried this too to see if it would work and unfortunately it doesn't. The issue again is how the criteria for the where cause is enclosed in single quotes. e.g:

WHERE `modelname`.`origvalue` != 'newvalue'

Thanks for the suggestion though.

Any other thoughts, greatly appreciated from everyone. Thanks Paddy

khm.. this is working for me. just checked. and do not understand what is wrong with the syntax WHERE `modelname`.`origvalue` != 'newvalue' The same is in my debug, but it is working.

Hi Elita,

It looks as though whenever the query is generated, the Value part of the where condition is always enclosed in single quotes!

The SQL query will then always treat whatever is in there as a string. This wasn't the logic in CCv5, but looks to be in CCv6.

I'm just hoping there is some way to overcome this!

Thanks again for your assistance Elita, Paddy.

Paddy, you should look for different problem here. The syntax you are worried about was always like this.
Perhaps the problem lies on the SQL version or PHP you are using. So far PHP 8.1. is doing good. 8.2. not (if possible, do not switcjh to 8.2. - even Joomla 4 has problems with it) as of SQL I am using MariaDB v. 10.3
you could switch on maximum error reporting for joomla of php to see it that is a problem.

And do not understand why there is a problem that query is a string. you can always format that field as you like. However for me it works either for numbers or text values or {data:} values. Only thing that is not working is date. that should be converted as date.

Hi Elita, Thanks again for the reply - really appreciated.

I'm able to test the old syntax on the old CCv5 using the same (newer) versions of PHP and MySQL and it works perfectly.

I can also debug the old CC5 connection and see the where statement as follows:

WHERE modelname.origvalue != modelname.newvalue

As you can see, this will run an SQL query that will only return the results where the two fields are not the same - perfect!

The issue is with CCv6 where for some reason it looks as though the prgramming now encloses the WHERE conditions in quotes as below:

WHERE `modelname`.`origvalue` <> 'modelname.newvalue'

Due to this change in logic, it will only return results where the field origvalue does not equal the text modelname.newvalue. This field is an INT field and therefore ALL results are returned.

Just writing this down is helpful and I'm wondering if I need to change the core code written by Max so that the quotes are not added to the where conditions. This would fix the issue I'm sure.

Thanks again for your responses though. I haven't even started listing the numerous issues I'm finding with Chronoforms 8! :-) Paddy

All of that makes sense. What does not is - why all this is working for me but not for you.
If you find a solutin, please, post it here. Just to prepare if stops working.

About CF8 - jupp, unfortunately we all know that Max is doing tremendous job but he is only a human. Unfortunately the same old - I was as eager to switch to CF7 as you for CF8. now I am going to wait a year or two - hope Max will manage to get it as powerful as CF6 and CCV6. It did not happen with CF7 unfortunately. I started with CF5 and can declare that Cf6 bundle is the best. Not only here but also in the market.