CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
Pages: [1]

Author Topic: Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working  (Read 1698 times)

oreo_masta

  • I’m new here
  • *
  • Posts: 4
  • Karma: 2
  • CiviCRM version: 4.3.2
  • CMS version: Wordpress 3.5.1
  • MySQL version: 5.5.30
  • PHP version: 5.3.24
Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
May 09, 2013, 09:14:16 am
I just completed an upgrade to CiviCRM 4.3.2 (from 4.2.9) and am very excited about the improvements to Search Builder. I've run into one issue when trying to incorporate queries involving Membership Type and Membership Status. I know simplistic queries can be done using Memberships > Find Members. However, I'm looking to include memberships into more complex Search Builder queries. I've simplified the queries to illustrate my point for this post.

The following query works (ss1.png I've blacked out names):

Contacts = Volunteer AND Group Status - "Added" ...AND...
Membership Type IS NOT NULL

There are other membership types, but you can see that Basic Membership definitely has matching contacts when Membership Type IS NOT NULL is used.

However, when I try to explicitly use Membership Type = Basic Membership, the Search Builder yields no results (ss2.png):

Contacts = Volunteer AND Group Status - "Added" ...AND...
Membership Type = 1

Is this a bug? Can others confirm? I'll report if so. If others cannot reproduce, does anyone have an idea of what could be going wrong? Or am I misunderstanding that query restriction criteria in Search Builder? Thanks for any help, cheers!

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
May 09, 2013, 09:54:56 am
Looks like a bug to me. Anyone know if membership type is a multivalued field? Could it be getting tripped up by the VALUE_SEPARATOR characters?
Try asking your question on the new CiviCRM help site.

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
May 09, 2013, 12:47:00 pm
Membership Type is not multi-valued - civicrm_membership.membership_type_id is an INTEGER column / foreign key to the civicrm_membership_type table. oreo_masta - would be good if you could enable query logging and dump the two different queries being created in your two cases.
Protect your investment in CiviCRM by  becoming a Member!

oreo_masta

  • I’m new here
  • *
  • Posts: 4
  • Karma: 2
  • CiviCRM version: 4.3.2
  • CMS version: Wordpress 3.5.1
  • MySQL version: 5.5.30
  • PHP version: 5.3.24
Re: Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
May 09, 2013, 08:29:49 pm
Good call on debugging. I cleared the cache and deleted the log file before running each query. Then I checked the log after running the query. Still quite a lot to look through but I think I found the pertenant lines. I inserted line breaks for readability.

Case 1: Contacts = Volunteer AND Group Status - "Added" ...AND... Membership Type IS NOT NULL
May 10 03:07:23  [info]
$Query = string(740) "
SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name 
FROM civicrm_contact contact_a 
LEFT JOIN civicrm_group_contact `civicrm_group_contact-1` ON contact_a.id = `civicrm_group_contact-1`.contact_id   
LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id 
LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )
LEFT JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id 
WHERE  (  ( `civicrm_group_contact-1`.group_id = ( 1 )
AND `civicrm_group_contact-1`.status IN ("Added")
AND LOWER(civicrm_membership_type.name) IS NOT NULL )  ) 
AND (contact_a.is_deleted = 0)   
ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc "


Case 2: Contacts = Volunteer AND Group Status - "Added" ...AND... Membership Type = 1
May 10 03:19:45  [info]
$Query = string(727) "
SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name 
FROM civicrm_contact contact_a 
LEFT JOIN civicrm_group_contact `civicrm_group_contact-1` ON contact_a.id = `civicrm_group_contact-1`.contact_id   
LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id 
LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )
LEFT JOIN civicrm_membership_type ON civicrm_membership.membership_type_id = civicrm_membership_type.id 
WHERE  (  ( `civicrm_group_contact-1`.group_id = ( 1 )
AND `civicrm_group_contact-1`.status IN ("Added")
AND civicrm_membership_type.name = '1' )  ) 
AND (contact_a.is_deleted = 0)   
ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc "

I think you've done it. I highlighted what I think is the error in Case 2. civicrm_membership_type.name column is varchar(128). It should be either matching civicrm_membership_type.name = 'Basic Member' or civicrm_membership_type.id = 1. Is this enough triage to report it to JIRA or do we need more?

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
May 09, 2013, 09:56:56 pm
Yes please do.
Try asking your question on the new CiviCRM help site.

oreo_masta

  • I’m new here
  • *
  • Posts: 4
  • Karma: 2
  • CiviCRM version: 4.3.2
  • CMS version: Wordpress 3.5.1
  • MySQL version: 5.5.30
  • PHP version: 5.3.24
Re: Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working
May 10, 2013, 09:50:01 am
In JIRA now. Thanks for the help!
http://issues.civicrm.org/jira/browse/CRM-12588
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Query by Memership Type using improved Search Builder in CiviCRM 4.3 not working

This forum was archived on 2017-11-26.