Getting a list of users by sponsor
Permalink
I've added some attributes to users. Specifically, I've added "Clients" which is a number of clients that user is allowed. I've also added "Sponsor", to identify both if an account belongs to a Sponsor, and who that sponsor is.
So I try the following:
This gives me an error stating:
An exception occurred while executing 'SELECT u.uID FROM Users u LEFT JOIN UserSearchIndexAttributes ua ON u.uID = ua.uID WHERE (ak_Sponsor = ?) AND (u.uIsActive = ?) AND (u.uIsValidated != 0) GROUP BY u.uID, u.uName ORDER BY u.uName asc' with params ["GeeEM", true]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ak_Sponsor' in 'where clause'
What am i missing?
So I try the following:
use Concrete\Core\User\UserInfo; $u = new User(); $ui = UserInfo::getByID($u->getUserID()); $clientcount = $ui->getAttribute('Clients'); $list = new \Concrete\Core\User\UserList(); $list->filterByAttribute('Sponsor',$ui->getUserName(),'='); $list->sortByUserName(); $clientlist = $list->get();
This gives me an error stating:
An exception occurred while executing 'SELECT u.uID FROM Users u LEFT JOIN UserSearchIndexAttributes ua ON u.uID = ua.uID WHERE (ak_Sponsor = ?) AND (u.uIsActive = ?) AND (u.uIsValidated != 0) GROUP BY u.uID, u.uName ORDER BY u.uName asc' with params ["GeeEM", true]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ak_Sponsor' in 'where clause'
What am i missing?
Usually attribute handles are all lower case. Are you sure it's "Sposor" and not "sponsor" ?
Also to simplify your code, instead of
you can do
$u = new User(); $ui = UserInfo::getByID($u->getUserID());
you can do
$u = new User(); $ui = $u->getUserInfoObject();
I have checked the capitalization, but I get the same error (with sponsor capitalized).
When I simplify the code, the error swaps to:
Exception Occurred: /public_html/packages/phpcodeblock/blocks/phpcodeblock/view.php(20) : eval()'d code:7 Call to a member function getAttribute() on string (0)
Exception Occurred: /public_html/packages/phpcodeblock/blocks/phpcodeblock/view.php(20) : eval()'d code:7 Call to a member function getAttribute() on string (0)
Is it possible that UserList doesn't have custom attributes exposed to it, so i need to use something else as it's a custom attribute?
So I made some test changes. Now my code reads:
With the result being...
UserInfo: 23
UserInfo: 18
UserInfo: 33
UserInfo: 32
UserInfo: 15
UserInfo: 3
UserInfo: 14
UserInfo: 19
UserInfo: 29
UserInfo: 16
UserInfo: 12
UserInfo: 21
UserInfo: 31
UserInfo: 35
UserInfo: 2
UserInfo: 30
UserInfo: 34
UserInfo: 22
UserInfo: 26
UserInfo: 24
UserInfo: 28
UserInfo: 20
UserInfo: 13
UserInfo: 27
UserInfo: 25
UserInfo: 1
So it looks like I'm only getting the user id. How do i pull the "sponsor" field?
use Concrete\Core\User\UserInfo; $u = new User(); $ui = UserInfo::getByID($u->getUserID()); $clientcount = $ui->getAttribute('Clients'); use Concrete\Core\User\UserList; $list = new UserList(); //$list->filterByAttribute('sponsor',$ui->getUserName(),'='); $list->sortByUserName(); $clientlist = $list->get(); echo 'List:'; if(!empty($clientlist)) { echo '<br/><ul>'; foreach ($clientlist as &$item) { echo '<li>'.$item.'</li>'; }
Viewing 15 lines of 17 lines. View entire code block.
With the result being...
UserInfo: 23
UserInfo: 18
UserInfo: 33
UserInfo: 32
UserInfo: 15
UserInfo: 3
UserInfo: 14
UserInfo: 19
UserInfo: 29
UserInfo: 16
UserInfo: 12
UserInfo: 21
UserInfo: 31
UserInfo: 35
UserInfo: 2
UserInfo: 30
UserInfo: 34
UserInfo: 22
UserInfo: 26
UserInfo: 24
UserInfo: 28
UserInfo: 20
UserInfo: 13
UserInfo: 27
UserInfo: 25
UserInfo: 1
So it looks like I'm only getting the user id. How do i pull the "sponsor" field?
instead of $list->get() try $list->getResults() to get user objects
I get the same result. When I un-comment the line which causes the error, I get the same Unknown columns error.
You have &$item did you mean it to be $item
Both have the same effect. Using &$item simply uses references to the data, rather than the data itself.
I'm not sure it will solve the problem but you can check if the attributes have "Field available in advanced search" flagged. If it's not, try to flag it.
Tried this just now. Same result. It only seems to be exposing the ID's.
If i just want to pull a list of users with text in a custom field, how would I do that? Is UserList the correct approach?
This is completely untested, but what happens if you do this...
<?php Loader::model('user_list'); $ul = new UserList(); //returns a list of users $users = $ul->get(); $select_users_options = array(); //create an array foreach($users as $user) { $select_user[$user->getUserID()] = $user->getUserName(); echo $user->getUserName(); $select_user[$user->getUserID()] = $user->getUserClients(); echo $user->getUserClients(); echo ", "; $select_user[$user->getUserID()] = $user->getUserSponsor(); echo $user->getUserSponsor(); }
Viewing 15 lines of 16 lines. View entire code block.
This is working. I assume getUserSponsor is one of those "Magic functions" I have heard mentioned. While I can make this work, I would prefer to filter the data I am sending back, to cut down on processing at the PHP level. Do you know if this is possible?
So I investigated and the answer is actually silly.
Your code is correct. The problem comes from this line
You see $item is an instance of UserInfo as it should be but when you concatenate it to a string you are effectively telling PHP you want to turn it into a string.
In that situation PHP has 2 options: either there is a function to convert that particular object to a string and PHP will use it or it will throw an error.
Now if you look at the class UserInfo you will find this
Which is why you are getting that list of IDs. So For instance just do
And you'll see that you do have an object.
Your code is correct. The problem comes from this line
echo '<li>' . $item . '</li>';
You see $item is an instance of UserInfo as it should be but when you concatenate it to a string you are effectively telling PHP you want to turn it into a string.
In that situation PHP has 2 options: either there is a function to convert that particular object to a string and PHP will use it or it will throw an error.
Now if you look at the class UserInfo you will find this
public function __toString() { return 'UserInfo: ' . $this->getUserID(); }
Which is why you are getting that list of IDs. So For instance just do
$item->getUserName();
And you'll see that you do have an object.
Thank you. This explains somewhat the issue I've been seeing. In testing, I am able to pull back user names. Is there a way to apply the filter, as filterByAttribute seems not to work? Or do I just need to loop all users each time and check the data line by line?
I tried to filter by attribute the same way you did and it works.
Make sure you're using the attribute's handle and not its name.
Make sure you're using the attribute's handle and not its name.
To be more precise I used all the modifications I mentioned in this post and it worked flawlessly without any error or problem
I used the function getUserInfoObject() which worked and I used getResults() instead of get() because get() is deprecated.
$u = new User(); $ui = $u->getUserInfoObject(); $list = new UserList(); $list->filterByAttribute('sponsor', $ui->getUserName(), '='); $list->sortByUserName(); $clientlist = $list->getResults();
I used the function getUserInfoObject() which worked and I used getResults() instead of get() because get() is deprecated.
I moved the order around a bit and re-tested. Same error.
Then, I had the idea to copy the text from the Attributes UI and paste it in the code. This worked. I don't know if there was some strange invisible character in there or something, but it immediately worked!
Sorry to have been such a pain, but it was hurting my brain!
Then, I had the idea to copy the text from the Attributes UI and paste it in the code. This worked. I don't know if there was some strange invisible character in there or something, but it immediately worked!
Sorry to have been such a pain, but it was hurting my brain!
Don't worry about it, I'm always happy to help and we all learned something so it's all good :)
Good job on figuring out that last bit.
Good job on figuring out that last bit.
