[Coldbox 5.x] - ORM and property statements in my model

I am new to using the coldbox ORM module (cborm) and trying to figure out if there is an alternate way to pull in data from a lookup table using the property syntax.

If you see the 2nd property statement, I am using the formula attribute to query the db table to get the text friendly version of the field “employment_type”. This does work, but I’m wondering is there another way to do this so I don’t have SQL inline like this.

property name="employmentTypeId" column="employment_type_id"; property name="employmentType" formula = "select e.employment_type from employment_type e where e.employment_type_id = employment_type_id";

Any help appreciated.

Based on what I see below, you should be configuring the “employment type” as a many-to-one relationship, rather than hard-coding foreign keys as simple value properties:

property name=“employmentType”

fieldtype=“many-to-one”

cfc=“EmploymentType”

fkcolumn=“employment_type_id” // this references the fieldtype=”id” of the EmploymentType entity

fetch=“join”; // fetch=”join” makes it instantly available

The above replaces your two property declarations. Then you would retrieve your type with getEmploymentType().getId() or getEmploymentType().getName(), etc after the entity is retrieved.

Jon

Thanks for the reply Jon,

This makes sense, however it does mean that I would need to create a new CFC for each lookup for lack of a better word. In my example I include just one field, but I also have the state_code, state_name scenario, where I may want to retrieve the text name of the state based on the state_code (e.g. TX for Texas, or if I was using a numeric ID for state_code and then wanting to grab the text value. I’d have to create a State.cfc and follow what you did in your example. Given that I have quite a few lookup tables where I need to do this it seems that I will end up with tons of CFCs just to get this one value if that makes sense.

It seems like a bit of clutter, but if that’s how it’s done I guess I will do it, but seems a bit overkill.

“Given that I have quite a few lookup tables where I need to do this it seems that I will end up with tons of CFCs just to get this one value if that makes sense.”

That’s kind of the nature of OOP and Hibernate. You can certainly use computed properties to access common join keys fast, though.

By not using relationships, you negate all of the benefits of using CBORM’s auto population and ORM’s built in relationship management.

Othwerwise, you’re going to be manually assigning those keys and will always have to use raw SQL to retrieve the relationships.

All that said, you can very easily scaffold those lookup tables using CommandBox:

box coldbox create

orm-entity

entityName=State

table=my_table

directory=models/lookup

primaryKey=id

primaryKeyColumn=state_id

properties=name,abbreviation

–tests --activeEntity

Depending on how many you have, you should be to generate most of what you need in a few minutes.

HTH,

Jon

Thanks for helpful reply Jon. I was not aware you could do all that with Commandbox. I guess I should play around with it more. I typically just use it to spin up a server, install modules and that’s it.

There is a rule of CommandBox: Whatever you are doing with it, there’s still more you’re not using. This is true even of me (the lead developer of CommandBox) because there’s people doing cool things I didn’t even think of yet! :slight_smile: