http://coldfusion.com logo
Join Slack
Powered by
Checking out Quick ORM and trying to figure out ho...
# box-products
b
Checking out Quick ORM and trying to figure out how to set up the correct relationship(s). Details in the thread.
For example, given the following entities: Users.cfc
Copy code
component table="dbo.tblUsers" extends="quick.models.BaseEntity" accessors="true" {

	variables._key = "userId";

	property name="userId";
	property name="userName";
	property name="active";
	property name="countryId";

}
Countries.cfc
Copy code
component table="dbo.Countries" extends="quick.models.BaseEntity" accessors="true" {

	variables._key = "countryId";

	property name="countryId";
	property name="countryName";
	property name="countryCode";

}
I'll always want the country info (not just the id) when I get a user. I added the following to the users component and it gets the expected data back but it runs two individual queries instead of an inner join. So I am assuming I am just doing something wrong with the relationship. 
country = () => belongsTo( "Countries", "CountryId" );
And this is what I'm doing to get the records 
getInstance( "Users" ).with( "country" ).get()
s
a couple small tweaks not exactly related to your question, I'd keep your entity naming singular, because one Country is not a Countries and one User has a Country, not a Countries. second, and reasonable people may disagree, I recommend spelling out all the arguments in your relationships, so:
Copy code
function country() { return belongsTo( relationName = "Countries", foreignKey = "countryId" ); }
in this particular instance where you only have a couple fields, rather than doing an eager load and 
.with()
I'd use a subselect to just pull in 
countryName
and 
countryCode
Copy code
scope withCountryData( qb ) {
   qb.addSubselect( "countryName", "country.countryName" );
   qb.addSubselect( "countryCode", "country.countryCode" );
}
this way you're only making one query (small savings) but you're not spinning up an entire CFC for each user (bigger savings)
if your relationship isn't returning what you expect, I find having the arguments spelled out makes it easier to debug
b
Thanks, @sknowlton - That was a scaled back example to allow focus on just this particular relationship but there are others (and more properties). My entity names are also actually singular (and suffixed with Entity. i.e. UserEntity.cfc and CountryEntity.cfc).
I will checkout the subselects
I did manage to get the subselect to work so thanks for that. I'm sure I'll get some mileage out of it. This one really should be an inner join though since there are multiple more country properties. I only used id, name and code for the sake of a smaller example.
w
@bhartsfield returning to your original question on eager loading using 
with().
The 
with()
construct is especially useful when you are loading multiple users. Let’s say you have 10 users from 3 countries, and you want to display 
oUser.getCountry.getName()
. Instead of firing 10 individual queries for each user’s country it is just firing two queries. One for all users and one for all related companies. So the benefits are not visible with just one user object. You can read about it here https://quick.ortusbooks.com/guide/relationships/eager-loading#with I agree with Sam the subselects might be more efficient in some cases, especially if you need only one or just a few properties from the nested object.
b
I thought the scaled down example would make for a simpler question but there are too many properties for them all to be subselects in every entity that needs a country. I was really expecting there to be a relationship here that would make it one query with an inner join.
w
Well, the with syntax is only 2 queries, no matter if you retrieve one or 50 users. Is that s problem? I think it is still quite efficient
b
well, certainly more efficient than, for example, if it ran 1 query per row or something but not more efficient than a single inner join query to get the same data.
If that's not an option here, fine. I just assumed it was and am just on the hunt for the syntax to achieve it.
I know Quick orm CAN do inner joins; I saw it somewhere (I believe it was on a belongsToMany() test I did for some other tables.
s
If you want one query with an inner join, make a view and an entity based off that view. otherwise you're kind of defeating the purpose of the 'R' in the ORM. You can get Quick to do inner joins, but you're dropping to QB to do that and returning an array of structs instead of objects
8 Views
Open in Slack
PreviousNext
Powered by