Re: Guide to filters?

Jan Bolmeson wrote:
> I can’t really seem to figure out filters, especially the “with” part –
> could somebody explain how it relates to SQL JOIN? E.g. I am trying to use
> the GetRights() to return all the rights with their area_id changed to the
> area_define_name… I am about to write my own sqls since it would go 10 times
> as fast so I am pretty sure I am missing something here… =o|

The with part is essentially a way to have LiveUser_Admin to another 
get*() call per row in the result set.

But here goes a simple filter wtf? guide .. or better a generic guide to 
the parameters supported in the admin api ..

1) 'fields' (default: all fields from the first table in the 
"selectable_tables" property): this is an array containing the fields 
you want to fetch. the storage engine will attempt to automatically 
determine the most logical set of tables to join if necessary. this set 
of tables depends on the get*() method you called and can be affecred by 
the "selectable_tables" property. the order is relevant and therefore 
its not irrelevant which get*() method you call, even if the tables you 
are interested are listed in both "selectable_tables" arrays.

$params = array('fields' => array('foo', 'bar'));

2) 'with' (default: no with): this setting uses "withFieldMethodMap" 
property to determine what get*() method should be used for the given 
field. The specified field is implicitly added to the field list, if its 
not already listed there explicitly.

$params = array('with' => array('group_id'));
$LUadmin->perm->getUsers($param);

this will fetch all the users from the perm database and it will do a 
join with the groupusers table in order to fetch the group_id field. it 
will then do a getGroups() call using the group_id of each row as the 
only filter. the result will be stored into a key "groups".

3) 'filters' (default: no filters): this is essentially a key value pair 
with the field on which the filter is to be executed is the key and the 
value is the criterion to match against. the simplest way is to have a 
simple scalar value which uses simple "=" comparisions. If you pass a 
php null value it will do an "IS NULL" check. if the value is a simple 
array that does not have a key "value" in any position an "IN ([comma 
separated list of values])" check will be made. if the value does 
contain a key "value", than it will also expect an "op" key which 
specifies the comparision operatator (like "=", "<", "NOT IN" etc.). If 
the value of the "value" key is an array it will create a comma 
separated list, if not it will just quote the value. multiple filters 
will be AND'ed.

fields specified inside the filters will affect what tables are joined.

4) 'orders' (default: no order by): a simple array containing the names 
of fields by which the result should be ordered. fields specified inside 
the orders will affect what tables are joined.

5) 'select'  (default: 'all'): possible values are
'all' if you want a two dimensional array containing all rows from the 
result set.
'col': if you only want the first column of the result set as a single 
dimensional array
'row': if you only want the first row of the result set as a single 
dimensional array
'one': if you only want the first column of the first row of the result 
set as a scalar value

6) 'rekey' (default: false): determines if the first dimension of the 
result where 'select' was set to 'all' should be the first column of 
each row.

7) 'group' (default: false): determines if all of the values with the 
same value in the first column when using the rekey option should be 
grouped inside another array.

8) 'limit' (default: none): determines if the number of rows in the 
result set should be limited by the given amount

9) 'offset' (default: none): determines if the given number of rows 
should be skipped at the beginning of the result set

hope this helps ..
look at the example 1 in the liveuser admin package to see some sample 
calls.

regards,
Lukas Smith
[email protected]
_______________________________
   BackendMedia
   www.backendmedia.com
   [email protected]

   Linn Zwoch Smith GbR
   Pariser Str. 44
   D-10707 Berlin

   Tel +49 30 83 22 50 00
   Fax +49 30 83 22 50 07