Hello,
As we now have a real forum, it would be fantastic to get the seemingly rouge list of query operators listed so we have a resource for this.
Here’s one to start it off. I’m looking for the IS NOT EQUAL TO… This really needs published for all to reference.
~<>~ = IS NOT NULL
Hi @Jerel_lorenzo,
While I know that ~<>~ works for not null it is more correctly stated to be not equal to just as it would be in any SQL or for that matter any programming language. Very much the same as != would in most languages.
@Jerel_lorenzo
Good thoughts, Just before the community was released this information was put into the xml documentation found here: https://developer.infusionsoft.com/docs/xml-rpc/#data-query-a-data-table
And to ensure that we have it multiple places here it is:
So how do I use a “AND” or “OR” operator in the queryData in the struct.
Example. I tried using under “Contact” State field and add 2 State field queries.
1st one equal to NY and second to WI. Seem that only the later gets queries.
Only received data from WI.
I did test NY separate and there are NY entries.
I thought of trying the in clause, but the notes says it only works on Id fields.
There is no AND or OR operators. You have to use multiple calls and then marry your arrays. You thought you’d get away easy huh?
Haha, thx Jerel. Yeah easy would have been the better option.
Getting the data from source with out marrying data would be better to keep the data quality.
No there is possibility to make mistake and not notice data quality is being reduced. Duplication, incorrect row sequence pulls etc.
But at least you gave me an answer and what to work with. So thanks.
We don’t really have an OR operator. An AND statement can be made by adding additional criteria to the query though. An additional member would need to be added to the queryData struct for an AND statement like this: https://www.screencast.com/t/RhBjosZE
In this example it would find everyone with the Billing State of NY AND Shipping State of WY.
As @Jerel_lorenzo said though Two separate API calls would be used to pull everyone that had the State of NY OR WY.
This is interesting now because the natural programmer’s logic is kicking in and I just have to ask. Is there a way to implement a NOT operator (thereby simulating the OR condition)?
I know, I know. These dang programmers; can’t live with em… lol
@John_Borelli, there is the “~<>~” query, eg: array(‘Id’ => ‘~<>~1’), which will return everything apart from ID 1.
Right but I’m thinking more along the lines that if there is no OR then would there be the equivalent to "NOT (a) AND NOT (b) (not likely i know but you know how programming logic can go lol)
Hi Guys, is it just with me or the IN statement is not working. I’m using:
Id => [39136,38977] returns an empty object
Id => 39136 returns 1 contact
Id => 38977 returns 1 contact
I’m using PHP with the Novak SDK, here’s the piece of code:
$results = Infusionsoft_DataService::query( new Infusionsoft_Contact(), array(‘Id’ => “[”.implode(",",$infusionsoft_ids)."]" ) );
Where $infusionsoft_ids = array(39136,38977);
Try wrapping your id’s in quotes. implode is a string function. Come to think of it, you’re passing an array to implode as well. Build a comma separated string to create $infusionsoft_ids with and then use implode on it if you need to do it that way.
I did it, no luck, sadly.
I’ve tried to use just a string
$results = Infusionsoft_DataService::query( new Infusionsoft_Contact(), array(‘Id’ => “[39136,38977]” ) );
Or to add ’ between Ids,
$results = Infusionsoft_DataService::query( new Infusionsoft_Contact(), array(‘Id’ => “[’”.implode("’,’",$infusionsoft_ids)."’]" ) );
Still nothing.
John, have you used the IN statement before, does it work for you?
Sorry about confusion. I just realized my mind saw explode when you had typed implode. So you build a comma separated string. Are you attaching the operator (IN)?
Well, on the doc it doesn’t display the IN inside the query but I tried it anyway:
$results = Infusionsoft_DataService::query( new Infusionsoft_Contact(), array(‘Id’ => “IN [”.implode(",",$infusionsoft_ids)."]" ) );
also
$results = Infusionsoft_DataService::query( new Infusionsoft_Contact(), array(‘Id’ => “IN (”.implode(",",$infusionsoft_ids).")" ) );
didn’t work.
Sorry, hopping between programs here so no, IN not important in name lol One moment and I’ll check some code.
Ok, so here is an example of how it works in the legacy api (the parameters are the same):
$data=$app->dsQuery(‘Contact’,1000,0,array(‘Id’=>[41449,13793]),array(‘Id’,‘FirstName’,‘LastName’));
What I did find though is that if my return fields did not exist then I would get an empty array. So in other words, if the contacts you are looking at have an empty first and last name and that’s what you’ve asked for return values then the array will be sent back as an empty array (or at least those empty contacts will be missing)
Hum, interesting, but for me it doesn’t work at all, hehe.
This is the SDK function for that:
public static function query($object, $queryData, $limit = 1000, $page = 0, $returnFields = false, Infusionsoft_App $app = null)
{
$app = parent::getObjectOrDefaultAppIfNull($app, $object);
if(!$returnFields){
$returnFields = $object->getFields();
}
$params = array(
$object->getTable(),
(int) $limit,
(int) $page,
$queryData,
$returnFields
);
$records = $app->send('DataService.query', $params, true);
return self::_returnResults(get_class($object), $app->getHostName(), $records, $returnFields);
}
You will notice that if there’s no $returnFields it will send all the fields to be returned.
Then I tried:
$results = Infusionsoft_DataService::query( new Infusionsoft_Contact(), array(‘Id’ => “[39136,38977]” ), 1000,0, array(‘Id’,‘FirstName’,‘LastName’) );
No luck. =(
But well, if it works for you, I will keep trying here. Thanks John.