SQL Join in Realm

[Veera Taneeru]

I have 3 tables which I get from JSON response, I want to join these tables and Query the information

1) Locations

2) Facilities

3) Rooms

I used Realm + Json for object mapping and used realm to create tables.

I have one - Many relationship between facilities and rooms

@interface Facilities:RLMObject

@propery RLMArray<Rooms> *rooms;

@end

While mapping json response, how can I map data to  RelationShip object(RLMArray<Rooms> *rooms) using facility ID, Please find below sample response. 

/* Rooms response */

fi_facil_rooms: [

{

room: "001",

area: "",

img_src: "",

type_id: 286,

id: 747,

status: "active",

notes: "Electronics",

facil_id: 53

},

{

room: "001",

area: "",

img_src: "",

type_id: 286,

id: 930,

status: "active",

notes: "Electronics",

facil_id: 4

}]

/* Facility Response */

fi_facilities: [

{

location_id: 15,

name: "Art Annex",

id: 53,

short_name: "Annex",

status: "active"

},

{

location_id: 1,

name: "Baker Laboratory",

id: 4,

short_name: "Baker Lab",

status: "active"

}]

[Marius Rackwitz]

Realm doesn't support so far foreign key resolution for JSON mapping.
You would need to prepare the data yourself before passing it to Realm.
Therefore you'll typically iterate over all your relations one-by-one and resolve all referenced objects by finding the instances and setting the relation explicitly on the new RLMObject subclass instance.

--
Marius Rackwitz
iOS Product Engineer

--
You received this message because you are subscribed to the Google Groups "Realm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to realm-cocoa...@googlegroups.com.
To post to this group, send email to realm...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/realm-cocoa/0b197175-3d8a-4ce4-b719-1fc25bf5a636%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

{#HS:76737548-641#}

[Steve Burris]

This can totally be done with value transformers.  Based on the information provided though I think you would first want to load your Facilities objects and then your Rooms objects.  The room object would use a subclass of NSValueTransformer like what I've shown below to do a lookup of the Facility based on the ID.

And then in the Facility class you can use an inverse relationship like:

- (NSArray *)rooms {

    return [self linkingObjectsOfClass:@"Room" forProperty:@"facility"];

} 

@implementation FacilityIdTransformer

+ (instancetype)valueTransformer {

    return [[self alloc] init];

}

+ (BOOL)allowsReverseTransformation {

    return NO;

}

- (id)transformedValue:(id)value  {

    NSInteger facId = [value integerValue];

    Facility *fac = [Facility objectInRealm:[RLMRealm defaultRealm] forPrimaryKey:[NSNumber numberWithInteger:facId]];

    

    return fac;

}

@end 

[Veera Taneeru]

Thanks for your reply steve, I added a transformer but have a question how to use it, Pls find below info

/////Rooms.h

#import <Realm/Realm.h>

@interface Rooms : RLMObject
@property NSInteger roomId;
@property NSInteger facilityId;
@property float area;
@property NSString *room;
@property NSString *imgSrc;
@property NSString *type;
@property NSInteger typeId;
@property NSString *status;
@property NSString *notes;


@end
RLM_ARRAY_TYPE(Rooms)

////End Rooms

/////Facilities

#import <Realm/Realm.h>
#import "Rooms.h"

@interface Facilities : RLMObject
@property NSInteger locationId;
@property NSString *name;
@property NSInteger facilityId;
@property NSString *shortName;
@property NSString *status;
@property (nonatomic,weak) RLMArray<Rooms> *rooms;
@end

@implementation Facilities : RLMObject
+ (NSDictionary *)JSONInboundMappingDictionary {
    return @{
             @"location_id": @"locationId",
             @"name": @"name",
             @"id": @"facilityId",
             @"short_name": @"shortName",
             @"status":@"status"
             };
}

+ (NSDictionary *)JSONOutboundMappingDictionary {
    return @{
             @"locationId": @"location_id",
             @"name": @"name",
             @"facilityId": @"id",
             @"shortName": @"short_name",
             @"status" : @"status"
             };
}

- (NSArray *)rooms {
    return [self linkingObjectsOfClass:@"Rooms" forProperty:@"facilityId"];
}

+ (NSString *)primaryKey {
    return @"facilityId";
}


@end

/////End Facilities

How to use FacilityIdTransformer mentioned above to load objects in to RLMArray<Rooms> *rooms based on foreign key mapping

[Steve Burris]

You need an JSONInboundMappingDictionary in Rooms.m as well.  The Rooms object will not have a facilityid property.  It will instead be something like:

@property Faciltiies facility;
 

In your inbound mapping you will have:

@"facilitiyId": @"facility"
 

And then in Rooms.m you'll have something like:

+ (NSValueTransformer *)facilityJSONTransformer {
    return [FacilityTransformer valueTransformer];
} 

For instance, in one of my classes I convert a country code in JSON to a Country object.  In my class that has the Country property I have:

+ (NSValueTransformer *)countryJSONTransformer {

    return [CountryCodeTransformer valueTransformer];

} 

And the CountryCodeTransformer is:

@implementation CountryCodeTransformer

+ (instancetype)valueTransformer {

    return [[self alloc] init];

}

+ (BOOL)allowsReverseTransformation {

    return NO;

}

- (id)transformedValue:(id)value  {

    Country *country;

    

    if (value) {

        NSString *countryCode = value;

        country = [Country objectsInRealm:[RLMRealm defaultRealm] where:@"code = %@", countryCode].firstObject;

    }

    

    return country;

}

@end 

On Tuesday, March 10, 2015 at 1:18:39 AM UTC-4, Veera Taneeru wrote:

[Veera Taneeru]

Thanks a lot Steve implemented as per your comments, It is working great.