calling findAll() from plugin odd behavior

[Ben D]

Hello,

Now that wheels 2.2 is out and the old plugin engine is back in place I've started back on upgrading our old Wheels 1.4.5 app and am about 90% of the way there but I'm having an issue with the SingleTableInheritance plugin and the wheels core findAll() function. For some reason it's adding a column name twice and I'm not entirely sure why.

If I run the same exact code on my wheels 1.4.5 branch the query in question looks like this and runs fine:

SELECT id FROM (

    SELECT TOP 100 id,lastName,firstName FROM (

        SELECT DISTINCT TOP 100 lf2_User.userID AS id,lf2_Person.lastName,lf2_Person.firstName 

        FROM lf2_User 

        LEFT OUTER JOIN lf2_Person ON lf2_User.personID = lf2_Person.personID 

        LEFT OUTER JOIN lf2_ftri_RDC ON lf2_User.rdcID = lf2_ftri_RDC.rdcID 

        LEFT OUTER JOIN lf2_UserRole ON lf2_User.userID = lf2_UserRole.userID 

        INNER JOIN lf2_Role ON lf2_UserRole.roleID = lf2_Role.roleID 

        LEFT OUTER JOIN aims_vendors ON lf2_User.vendorID = aims_vendors.id

WHERE

lf2_User.active = 1

 AND 

lf2_Role.isexternal = 0

ORDER BY lf2_Person.lastName ASC,lf2_Person.firstName ASC,lf2_User.userID ASC) AS tmp1 ORDER BY lastName DESC,firstName DESC,id DESC) AS tmp2 ORDER BY lastName ASC,firstName ASC,id ASC

Now when I switch back to my wheels 2.2.0 branch, the same query now looks like this:

SELECT id FROM (

    SELECT TOP 100 id,lastName,firstName,id FROM (

        SELECT DISTINCT TOP 100 lf2_User.userID AS id,lf2_Person.lastName,lf2_Person.firstName,lf2_User.id

        FROM lf2_User 

        LEFT OUTER JOIN lf2_Person ON lf2_User.personID = lf2_Person.personID 

        LEFT OUTER JOIN lf2_ftri_RDC ON lf2_User.rdcID = lf2_ftri_RDC.rdcID 

        LEFT OUTER JOIN lf2_UserRole ON lf2_User.userID = lf2_UserRole.userID 

        INNER JOIN lf2_Role ON lf2_UserRole.roleID = lf2_Role.roleID 

        LEFT OUTER JOIN aims_vendors ON lf2_User.vendorID = aims_vendors.id 

        WHERE lf2_User.active = 1 

        AND lf2_Role.isexternal = 0 

        ORDER BY lf2_Person.lastName ASC,lf2_Person.firstName ASC,lf2_User.id ASC) AS tmp1 ORDER BY lastName DESC,firstName DESC,id DESC) AS tmp2 ORDER BY lastName ASC,firstName ASC,id ASC

It's almost identical but if you look at the Select TOP 100 and Select Distinct Top 100 on the wheels 2.2.0 version it is appending an extra  "id" column and adding in lf2_User.id

The User Model does have a column called "userID" that is being mapped to the name "id"

 property(name="id", column="userId");

The code that runs that particular query is the following in the User model which calls the findAll() function that is in the SingleTableInheritance plugin which then calls core.findAll(argumentCollection=arguments):

    <cfreturn this.findAll(

      include="person,rdc,userRoles(role),vendor",

      where=ArrayToList(local.where, " AND "),

      order=local.order,

      page=arguments.page,

      perPage=100

    )>

I'm not seeing anything blatantly obvious to me that I can pass to the core findAll() function. Or maybe there is something on the property I can add to modify this behavior? 

If anyone has any ideas on this, it would be greatly appreciated.

Thanks,

Ben

[Ben D]

I was able to dig into this further and found that the Person Model I'm including is causing this issue, but it only appears to be when enabling pagination for this area. If I either remove the Person model from the include attribute or I remove the page and perPage attributes and keep the Person model in it works fine.

I'm not sure if it helps to explain but I will just in case.

a User belongs to a Person with the following association:

belongsTo(name="person", joinType="outer");

And a Person hasOne user:

hasOne(name="user", dependent="delete");

each model has a property with a name of id and the column it belongs to.

property(name="id", column="personID");

property(name="id", column="userId");

I'm just not sure why the Person object alone would be throwing off the query that is used for pagination other than maybe something is misconfigured. But I'm not seeing the issue.

I'll continue digging into this, but if anyone has any insight, that would be great!

Thanks again,

Ben

[Tom King]

I've pinged Andy (who wrote the original plugin) to see if he has any thoughts.

T

[Ben D]

Thanks Tom, I appreciate that. I'll keep digging in and will hopefully track down the issue just in case there is no response from Andy.

[Ben D]

Hey Tom,

I just tried something to see if this was actually due to the STI plugin and it seems it's not related to that at all. I edited the plugin so the findAll() function is now called findAllSTI because I know the area of the app where I'm running into this issue isn't using STI for any of the Models involved. I'm still getting the same error. It's in the findAll() function that is in the core framework when it is trying to get the paginationIds. The error is attached in the screenshot.

I've been doing some comparison of files(model/adapters/cfquery.cfm, model/adapters/Base.cfc, model/adapters/SQLServer.cfc, model/read.cfm)  between wheels 1.4.5 and 2.2.0 and the changes between the two are pretty minimal. But somewhere along the way when the columns get passed into findAll() it's adding that extra id column.

I haven't been able to figure out exactly where it's happening, but since you know the core framework code maybe you might be able to pinpoint a little better.

Thanks,

Ben

 

[Tom King]

Sorry haven't had much headspace to think about this.
One thing you could try (just to properly narrow it down) is to skip the inbuilt pagination, and use your own count (i.e pass in count=x to findAll).

When Wheels paginates a query it actually does 3 queries, one to get the total count of possible records, one to get the specific IDs and then one to get the contents.

I'm just thinking would be good to knock that out the equation.
T

[Ben Densmore]

Great, I will give that a try. Thanks for the suggestion on that. 

Ben

--
You received this message because you are subscribed to a topic in the Google Groups "CFWheels" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cfwheels/WdiFYrU98EQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cfwheels+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cfwheels/c74d966a-cd08-4055-a857-a58b077ae866n%40googlegroups.com.

[Andrew Bellenie]

Hi Ben

Sorry it's taken me a while to chime in here. I'd actually forgotten I wrote that plugin until Tom gave me the nudge. Seems the plugin itself has been ruled out now anyway, which makes sense, all it does is append a where clause to any models that has it enabled.

I vaguely remember that the way wheels handles pagination queries is different for MS SQL to MySQL and is quite complex. Tom's suggestion here makes sense.

Andy

You received this message because you are subscribed to the Google Groups "CFWheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cfwheels/CAA0qRMiB7q0h%2B%2B0YqkTRBxncFC_TNiOuxNLkJToiq9A9W1vPFw%40mail.gmail.com.

[Ben D]

Thanks for checking in, Andy. I know the plugin is pretty old at this point in time. I too, am glad that it's not an issue with the Plugin. Makes my life a little easier to get this fixed.

Ben