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!
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";
}
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.
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";