Stuck on an error using TQL

[Daniel Short]

Hi,

I'm using the following TQL Statement:

FROM Items.Item AS Item JOIN Groups.Group AS Group JOIN Users.User AS
User WHERE User.ID = :userid AND Group.ID = :groupid ORDER BY Label
ASC

I'm getting this error when I try to execute it:

The 2 parameter of the Left function, which is now -1, must be a
positive integer

The error occurred in \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\walkers\Property.cfc: line 45
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\walkers\OrderBy.cfc: line 38
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\walkers\OrderBy.cfc: line 50
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\SelectStatement.cfc: line 241
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\SelectStatement.cfc: line 149
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\SelectStatement.cfc: line 102
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\tql\TQLManager.cfc: line 28
Called from \\.host\Shared Folders\Sites\Site Drive\Core
Files\Transfer\com\Transfer.cfc: line 536
Called from \\.host\Shared Folders\Sites\Site
Drive\ikeeptally.com\_model\items\ItemService.cfc: line 45
Called from \\.host\Shared Folders\Sites\Site
Drive\ikeeptally.com\_handlers\ajax.cfc: line 74

43 :
44 : propertyName = ListGetAt(arguments.text, listLen, ".");
45 : className = Left(arguments.text, Len(arguments.text) -
(Len(propertyName) + 1));
46 :
47 : arguments.buffer.append(" ");

I have no idea where to even begin troubleshooting this one. My
Transfer config in case it's needed:

<?xml version="1.0" encoding="UTF-8"?>
<transfer xsi:noNamespaceSchemaLocation="../xsd/transfer.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<objectDefinitions>

<package name="Users">
<object name="User" table="Users" decorator="_model.Users.UserDecorator">
<id name="ID" type="numeric" />
<property name="Username" type="string" column="Username" />
<property name="Password" type="string" column="Password" />
<property name="FirstName" type="string" column="FirstName" />
<property name="LastName" type="string" column="LastName" />
<property name="EmailAddress" type="string" column="EmailAddress" />
<property name="Confirmed" type="boolean" column="Confirmed" />
<property name="GUID" type="string" column="GUID" />
<property name="stamp" type="date" column="stamp" />
<property name="stampupdated" type="date" column="stampupdated" />

<onetomany name="Groups" lazy="true">
<link to="Groups.Group" column="UserID"/>
<collection type="array">
<order property="SortOrder" order="asc" />
</collection>
</onetomany>

</object>
</package>

<package name="Groups">
<object name="Group" table="Groups" decorator="_model.Groups.GroupDecorator">
<id name="ID" type="numeric" />
<!--
Don't set this as a property since it's part of a manytoone relationship
<property name="UserID" type="numeric" column="UserID" />
-->
<property name="GroupLabel" type="string" column="GroupLabel" />
<property name="SortOrder" type="numeric" column="SortOrder" />
<property name="stamp" type="date" column="stamp" />
<property name="stampupdated" type="date" column="stampupdated" />
<property name="UserID" type="numeric" column="UserID" />

<manytomany name="Items" table="GroupsItems">
<link to="Groups.Group" column="GroupID"/>
<link to="Items.Item" column="ItemID"/>
<collection type="array" />
</manytomany>
</object>
</package>

<package name="Items">
<object name="Item" table="Items" decorator="_model.Items.ItemDecorator">
<id name="ID" type="numeric" />
<property name="Target" type="numeric" column="Target" />
<property name="Label" type="string" column="Label" />
<property name="stamp" type="date" column="stamp" />
<property name="stampupdated" type="date" column="stampupdated" />

<onetomany name="Entries" lazy="true">
<link to="Items.Entry" column="ItemID"/>
<collection type="array">
<order property="stamp" order="desc"/>
</collection>
</onetomany>
</object>

<object name="Entry" table="Entries"
decorator="_model.Entries.EntryDecorator">
<id name="ID" type="numeric" />
<property name="ItemID" type="numeric" column="ItemID" />
<property name="Entry" type="numeric" column="Entry" />
<property name="stamp" type="date" column="stamp" />
<property name="stampupdated" type="date" column="stampupdated" />
</object>
</package>

</objectDefinitions>
</transfer>

Thanks,

Dan

[Jon Messer]

Group and User are both reserved words in sql (which ultimately tql boils down to), you can't do select x as User, y as Group.

You couldn't do that in straight sql either without escaping them.

[Daniel Short]

> Group and User are both reserved words in sql (which ultimately tql boils
> down to), you can't do select x as User, y as Group.
>
> You couldn't do that in straight sql either without escaping them.

That's understandable... but that wasn't the root of this particular
problem. It was that I didn't fully qualify my order by statement. I
have it working now.

Thanks for the catch on the other issue though, I should have caught that.

Dan

[Mark Mandel]

I have an enhancement request to fix that 'Left' error into something
more meaningful, I just need to find time to wrap it in,

Mark

--
E: mark....@gmail.com
W: www.compoundtheory.com