<p>Not 100% certain, but I suspect that with a standard ElementCriteriaModel it's all or nothing, as craft generally returns the full userModel including all custom fields. If you're worried about memory limitations, then you might want to either process the users in batches and/or create your own custom query. Something like this perhaps:</p>

<pre><code>// define query
$criteria = craft()-&gt;elements-&gt;getCriteria(ElementType::User);

$query = craft()-&gt;elements-&gt;buildElementsQuery($criteria);
$query-&gt;select(array('elements.id AS id', 'field_extid AS extid', 'field_lastupdatetime AS lastupdatetime'));
$query-&gt;limit(null);
$results = $query-&gt;queryAll();

foreach($results as $result)
{
    // compare craft values to json

    // var_dump($result['extid']) // uncomment to test
    // var_dump($result['lastupdatetime']) // uncomment to test
    // set $userExists equal to true or false if needed

    // create/retrieve user
    if ($userExists) {
        $user = craft()-&gt;users-&gt;getUserById($result['id']);
    } else {
        $user = new UserModel();
    }

    // update, save, etc
    // ...
}
</code></pre>

<p>You can also use <code>var_dump($results)</code> to test whether you are getting the expected results.</p>

CC BY-SA 3.0
2015-04-14T18:41:15.273Z
I have no idea what to fill in here: $query->select(array('elements.id AS id', 'field_myField1 AS myField1', 'field_myField2 AS myField2'));

Could you be a bit more specific? My fields are called extid and lastupdatetime. The id on which I will have to find my users is not the craft id but the extid field.
Tom De Smet
CC BY-SA 3.0
2015-04-14T19:09:11.173Z
I updated the field names and added some notes. See if that helps at all. I would still keep the craft element id as you will need that to eventually retrieve the user.
Douglas McDonald
CC BY-SA 3.0
2015-04-14T19:13:46.300Z
After a lot of trial and error, I came upon the same SELECT statement, but it just turns up empty... And I do have users in my database :)

I'm looking at the actual database too to understand what the SELECT is supposed to do, but I just don't see it... Don't I have to add a WHERE statement somewhere? So it knows it should compare the elements.id property?
Tom De Smet
CC BY-SA 3.0
2015-04-14T19:21:46.260Z
I tried it myself, and it doesn't like the `$query->limit('none');`. Try removing that line.
Douglas McDonald
CC BY-SA 3.0
2015-04-14T19:21:47.040Z
Allright, after some serious brainhurting thinking I understand what I'm doing here. And why the array just kept returning empty. It seems that the limit('none') is returning an empty array. When I delete that line it works! Thanks!
Tom De Smet
CC BY-SA 3.0
2015-04-14T19:35:45.273Z
But to answer your question, you do not really need a WHERE clause in this case because you are grabbing all user records from the elements table; and the element id (in the craft_elements table) is the same as the user id (in the craft_users table), so you can it as is to retrieve the user.
Douglas McDonald
CC BY-SA 3.0
2015-04-14T22:13:50.987Z
@TomDeSmet `limit` expects an integer value. The default limit in Craft is 100. If you want to get results with no limit, use `limit(null)`
Lindsey D
CC BY-SA 3.0
2015-04-15T01:23:34.610Z
Thanks @LindseyD. I wasn't sure if the limit applied to custom element queries or the proper syntax — I'll add that to the answer.
Douglas McDonald
CC BY-SA 3.0
2015-06-01T16:11:33.843Z
I have a fresh Craft install and I'm trying to run the same query but the fields have different names. And I can't get it to work again... problem is, I still don't understand the query: why do we look in the elements table? Why is there field_ before the fieldname, what is the AS fieldname text?
Tom De Smet
CC BY-SA 3.0
2015-06-01T17:26:45.837Z
@Tom 'Users' are a type of 'element' (as are entries, categories, tags, and just about everything else in craft). We are then using `getCriteria(ElementType::User)` to specify that we only want to return elements of type 'User'. We are then building a query to search against the 'craft_content' table, using the actual DB column names. Any custom fields that you have are going to start with 'fields_', followed by the field handle. The 'AS something' means that you want to refer to that field by the shortened name throughout the rest of the query (think of it as an alias).
Douglas McDonald
CC BY-SA 3.0
2015-06-01T17:48:27.553Z
Allright thanks. I solved it by changing to a DBquery as Brandon suggested that ElementQueries weren't really supposed to be used, hence there is no official documentation. I also understand the DBquery a bit better, but thanks for your great help!
Tom De Smet
CC BY-SA 3.0
2015-04-14T16:09:25.917Z
2015-04-15T01:26:13.077Z
2015-04-15T01:26:13.077Z
Douglas McDonald
<p>I'm writing a plugin that adds or updates users profiles in Craft from a JSON file. Every user profile could have a lot of custom fields in the future which store a lot of data.
To check if a user exist and if its data is up to date with the data in the JSON file, I need to retrieve 2 fields from every user profile. Is that possible with the elementCriteriaModel?</p>

