Q: Performing AND logic on related columns in a where clause


App ID: 532CF1B7-693E-48CA-B1B1-F16FCB709609

(tl;dr at the bottom)

We’re building a CRM that allows users to add contacts and store properties on those contacts. Users create the property “types” (i.e. “City”, “Country”, “Favourite colour”, etc) which are then usable on all their contacts. As these properties can be varying data types like numbers or strings, we’ve designed our property tables to use JSON as the datatype for storing the values.

What we’re looking to do now is create a filtering system that users can set up on their list of contacts. In my app I’ve made extra tables to simulate what we’re trying to do:

‘EXAMPLE_Contact’ table:

(One contact has properties { “City”: “Toronto”, “Country”: “Canada” }, the other has { “City”: “Vancouver”, “Country”: “Canada” })

‘EXAMPLE_Properties’ table:

On the contact table, I can have a where clause set up to find contacts that live either in “city: Toronto” or “city: Vancouver”:

(properties.name = 'City' AND properties.value->'$[0]' = 'Toronto') OR (properties.name = 'City' AND properties.value->'$[0]' = 'Vancouver')
		"___class": "EXAMPLE_Contact",
		"ownerId": null,
		"updated": null,
		"created": 1644514424000,
		"objectId": "9D2006B8-E5A0-40B6-864C-2C1C65027ED4"
		"___class": "EXAMPLE_Contact",
		"ownerId": null,
		"updated": null,
		"created": 1644514344000,
		"objectId": "E63C7A7C-13DD-4B1A-BF35-DA571D096A07"

This works as expected, I can filter based on a city or - using OR logic - two cities, but I’m trying to figure out how I should structure the clause to support AND logic. For example:

(properties.name = 'Country' AND properties.value->'$[0]' = 'Canada') AND (properties.name = 'City' AND properties.value->'$[0]' = 'Toronto')



Which I understand doesn’t work as it’s trying to compare two of the same properties with different values which will always be false.

Question (TL;DR)

I’m wondering is there a way to write a where clause to perform AND logic on a 1:N related column, so that if any two related records satisfy the condition then the where clause passes for that record?

If not, I’m open for suggestions or workarounds to achieve this behaviour. I’m not much of a database expert so any advice is very highly appreciated!

Thank you!

I think going with JSON as a solution here would create the problem you’re running into. How about creating a separate table that would contain a combination of property name/value for your contacts?

Also, have you seen the CRM blueprint we published in the marketplace?