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
– AD7six, Commented Jul 31, 2015 at 7:19
Post the association you've made between the Order and SalesAgent. Also the SalesAgent model — Colonel Mustard
– Colonel Mustard, Commented Jul 31, 2015 at 10:21
Hey guys I updated the question if you want to take a look at it — Anthony
– Anthony, Commented Aug 4, 2015 at 14:44

Anthony · Accepted Answer · 2015-08-04 15:01:00Z

1

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"] 
)

answered Aug 4, 2015 at 15:01

Anthony

2634 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fury · Accepted Answer · 2015-08-03 10:12:41Z

0

First try to configure your model association.

What is belong to what and the by running this

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

view try this

echo $this->Form->input("user_id");

You should have list of users.

edited Aug 3, 2015 at 10:12

answered Jul 30, 2015 at 22:26

Fury

4,7867 gold badges54 silver badges83 bronze badges

1 Comment

AD7six Over a year ago

This is the same as the code in the question. The user doesn't appear to have a user_id field.

Collectives™ on Stack Overflow

cakePHP find("list") returns empty array

UPDATE:

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE:

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related