<p>I've seen examples of the criteriaModel by which you filter which users are returned by adding parameters. And then you get an array back filled with users that match the criteria and all of their custom fields.</p>

<p>But I need all users returned, but not with all their custom fields. Because I think that will slow things down.
Is it possible to fetch all users but returning only 2 specific fields by using the criteria model? Or some other way?
I know I can filter the results afterwards and return another array, but then the slow database query is allready been made. I want to limit the amount of queries made and the amount of data retrieved to the bare necessities.</p>

<database><elementcriteriamodel>
Returning specific fields with elementCriteriaModel
# Returning specific fields with elementCriteriaModel

(Asked by: username_0 on 2015-04-14)

I'm writing a plugin that adds or updates users profiles in Craft from a JSON file. Every user profile could have a lot of custom fields in the future which store a lot of data.
To check if a user exist and if its data is up to date with the data in the JSON file, I need to retrieve 2 fields from every user profile. Is that possible with the elementCriteriaModel?

I've seen examples of the criteriaModel by which you filter which users are returned by adding parameters. And then you get an array back filled with users that match the criteria and all of their custom fields.

But I need all users returned, but not with all their custom fields. Because I think that will slow things down.
Is it possible to fetch all users but returning only 2 specific fields by using the criteria model? Or some other way?
I know I can filter the results afterwards and return another array, but then the slow database query is allready been made. I want to limit the amount of queries made and the amount of data retrieved to the bare necessities.

**Tags:** database, elementcriteriamodel


---

## 1 answers

### Answer by username_1 on 2015-04-14. Score: 3 | Accepted answer

Not 100% certain, but I suspect that with a standard ElementCriteriaModel it's all or nothing, as craft generally returns the full userModel including all custom fields. If you're worried about memory limitations, then you might want to either process the users in batches and/or create your own custom query. Something like this perhaps:
// define query
$criteria = craft()->elements->getCriteria(ElementType::User);

$query = craft()->elements->buildElementsQuery($criteria);
$query->select(array('elements.id AS id', 'field_extid AS extid', 'field_lastupdatetime AS lastupdatetime'));
$query->limit(null);
$results = $query->queryAll();

foreach($results as $result)
{
    // compare craft values to json

    // var_dump($result['extid']) // uncomment to test
    // var_dump($result['lastupdatetime']) // uncomment to test
    // set $userExists equal to true or false if needed

    // create/retrieve user
    if ($userExists) {
        $user = craft()->users->getUserById($result['id']);
    } else {
        $user = new UserModel();
    }

    // update, save, etc
    // ...
}

You can also use var_dump($results) to test whether you are getting the expected results.

> **Comments:**
> **username_0**: I have no idea what to fill in here: $query->select(array('elements.id AS id', 'field_myField1 AS myField1', 'field_myField2 AS myField2'));  Could you be a bit more specific? My fields are called extid and lastupdatetime. The id on which I will have to find my users is not the craft id but the extid field.  
> **username_1**: I updated the field names and added some notes. See if that helps at all. I would still keep the craft element id as you will need that to eventually retrieve the user.  
> **username_0**: After a lot of trial and error, I came upon the same SELECT statement, but it just turns up empty... And I do have users in my database :)  I'm looking at the actual database too to understand what the SELECT is supposed to do, but I just don't see it... Don't I have to add a WHERE statement somewhere? So it knows it should compare the elements.id property?  
> **username_1**: I tried it myself, and it doesn't like the `$query->limit('none');`. Try removing that line.  
> **username_0**: Allright, after some serious brainhurting thinking I understand what I'm doing here. And why the array just kept returning empty. It seems that the limit('none') is returning an empty array. When I delete that line it works! Thanks!  
> **username_1**: But to answer your question, you do not really need a WHERE clause in this case because you are grabbing all user records from the elements table; and the element id (in the craft_elements table) is the same as the user id (in the craft_users table), so you can it as is to retrieve the user.  
> **username_2**: @TomDeSmet `limit` expects an integer value. The default limit in Craft is 100. If you want to get results with no limit, use `limit(null)`  
> **username_1**: Thanks @LindseyD. I wasn't sure if the limit applied to custom element queries or the proper syntax — I'll add that to the answer.  
> **username_0**: I have a fresh Craft install and I'm trying to run the same query but the fields have different names. And I can't get it to work again... problem is, I still don't understand the query: why do we look in the elements table? Why is there field_ before the fieldname, what is the AS fieldname text?  
> **username_1**: @Tom 'Users' are a type of 'element' (as are entries, categories, tags, and just about everything else in craft). We are then using `getCriteria(ElementType::User)` to specify that we only want to return elements of type 'User'. We are then building a query to search against the 'craft_content' table, using the actual DB column names. Any custom fields that you have are going to start with 'fields_', followed by the field handle. The 'AS something' means that you want to refer to that field by the shortened name throughout the rest of the query (think of it as an alias).  
> **username_0**: Allright thanks. I solved it by changing to a DBquery as Brandon suggested that ElementQueries weren't really supposed to be used, hence there is no official documentation. I also understand the DBquery a bit better, but thanks for your great help!  

---

