Highlighted

ORM: nested objects without foreign keys

New Here ,
Sep 20, 2019

Copy link to clipboard

Copied

I'm trying to use ORM to CRUD some change logs. I already have a pretty simple table structure that would require 2 joins. I've created entities for both of the joined objects. I'd like to fetch change logs with these objects populated.

 

I already have the tables. I don't have or want any official foreign keys setup; I just use the "_fk" convention to indicate it references the PK of another table.

 

change_logs
---------------
change_log_id
change_log_type_id_fk
message
user_id_fk



change_log_types
----------
change_log_type_id
name


users
----------
user_id
name

 

------------------

component entityname="ChangeLog" accessors="true" persistent="true" table="change_logs" {
  property name="id" type="numeric" column="change_log_id" fieldtype="id" generator="native";
  property name="message" type="string";

  property name="type" column="change_log_type_id_fk" cfc="ChangeLogType";
  property name="user" column="user_id_fk" cfc="User";
}


component entityname="ChangeLogType" accessors="true" persistent="true" table="change_log_types" {
  property name="id" type="numeric" column="change_log_type_id" fieldtype="id" generator="native";
  property name="name" type="string";
}

component entityname="User" accessors="true" persistent="true" table="users" {
  property name="id" type="numeric" column="user_id" fieldtype="id" generator="native";
  property name="name" type="string";
}

 

At best, the "user" property comes back as an int -- the user id; at worst the whole thing blows up failing on some DDL issue or trying to create a foreign key or not understanding the type. What I want is the user and type properties to be their User.cfc and ChangeLogType.cfc entity object equivalents.

 

This shouldn't be that hard. How can I do this? I've tried many things (including messing with hdmxml files) with no luck. Any help would be greatly appreciated. Thank you!

Views

55

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

ORM: nested objects without foreign keys

New Here ,
Sep 20, 2019

Copy link to clipboard

Copied

I'm trying to use ORM to CRUD some change logs. I already have a pretty simple table structure that would require 2 joins. I've created entities for both of the joined objects. I'd like to fetch change logs with these objects populated.

 

I already have the tables. I don't have or want any official foreign keys setup; I just use the "_fk" convention to indicate it references the PK of another table.

 

change_logs
---------------
change_log_id
change_log_type_id_fk
message
user_id_fk



change_log_types
----------
change_log_type_id
name


users
----------
user_id
name

 

------------------

component entityname="ChangeLog" accessors="true" persistent="true" table="change_logs" {
  property name="id" type="numeric" column="change_log_id" fieldtype="id" generator="native";
  property name="message" type="string";

  property name="type" column="change_log_type_id_fk" cfc="ChangeLogType";
  property name="user" column="user_id_fk" cfc="User";
}


component entityname="ChangeLogType" accessors="true" persistent="true" table="change_log_types" {
  property name="id" type="numeric" column="change_log_type_id" fieldtype="id" generator="native";
  property name="name" type="string";
}

component entityname="User" accessors="true" persistent="true" table="users" {
  property name="id" type="numeric" column="user_id" fieldtype="id" generator="native";
  property name="name" type="string";
}

 

At best, the "user" property comes back as an int -- the user id; at worst the whole thing blows up failing on some DDL issue or trying to create a foreign key or not understanding the type. What I want is the user and type properties to be their User.cfc and ChangeLogType.cfc entity object equivalents.

 

This shouldn't be that hard. How can I do this? I've tried many things (including messing with hdmxml files) with no luck. Any help would be greatly appreciated. Thank you!

Views

56

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 20, 2019 0
Adobe Community Professional ,
Sep 22, 2019

Copy link to clipboard

Copied

There's a lot going on here. From what I can see, the names of the tables should be singular, hence change_log, change_log_type and user. Also, I expected the changelog to contain multiple users. Were you perhaps aiming for something like this:

 

component persistent="true" table="change_log" {
property name="id" type="numeric" column="change_log_id" fieldtype="id";
property name="message" type="string";

property name="type" type="one-to-one" cfc="ChangeLogType";
property name="users" singularname="user" fieldtype="one-to-many" cfc="User" fkcolumn="change_log_id" cascade="all";
}


component persistent="true" table="change_log_type" {
property name="id" type="numeric" column="change_log_type_id" fieldtype="id";
property name="name" type="string";
}

component persistent="true" table="user" {
property name="id" type="numeric" column="user_id" fieldtype="id";
property name="name" type="string";
property name="changelog" fieldtype="many-to-one" cfc="changeLog";
}

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2019 0
New Here ,
Sep 22, 2019

Copy link to clipboard

Copied

What I'm trying to do is utilize ORM with a DB schema that already exists. So renaming that tables to be singular isn't something I should need to do.

 

The main ask here isn't to allow for a one-to-many on ChangeLog to User because only 1 user creates the log; which is why the change log table has a user_id_fk representing the user that created it. What I want is a ChangeLog object with a "user" property  that is the User object of the user that created the log.

 

I don't see the need in cluttering up the User entity with a defined relationship to ChangeLog. Why? User doesn't need to know ChangeLog is referencing it. This is User ... this could be references *everywhere* ... all ChangeLog should need to know is the column representing the referencing id and the entity to join to; it should be able to assume without further configuration that is should join that column to the PK of the referenced entity.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2019 0
BKBK LATEST
Adobe Community Professional ,
Sep 24, 2019

Copy link to clipboard

Copied

Thanks for the clarification. As I now understand it, I would suggest simply

 

property name="user" column="user_id_fk" cfc="Users";

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 24, 2019 0