Skip to content

Instantly share code, notes, and snippets.

@azell
Last active December 18, 2015 01:29
Show Gist options
  • Save azell/5704171 to your computer and use it in GitHub Desktop.
Save azell/5704171 to your computer and use it in GitHub Desktop.
Tables:
CREATE TABLE "activities" (
"id" INT UNSIGNED NOT NULL AUTO_INCREMENT,
...
);
CREATE TABLE "interests" (
"id" INT UNSIGNED NOT NULL AUTO_INCREMENT,
"name" VARCHAR(255) NOT NULL,
"parent_id" INT UNSIGNED,
PRIMARY KEY ("id"),
FOREIGN KEY ("parent_id") REFERENCES "interests" ("id")
);
CREATE TABLE "activities_interests" (
"activity_id" INT UNSIGNED NOT NULL,
"interest_id" INT UNSIGNED NOT NULL,
PRIMARY KEY ("activity_id", "interest_id"),
FOREIGN KEY ("activity_id") REFERENCES "activities" ("id"),
FOREIGN KEY ("interest_id") REFERENCES "interests" ("id")
);
Query:
ctx.select().from(ACTIVITIES_INTERESTS).join(INTERESTS).on(
ACTIVITIES_INTERESTS.INTEREST_ID.equal(INTERESTS.ID)).where(
ACTIVITIES_INTERESTS.ACTIVITY_ID.equal(id)).fetch();
Record:
+-----------+-----------+----+------+---------+
|activity_id|interest_id| id|name |parent_id|
+-----------+-----------+----+------+---------+
| 1| 2| 2|Math | 1|
| 1| 48| 48|Travel| {null}|
+-----------+-----------+----+------+---------+
System.out.println("ACTIVITIES.ID -> " + rec.getValue(ACTIVITIES.ID));
System.out.println("INTERESTS.ID -> " + rec.getValue(INTERESTS.ID));
ACTIVITIES.ID -> 2
INTERESTS.ID -> 2
ACTIVITIES.ID -> 48
INTERESTS.ID -> 48
Fields:
"xxx"."activities_interests"."activity_id"
"xxx"."activities_interests"."interest_id"
"xxx"."interests"."id"
"xxx"."interests"."name"
"xxx"."interests"."parent_id"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment