CakePHP find ("list") returns an empty array
I am trying to make a dropdown list of users using the [UserID] foreign key. In the controller I found ("list"). When I debug $this->Order->SalesAgent
in the controller, it prints the custom object. However, in the view page, when I debug the result $this->Order->SalesAgent->find("list")
, it shows an empty array as well.
Here is 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 sees the view:
debug($users);
echo $this->Form->create("Order");
echo $this->Form->input("UserID");
$users
is the result find("list")
Can anyone help me? Thank!
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:
Ok, so I figured out what the problem was, but I don't know how to fix it. When I type find (list), this is the query it executes:
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 he suggests:
SQL error: The User column prefix does not match the table name or alias used in the query. [APP / Model / Datasource / Mssql.php, line 749]
SalesAgent uses User class which uses UserAccount table
source to share
I understood that.
The problem was the request was running:
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 he will order [User] .LastName and [User]. [FirstName].
User does not match table name or alias, so I needed to specify the order in the cake.
array(
"fields"=>array("SalesAgent.username"),
' "order"=>["SalesAgent.UserID ASC"]
)
source to share
First try setting up the model association.
What belongs to what and when running this
public function edit_sales_agent ($id=null) {
$users = $this->Order->SalesAgent->find("list");
$this->set("users",$users);
}
view this topic
echo $this->Form->input("user_id");
You should have a list of users.
source to share