The multi-part identifier could not be bound

Yii 1.1 General Discussion
1

Hello,

I’m not sure if this is a bug, or if something simple is just out of place.

I am using Microsoft’s’ SQL server for my DB backend.

When I create a new webapp using the yii commandline tool, and then create my models and controllers via gii.

When I goto the admin page for any table, the grid loads perfectly.

  • note, no relationships are defined @ the model level, nor are any relational queries used for the grid columns. Just the standard default admin page.

When I click on a column header, it too works perfectly.

However, when I click to goto any other page than the 1st page, I get this error:

The multi-part identifier t.FIELD_SORTING_BY could not be bound.

Of course, FIELD_SORTING_BY is the place holder for any field I try to sort by. Any table, any field, same error.

I’m not sure if this has to do with the MSSQL DB backend, but I have tried both drivers “mssql” & “sqlsrv”, both have the same results.

Anyone have any ideas?

Thanks,

2

No one else is having this issue?

I cannot get CGridView to sort and allow for any results other than pg1 of the results without getting this error out of the box.

Any ideas would be really helpful. Does anyone have working CGridView + sort by a column + click other than pg1 against the Microsoft SQL Server [2005 or 2008]?

Thanks,

3

It has been a little while since this post, but I would like to report the same problem. Well sort of… actually with SQL Server 2000 (don’t ask). I, however, seem to be getting a different error code.

Error 10007 – Column prefix [t] does not match a table name or alias

A trace shows that this query triggers the error:

SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP 40 * FROM [dbo].[_Ms_Deadlines] [t] ORDER BY [t].[EnrPer]) as [inner top table] ORDER BY [t].[EnrPer] DESC) as [outer top table] ORDER BY [t].[EnrPer] ASC

By the way, I am using dblib driver.

Anyone have any insight? This doesn’t seem to be a problem with other database sources.

4

Quick reply to my own post…

The correct query should be (or at least seems like it should be):

(expected result)

SELECT * FROM (

    SELECT TOP 20 * FROM (


            SELECT TOP 40 * FROM [dbo].[_Ms_Deadlines] [t] 


	ORDER BY [t].[EnrPer]) as [__inner top table__] 


ORDER BY [__inner top table__].[EnrPer] DESC) as [__outer top table__]

ORDER BY [outer top table].[EnrPer] ASC

Notice that the table aliases in the second two ‘order by’ clauses are mislabeled:

(actual result)

SELECT * FROM (

    SELECT TOP 20 * FROM (


            SELECT TOP 40 * FROM [dbo].[_Ms_Deadlines] [t] 


            ORDER BY [t].[EnrPer]) as [__inner top table__] 


    ORDER BY [t].[EnrPer] DESC) as [__outer top table__]

ORDER BY [t].[EnrPer] ASC

Have not yet started digging into the yii codebase yet to see what’s up, but I’m thinking it might actually be a PDO thing…

5

As promised, here is a much better patch for this issue:

First find: framework/db/schema/mssql/CMssqlCommandBuilder.php line 205

This line should look like this:


$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}";

Add these two lines directly before line 205


$orginalOrdering = str_replace('[t]', '[__outer top table__]', $orginalOrdering);

$reverseOrdering = str_replace('[t]', '[__inner top table__]', $reverseOrdering);

This "patch" basically just replaces the [t] alias with the expected alias for both the inner and outer tables.