Don't know if you can do this in on query, but you can write an aggregate that collects results from two and merges the results all back into one collection.
See https://github.com/Jasig/person-directory#attribute-aggregation From: Michael Overmeyer [mailto:m.overme...@yahoo.ca] Sent: Friday, June 26, 2015 8:16 AM To: cas-user@lists.jasig.org Cc: festi...@gmail.com Subject: Re:[cas-user] Cas 4 - sql authentication with username or email and retrieve person attribute I have also hit this problem, albeit for a slightly different query. I wish to use the username in my query in multiple places. For example, part of my results are generated from a subquery: SELECT attribute1, attribute2, attribute3, array(SELECT attribute4 FROM table2 t2 WHERE {0}) FROM table1 t1 WHERE {0}; At first I tried <property name="queryAttributeMapping"> <map> <entry key="username" value="username" /> <entry key="username" value="username" /> </map> </property> But of course this is a map ("duh"), so the second value replaces the first. The good news is that (In my case, because the column names are the same) it generates the correct SQL SELECT attribute1, attribute2, attribute3, array(SELECT attribute4 FROM table2 t2 WHERE username = ?) FROM table1 t1 WHERE username = ?; but when it actually executes the SQL, it only has one entry in the map, so it only adds the username value once and the database complains that it doesn't have a value for the second parameter. I'm not sure what the best way to work around this is. My current plan is to attempt to modify the source to do something like add a duplicate username parameter to the "seed map" for the user so I can do things like <property name="queryAttributeMapping"> <map> <entry key="username" value="username" /> <entry key="username2" value="username" /> </map> </property> But this is hacky and would result in code that is specific to my installation and schema. Please let me know if there is a better way that I could adopt to work around this problem. On Thursday, 9 October 2014 09:43:46 UTC-4, Jonathan Luthi wrote: Hi, I'm trying to configure my CAS Server 4 to allow users to authenticate with username or email within a mysql database (using JDBC). So, in my primaryAuthenticationHandler I set the sql like this: SELECT password FROM users WHERE LOWER(?) IN (LOWER(username), LOWER(email)) AND NOT deleted It works, but my problem is that I'm using a SingleRowJdbcPersonAttributeDao to retrieve user attributes. Something like this: ... <constructor-arg index="1" value="SELECT ... FROM users WHERE {0}" /> <property name="queryAttributeMapping"> <map> <entry key="username" value="email" /> </map> </property> ... Attributes are retrieved only when user log in with email (because query is translated as email=username). So, I wonder if it's possible to have a query like this "...email=username OR username=username". I really want to have login possible by username or email on my website. Best regards, Jonathan -- You are currently subscribed to cas-user@lists.jasig.org <mailto:cas-user@lists.jasig.org> as: mmoay...@unicon.net <mailto:mmoay...@unicon.net> To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/cas-user -- You are currently subscribed to cas-user@lists.jasig.org as: arch...@mail-archive.com To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/cas-user
