cfwheels
cfwheels copied to clipboard
cfwheels
Invalid Keys shown in included relationships when using findAll()
Below, you can see request.id and vendor.id are using the value from "id". This occurs when using the select clause and returning as a object/struct. I'm guessing there is a namespace conflict that isn't handled properly by the ORM, since the tables all use an "id" field as there key.
select: delistedat,id,modelnumber,name,primarylabids,requestid include: requests,vendor
ACF 2016 CFWheels 2.1
When you specify your own select columns and one of those columns is repeated in both tables, you need to clarify which of those columns you want OR add an alias for each repeateded column. I'm guess the main ID is some other table (because you have two includes) so if you want them both, I would do this:
select: delistedat,vendors.id AS vendorid,requests.id AS requestid,modelnumber,name,primarylabids,requestid
Wheels will only try to correct duplicates if you don't select your own columns (prefacing the name of the repeated column with the table name in the results)
If you're implicitly selecting the fields, you'll need to do requests.id AS requestid etc..
lol, you guys are fast.
Doesn't doing this invalidate any calculated props though?
Doesn't seem to for my calc prop, 'primarylabids'. I tried it with a simpler one too, but same problem.
Message: [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'primarylabids'.
Used the following select clause: select: delistedat,devicemodels.id as devicemodelid,modelnumber,name,requests.id as requestid,primarylabids
FYI: It's important that AS is uppercase. Next, you can't nest your aliases. Ex: requestid,primarylabids should simply be "primarylabids" to appear where it should
@dbelanger , I'm not sure I follow your 2nd suggestion. Using the above example, how would the select param need to change to make this work, or are you saying that nesting an alias in the call will not work?
My second suggestion is simply not to treat your alias name as a struct (no dot notation). It should be a simple one word string (ex. primarylabels).
Thanks for your patience, but I don't see where I'm using dot notation in an alias name. I do use dot notation for the column name, but @neokoenig mentioned above that I needed to do this in order to discern one "id" field from another.
You are correct, I mistook a comma for a period (my bad) - the error was then only because you weren't using uppercase AS.
No worries. Though, even with the uppercase 'AS', I'm unable to use calc props when implicitly selecting fields.
@bshea5 The original issue that you posted of the same ids in multiple nodes was due to the alias missing and no table dot notation. I replicated your issue and it was the same as yours, i.e. if we implicitly select the columns, and same column exist in multiple tables then we have to pass in table name as well and use aliases otherwise it returns us same id in the reference table as well.
Passing in alias solved your issue of ids but then the new issue came, and that is, when we pass in table name or alias in the select argument, wheels does not add the calculated properties to the select clause and just passes the original calculated property name in the select clause which causes the invalid column name error.
Here is a reference to that condition: https://github.com/cfwheels/cfwheels/blob/develop/wheels/model/sql.cfm#L274
In this condition, there is a code block that replaces the calculated property's name to its definition in the select clause and adds aliases as well.
So, we will need to add the same code logic of adding calculated properties in case the developer specified "AS" or "." in the select clause.
@bshea5 I made some changes in the code and this is working for me and adds the correct columns and also adds the calculated properties in the SELECT clause.
As the original condition was checking the entire list for any "." or " AS ", it did not add calculated props if passed in the list. Now I added a condition that checks every column one by one for "." or " AS " instead of the entire list at the same time. This way the condition will work as before for columns with alias or "." but will work correctly if calculated properties are passed in.
Here is the PR. https://github.com/cfwheels/cfwheels/pull/1249
I have tested with some different select clauses as arguments and all of them are working for me.
That's awesome. I'm not active on the project where this was an issue (nor even doing CF these days) so I think I'll leave it up to others to confirm.