CiviCRM Community Forums (archive)

Hi all,

Yesterday I created a smart group on my test CiviCRM-instance, that returns every contact that is contact_a in a parent relationship. (I called this smart group 'parents').

It seems that the civicrm_group database table references civicrm_saved_search. This was what ended up in the relevant saved search record:

MariaDB [civi]> select * from civicrm_saved_search where id=2;
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | form_values                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | mapping_id | search_custom_id | where_clause                                                                                                                                                                                                                                                                                                            | select_tables                                                                                                                                                                                                                                                                                                                                                                                                 | where_tables                                                                                                                                                                                                                                     |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  2 | a:84:{s:5:"qfKey";s:37:"4b50e233dcbe77cced4bd8fd8df90567_1384";s:8:"entryURL";s:64:"http://localhost/vanilla/civicrm/contact/search/advanced?reset=1";s:12:"hidden_basic";s:1:"1";s:12:"contact_type";a:0:{}s:5:"group";a:0:{}s:10:"group_type";a:0:{}s:21:"group_search_selected";s:5:"group";s:12:"contact_tags";a:0:{}s:9:"sort_name";s:0:"";s:5:"email";s:0:"";s:14:"contact_source";s:0:"";s:9:"job_title";s:0:"";s:10:"contact_id";s:0:"";s:19:"external_identifier";s:0:"";s:7:"uf_user";s:0:"";s:10:"tag_search";s:0:"";s:11:"uf_group_id";s:0:"";s:14:"component_mode";s:1:"1";s:8:"operator";s:3:"AND";s:25:"display_relationship_type";s:0:"";s:15:"privacy_options";a:0:{}s:16:"privacy_operator";s:2:"OR";s:14:"privacy_toggle";s:1:"1";s:13:"email_on_hold";a:1:{s:7:"on_hold";s:0:"";}s:30:"preferred_communication_method";a:5:{i:1;s:0:"";i:2;s:0:"";i:3;s:0:"";i:4;s:0:"";i:5;s:0:"";}s:18:"preferred_language";s:0:"";s:13:"phone_numeric";s:0:"";s:22:"phone_location_type_id";s:0:"";s:19:"phone_phone_type_id";s:0:"";s:19:"hidden_relationship";s:1:"1";s:16:"relation_type_id";s:5:"1_b_a";s:20:"relation_target_name";s:0:"";s:15:"relation_status";s:1:"0";s:19:"relation_permission";s:1:"0";s:21:"relation_target_group";a:0:{}s:28:"relation_start_date_relative";s:0:"";s:23:"relation_start_date_low";s:0:"";s:24:"relation_start_date_high";s:0:"";s:26:"relation_end_date_relative";s:0:"";s:21:"relation_end_date_low";s:0:"";s:22:"relation_end_date_high";s:0:"";s:4:"task";s:2:"13";s:8:"radio_ts";s:6:"ts_sel";s:12:"toggleSelect";s:1:"1";s:10:"mark_x_181";s:1:"1";s:9:"mark_x_79";s:1:"1";s:9:"mark_x_20";s:1:"1";s:9:"mark_x_77";s:1:"1";s:10:"mark_x_102";s:1:"1";s:9:"mark_x_38";s:1:"1";s:9:"mark_x_22";s:1:"1";s:10:"mark_x_171";s:1:"1";s:10:"mark_x_136";s:1:"1";s:10:"mark_x_190";s:1:"1";s:9:"mark_x_16";s:1:"1";s:10:"mark_x_123";s:1:"1";s:9:"mark_x_31";s:1:"1";s:9:"mark_x_27";s:1:"1";s:9:"mark_x_67";s:1:"1";s:10:"mark_x_176";s:1:"1";s:10:"mark_x_149";s:1:"1";s:9:"mark_x_58";s:1:"1";s:10:"mark_x_186";s:1:"1";s:10:"mark_x_177";s:1:"1";s:9:"mark_x_50";s:1:"1";s:9:"mark_x_94";s:1:"1";s:9:"mark_x_64";s:1:"1";s:8:"mark_x_4";s:1:"1";s:10:"mark_x_110";s:1:"1";s:9:"mark_x_46";s:1:"1";s:10:"mark_x_180";s:1:"1";s:9:"mark_x_65";s:1:"1";s:10:"mark_x_106";s:1:"1";s:10:"mark_x_183";s:1:"1";s:10:"mark_x_109";s:1:"1";s:9:"mark_x_41";s:1:"1";s:9:"mark_x_99";s:1:"1";s:9:"mark_x_19";s:1:"1";s:10:"mark_x_121";s:1:"1";s:10:"mark_x_188";s:1:"1";s:10:"mark_x_195";s:1:"1";s:9:"mark_x_17";s:1:"1";s:10:"mark_x_185";s:1:"1";s:9:"mark_x_57";s:1:"1";} |       NULL |             NULL |  ( (
civicrm_relationship.is_active = 1 AND
( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= 20150616 ) AND
( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= 20150616 )
) AND (contact_b.is_deleted = 0) AND civicrm_relationship.relationship_type_id = 1 )  | a:8:{s:15:"civicrm_contact";i:1;s:15:"civicrm_address";i:1;s:15:"civicrm_country";i:1;s:13:"civicrm_email";i:1;s:13:"civicrm_phone";i:1;s:10:"civicrm_im";i:1;s:19:"civicrm_worldregion";i:1;s:20:"civicrm_relationship";i:1;}                                                                                                                                                                                | a:2:{s:15:"civicrm_contact";i:1;s:20:"civicrm_relationship";i:1;}                                                                                                                                                                                |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
In the 'where_clause' column, this particular part catched my attention:

civicrm_relationship.is_active = 1 AND
( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= 20150616 ) AND
( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= 20150616 )
So I was worried that my smart group would not work for parent relations created after 2015-06-16 (yesterday).

I tried this today: I created a new parent relation, with start_date today (2015-06-17). And guess what: the new parent did show up in my smart group.

So I guess that the members in the smart group are determined using the form_values field in civicrm_saved_search, and not the where_clause.

Is this the case? Is the form_values field the important one? Is where_clause used somewhere in civicrm?

Thank you for looking at this.
Johan