cakePHP find("list") returns empty array

Question

I am trying to make a drop down list of users by using the foreign key [UserID]. In the controller, I have find("list"). When I debug $this->Order->SalesAgent in the controller, it prints the User Object. However, in the view page, when I debug the result of $this->Order->SalesAgent->find("list"), shows and empty array.

Heres the Controller:

    public function edit_sales_agent ($id=null) {
        debug($this->Order->SalesAgent);
        $this->set("users",$this->Order->SalesAgent->find("list"));
        debug($this->users);
    }

and heres the View:

debug($users);
echo $this->Form->create("Order");
    echo $this->Form->input("UserID");

$users is the result of find("list")

Could anyone help me out? Thanks!

Association:

class Order extends AppModel{
    public $useTable = 'CustomerOrder';
    public $primaryKey = 'OrderID';
    **public $belongsTo = array(
        "SalesAgent"=>array(
            "className"=>"User",
            "foreignKey"=>"UserID"**
        ),

Sales Agent Model:

<?php
class User extends AppModel{
    public $useTable = 'UserAccount';
    public $primaryKey = 'UserID';
    public $order = array(
        "User.LastName"=>"asc",
        "User.FirstName"=>"asc"
    );
    public function __construct($id = false, $table = null, $ds = null) {
        parent::__construct($id, $table, $ds);
        $this->virtualFields['full_name'] = sprintf("(%s.FirstName+' '+%s.LastName)", $this->alias, $this->alias);
    }
    public function login($data){
        return $this->find("first",array("conditions"=>$data['User']));
    }
}

UPDATE:

Alright, so I figured out what the problem is but I dont know how to fix it. When I type find(list), this is the query it runs:

SELECT [SalesAgent].[UserID] AS [SalesAgent__0], [SalesAgent].[UserID] AS [SalesAgent__1] FROM [UserAccount] AS [SalesAgent] WHERE 1 = 1 ORDER BY [User].[LastName] asc, [User].[FirstName] asc

THis is the error it proposes:

SQL Error: The column prefix 'User' does not match with a table name or alias name used in the query. [APP/Model/Datasource/Mssql.php, line 749]

The SalesAgent uses class User, which uses table UserAccount

If debug($users) is an empty array in the view - it's because that's what the code in the question returns. debug the find call, not the object. — AD7six
Post the association you've made between the Order and SalesAgent. Also the SalesAgent model — Colonel Mustard
Hey guys I updated the question if you want to take a look at it — Anthony

Anthony Anthony · Accepted Answer · 2015-08-04T15:01:00

I figured it out.

The problem was the query would run:

SELECT [SalesAgent].[UserID] AS [SalesAgent__0], [SalesAgent].[UserID] AS [SalesAgent__1] FROM [UserAccount] AS [SalesAgent] WHERE 1 = 1 ORDER BY [User].[LastName] asc, [User].[FirstName] asc

where it would order by [User].LastName and [User].[FirstName].

User doesnt match the table name OR alias name, so I had to specify the order in cake.

array(
   "fields"=>array("SalesAgent.username"), 
'   "order"=>["SalesAgent.UserID ASC"] 
)

cakePHP find("list") returns empty array

UPDATE:

2 Answers