Aggregating two queries into one on local RealmDB using swiftSDK

I am working on local RealmDB. In my application there are two collection

Drug

id drugName

1 abc

2 xyz

3 pqr

Pill

id pillName drugID

1 qqq 1

2 www 2

3 eee 1

4 ttt 2

5 rrr 2

6 fff 3

To get pills for any specific drug with some name… I need to write below code

let drugObject = realm.objects( Drug . self ).where{ $0.drugName == xyz }.first

let pills = realm.objects( Pill . self ).where{ $0. drugID == drugObject.id }

Instead of writing two queries here is there any way to combine into single query like join operation and get the result here?

Welcome to the forums!

Yes, there is a way (and maybe lots of ways) to do that but some clarity is needed in the question.

Is the goal to retrieve all of the pills that contain a specific drug? Also, drug names change so using that as a key to look things up may not be the best idea - I can suggest some better options on that once we have clarity on the goal in the question.

In the future, it’s a good idea to include your actual Realm objects in the question along with sample data to clarify what’s being asked. (and format your code!)

1 Like

Yes the goal is to retrieve the pills of specific drugID.

Here I am not using drugName as a key. First the user will perform search operation on Drug collection based on drugName and then tries to fetch the pill details for that drug.

It appears that Pills can contain several drugs and a Drug can appear in a variety of pills. In that case we have a two-way relationship between Pills and Drugs.

One possible setup would have two classes

class PillClass: Object {
   @Persisted var drug_list = List<DrugClass>()
}

class DrugClass: Object {
   @Persisted var drug _id = ""
   @Persisted(originProperty: "drug_list") var linkedPills: LinkingObjects<PillClass>
}

The PillClass has list of Drugs that are in that pill and the DrugClass appears in different Pills

To then get the pills that contain a specific drugID the query would be

let results = realm.objects(PillClass.self ).where { $0.drug_list.drug_id == "some drug id" }

You could also then traverse the graph from Drugs back to pills to for example, detect drug interactions with certain pills.

Is there any way to write queries without LinkingObjects… Pill to drug is always one to one relationship not list.

It Pill to Drug is always 1-1 then you should consider should use embedded objects as that simplifies the structures and queries.

However that would mean that 1 pill only ever has one drug and 1 drug is only ever contained in one pill. Is that correct?

In this use case 1 drug can contain multiple pills. Yes embedding pill objects with drug is good in this situation.

From learning perspective as mentioned in question, Instead of writing two queries is there any way to combine into single query like join operation and get the result without linking and embedding objects?

Sure. Assuming there’s a Drug object with a List if EmbeddedObjects of Pills, you can filter for drug.pill == ‘some pill’ to return Drugs that contain that pill.

Thank you.

I wanted to understand how we can write join query to get pill objects instead of the one I mentioned above … when drug and pill are two independent collections

Is there any solution other than below one?

let drugObject = realm.objects( Drug . self ).where{ $0.drugName == xyz }.first

let pills = realm.objects( Pill . self ).where{ $0. drugID == drugObject.id }

Ok, first thing is that MongoDB Is a NoSQL Database - so there are not really any ‘join’ queries like what you would find in SQL. Realm puts a nice ‘face’ on that NoSQL data so we can run more human-readable queries and joins are done through relationships (forward through the object graph with Lists, and backward with LinkingObjects).

Second thing is (when) you’re using embedded objects, only one object will be managed; the parent object that contains the embedded object. Embedded objects never stand alone.

The last thing is that it’s not really clear what you’re trying to query for and why don’t want to use the solution presented above.

It seems like you want to retrieve pills that contain a specific drug and that’s exact what my example above does, all in one query.

let results = realm.objects(PillClass.self ).where { $0.drug_list.drug_id == "some drug id" }

Is there a reason you don’t want to use that?

Yep will go with same solution as you suggested for Pill and Drugs collection. But I just wanted to understand is there any ways to go with single query with two independent collection to get records instead of two separate queries.

Thanks for your support.

My pleasure.

To answer your question, yes! and the above solution does that - it’s a single query with two independent collections.

Joining is (can be) done through a common column in SQL and joining is done through references in Realm.

Keep in mind that in your original question, there’s really only one query - the first statement retrieves the objectId your after and the second line is the actual query.

That would be akin to not knowing the column name you want to join by in SQL; If you know the column name though - you can SQL query join in one line.

Likewise, if you know the ObjectID in Realm, you can get the results with a single query.