Is there a way to use SQL distinct in a query?

General Q&A
1

I was just wondering whether this feature is perhaps available.

2

Hi @chansmann

Do you mean you want to query the JourneyApps DB in your App using something like SQL distinct?

3

Yes

For example,
an animal can be spotty or striped (characteristic field)
and an owner can own more than one animal

Then if I want to query for all owners with striped animals
I may get duplicate owners if I use .include on the characteristic field
and then I want to reduce the owners to a distinct set so that I can display the owners on some ui component

Does that make sense?

4

Hi @chansmann

That makes sense, thank you for clarifying.

We do not have a direct way of querying parents based on children fields or children based on parent fields.

Based on your example:
You will have to go through a two step process to get to the unique list of owners:

  1. Query the striped animals and include the owner in the query:
var stripedAnimals = DB.animal.where('striped = ?', true).include('owner').toArray();
  1. You can then use something like a for loop or the array.filter() method to generate a different array containing the unique owners. I have used a for loop in the example code below:
var uniqueOwners = [];
for(var i = 0; i < stripedAnimals.length; i++) {
    // Check if the owner with the same id already exists in the uniqueOwners array
    var index = uniqueOwners.findIndex(function(owner) {
        return owner.id === stripedAnimals[i].owner_id;
    });

    // Add the owner to the array if it has not been added yet
    if(index === -1) {
        var uniqueOwner = stripedAnimals[i].owner();
        uniqueOwners.push(uniqueOwner);
    }
}
5

Thanks Nidene!