Filter on extended field with pdoUsers

101 Posts

mintnl Reply #1, 12 years, 3 months ago

Hi,

As i'm trying to create a list of users that belong to a certain usergroup.
I stumbled upon pdoUsers as part of pdoTools.
All seems powerfull however i want something extra it might not can do. Who can help me here?

I have a user that also has extended profile fields.
I want to select users, based on a value that's set in one of these extended fields (eg customvalue).

[[!pdoUsers?
     &groups=`Partners`
     &xtpl=`partnerTpl`
     &sortby=`id`
     &sortdir=`asc`
	&limit=`3`
	&where=`{"modUserProfile.extended.customvalue:=":"TypeA"}`
	&showLog=`1`
]]

Looks very nice but doesnt work.... Thats because of the fact that modUserProfile.extended is returned as a JSON type.
How to edit the where clause to select within these extended properties.

This question has been answered by Bruno17. See the first response.

Addict since 2012....

discuss.answer

5,888 Posts

Bruno17 Reply #2, 12 years, 3 months ago

you can not search (that easy) on extended fields.
If you need users searchable on custom-fields, you will need that values in seperate columns in a custom-table.
Bob's http://modx.com/extras/package/classextender should help to get that done.

Then it should be possible to use pdoUsers for filtering them on that fields with an additional leftjoin.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

128 Posts

Michael Engel Reply #3, 12 years, 3 months ago

I don`t know pdoUsers but other plugins using the where conditions on extended fields.

Extended Fields are saved as JSON and can`t accessed on the fields via MySQL. But you can access it as a string, with LIKE %%

But Remind, its slow somehow and should always be cached.


[[!pdoUsers?
     &groups=`Partners`
     &xtpl=`partnerTpl`
     &sortby=`id`
     &sortdir=`asc`
    &limit=`3`
    &where=`{"modUserProfile.extended:LIKE":"%\"customvalue\":\"TypeA\"%"}`
    &showLog=`1`
]]

101 Posts

mintnl Reply #4, 12 years, 3 months ago

Thanx for the swift replies, what a fantastic community!

@Michael, thanx, will try that!

@Bruno17, extended modUser thanx to BobRay's component, thank you for your pointer.
How would i go about updating these fields via a form? Right now i use the UpdateProfile snippet from the login-component to do that. It nicely saves all the user-items. But i extended the modUser with items like IBAN, VATnumber etc. and if i include that in my form the same way i do with items like [ [+ fullname] ] -placeholders nothing gets saved...
Any ideas?

<input type="text" name="fullname" id="fullname" value="[[+fullname]]" placeholder="Full name" >
<input type="text" name="iban" id="iban" value="[[+iban]]" placeholder="Iban-number">

Cheers,
Michiel

Addict since 2012....

5,888 Posts

Bruno17 Reply #5, 12 years, 3 months ago

there should be a snippet, called ExtUserUpdateProfile as an example

https://github.com/BobRay/ClassExtender/blob/master/core/components/classextender/elements/snippets/extuserupdateprofile.snippet.php

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

1,118 Posts

dubbs Reply #6, 11 years ago

Quote from: m.engel at Jul 09, 2014, 12:13 PM

I don`t know pdoUsers but other plugins using the where conditions on extended fields.

Extended Fields are saved as JSON and can`t accessed on the fields via MySQL. But you can access it as a string, with LIKE %%

But Remind, its slow somehow and should always be cached.
[[!pdoUsers?
     &groups=`Partners`
     &xtpl=`partnerTpl`
     &sortby=`id`
     &sortdir=`asc`
    &limit=`3`
    &where=`{"modUserProfile.extended:LIKE":"%\"customvalue\":\"TypeA\"%"}`
    &showLog=`1`
]]

Hi, Is it possible to pass more than one customvalue to the Where element of the code? e.g. to search more than one of the custom fields for the searched value. e.g.

&where=`{"modUserProfile.extended:LIKE":"%\"customField1,customField2\":\"TypeA\"%"}`

1,118 Posts

dubbs Reply #7, 11 years ago

I have this working to search 2 fields... but only 2 so far... any ideas on making it be able to get 100 fields to check for the value?

&where=`{
"modUserProfile.extended:LIKE":"%\"workregion_01\":\"london\"%",
"OR:modUserProfile.extended:LIKE":"%\"workregion_02\":\"london\"%"
}`

Any ideas???

24,544 Posts

BobRay Reply #8, 11 years ago

Just keep adding lines. End each line with a comma *except the last one*.

Now you see why we don't like using profile extended fields for searching. wink

If there will be no overlap in the place names, you could simplify it by leaving out the workregion:

&where=`{
   "modUserProfile.extended:LIKE":"%\"london\"%"
}`

That will get anyone who has london anywhere in the extended field. If there is an option like South London, though, this won't work.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

1,118 Posts

dubbs Reply #9, 11 years ago

Thanks Bob

Answered Filter on extended field with pdoUsers