JOIN on multiple columns

[cf]

Apologies is this has already been mentioned, search on wordpress forums isn’t great.

TF supports joining only on a single field without filtering joined results. I have use cases involving join using multiple columns and other cases filtering the joined results. I found a workaround I’d like to share.

I am using TF to track my weight lifting. My setup has tables for the workouts, exercises, muscles and a join table that expresses the relationship between exercises and muscles called exercise_muscle. When I view an exercise, the exercise_muscle table is joined by its exerciseId. This tells me all the muscles involved in that exercise in addition to their role (target, synergist, stabilizer etc…). However, when I am viewing a record in the muscles table I would like to see all the exercises that target that muscle, that is, exercises who’s muscleId match exercise_muscle‘s muscleId AND have a role value of target. If I join by the muscleId column only, I would end up with a long list of exercises that have something to do with that muscle but don’t necessarily target it.

The solution: The link table exercise_muscle has a calculated field that concatenates its muscleId and role fields. In the muscle table there is a similar field that concatenates muscleId and the string target. This muscleId,role field in both tables allows one to join them in a way that resembles a SQL join with a where clause: JOIN exercise_muscle AS em ON m.muscleId = em.muscleId WHERE role = 'target'. Similarly, if you concatenate field values in both tables instead of just a string then you get a multi-column join instead of a WHERE.

Of course you can concatenate many values into this field to simulate a WHERE with multiple AND operators (ie. WHERE a = b AND b = c AND...).

My question is, can anyone think of a way to simulate a JOIN with a WHERE clause whose conditions are OR instead of AND?

[Brendan]

Hi Cristian,

When I first saw the title of your post, I was going to suggest a Calculation field. But you’ve obviously already discovered that.

Being that it’s an exact match between the parent and child forms, there’s no way to do an OR match that I can think of.

Thanks,

Brendan

[T.L. Ford]

It can be done like this (for smaller datasets):
Copy “target” records into their own table and link to that.
A simple “rebuild records” script can keep the copied-form data in sync with the real form data.
Hide things you don’t want to see.

See attached Tap Forms database. The copy script (at the end of this post) uses a search to limit exercise_muscles to target.

Random trivia: NoSQL tables don’t need IDs (if you are using traditional numbers for ids (tough to accept going from SQL-brain to NoSQL-brain). Links between objects are maintained for you.

Question for experienced Tap Form scripters:
What’s the correct way to copy links? This works, but feels cumbersome and incorrect.

// only going to be one match, if any
var possible_records = records[index].getLinkedRecordsForField(em_exercise_id);
if (possible_records) {
	var possible_record = records[index].getFieldValue(em_exercise_id);
	if (possible_record) {
		var possible_rec_id = possible_record.getId();
		var possible_record = frm_exercise.getRecordWithId(possible_rec_id);
		rec.addRecordToField(possible_record, exercise_id);
	}
}

Notes for Brendan,
TFFormEntry object needs documentation.
Typo at:
record.recordExistsInField(field_id’); // example is missing the record object parameter, and ‘
record.getNoteFieldValue(field_id’); // I think I reported this one already.

Chance of support for directly copying linked records? i.e.
rec.setFieldValue(muscles_id, records[index].getLinkedRecordsForField(em_muscles_id));

——-
Copy Code:

function rebuild_data() {

	// delete old records
	var records = form.getRecords();
	for (var index = 0, count = records.length; index < count; index++){
		form.deleteRecord(records[index]);
	}
	document.saveAllChanges();

	// this form's ids
	var role_id = 'fld-e9c606f133124d6891c9a04645221833';
	var exercise_id = 'fld-22527485052d4c0f98aa91bff884f75a';
	var muscles_id = 'fld-5d856f1c8bfb4010843755a98f556f5a';

	// exercise_muscle's ids
	var em_role_id = 'fld-c2d53eebfed9446dbc57a11919c8b85d';
	var em_exercise_id = 'fld-99daf5cc157f4f5d9b51ed3496f43e28';
	var em_muscles_id = 'fld-a982502aca7649dcb1c9e47f812762d7';

	var frm = document.getFormNamed('exercise_muscle');
	var src = frm.getSearchNamed('target');
	records = src.getRecords();

	var frm_exercise = document.getFormNamed('exercise');
	var frm_muscles = document.getFormNamed('muscles');

	for (index = 0, count = records.length; index < count; index++){
		var rec = form.addNewRecord();
		rec.setFieldValue(role_id, records[index].getFieldValue(em_role_id));

		// only going to be one match, if any
		var possible_records = records[index].getLinkedRecordsForField(em_exercise_id);
		if (possible_records) {
			var possible_record = records[index].getFieldValue(em_exercise_id);
			if (possible_record) {
				var possible_rec_id = possible_record.getId();
				var possible_record = frm_exercise.getRecordWithId(possible_rec_id);
				rec.addRecordToField(possible_record, exercise_id);
			}
		}

		var possible_records = records[index].getLinkedRecordsForField(em_muscles_id);
		if (possible_records) {
			var possible_record = records[index].getFieldValue(em_muscles_id);
			if (possible_record) {
				var possible_rec_id = possible_record.getId();
				var possible_record = frm_muscles.getRecordWithId(possible_rec_id);
				rec.addRecordToField(possible_record, muscles_id);
			}
		}

	}
	document.saveAllChanges();

}

rebuild_data();

[T.L. Ford]

Not sure where my attachment went (pebkac error, I’m sure).

[Sam Moffatt]

It should have warned you that it didn’t like the attachment extension or it might have been too large (though I thought it was reasonably generous for size). There are a few ways things can disappear, try attaching to a new post and see if you get any error messages when attaching/posting. There is also sometimes a message that shows up under your post about why the attachment was dropped as well.

[T.L. Ford]

Should have, but didn’t, and I tried on the second post, too – I’ve had issues before. I expect it’s (contentBlocker, popupBlocker, oldBrowser)[Math.random * 3] + needingToGetBackToProjects; It’ll be an environment issue for me, not a problem with this site.

The file size is tiny (<700k).
http://www.cattail.nu/tap_forms/Muscle.tapforms.zip

– T

[cf]

Random trivia: NoSQL tables don’t need IDs (if you are using traditional numbers for ids (tough to accept going from SQL-brain to NoSQL-brain). Links between objects are maintained for you.

While true there are some reasons to maintain IDs for joins.

1. In this case data was exported from an existing SQL database so it’s easier to just re-use the IDs rather than build out all the links.
2. Tapforms behavior when maintaining links is a bit different than join table by ID. For example, a self join for a parent-child relation using the tf one-to-many option will actually show both the parent and child in each other’s linked field.
3. Tap forms treats joined fields differently from managed linked fields in how they are rendered in iOS. Join fields will show the contents of the linked table in the parent form whereas the two managed options will just show a single row that opens the child data in a separate pushed view controller. For something like recipe ingredients I prefer to see the contents if the linked form right there. This is not a great reason, the rendering option should be separate from the data joining option but for now this is the only way to control the display.
4. Relations managed by tapforms cannot contain extra information on each relation. For example, if I have a recipes table and an ingredients table and use the one-to-many option I would only get a list of ingredients in my recipe. If I use join with an ID and a join table I can include extra data with the relation like portion size to include in the join.

[T.L. Ford]

Cristian, I agree with you entirely. I was merely pointing it out because my first run with Core Data had me grinding my teeth trying to adapt to a lack of IDs.

I don’t have the iOS version, but I know there are some differences from reading the forums.

Point #4 can be worked around with a script field that builds what you want displayed. A bit rough, but tolerable.

– T

[Author]

Posts