Answers.Body: <p>You could use Contribution -&gt; Left Join line items where entity_table = civicrm_membership</p>
  <p>Where contribution FT= ABC and line_item.id IS NULL</p>
  <p><a href="https://i.sstatic.net/S2peS.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/S2peS.png" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-13T22:56:22.653Z
Answers.Text: 1. Are you saying I can add table joins in searchkit even if they're not listed or directly supported?
  2. What do you mean by 'add a MembershipPayment file in Civi/API4' - are you talking about a custom extension? Or a PR?
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-14T16:14:07.363Z
Answers.Text: I think core won't accept PR for MembershipPayment API4 as there was a discussion in past about deprecating the civicrm_membership_payment and participant_payment table over line_item. Going forward it will be via line item. You could implement that using custom extension. Or you could use line item table to joining to contribution
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-15T14:56:58.550Z
Answers.Text: Yes, this is now almost the right answer. If you remove the 'add a file' suggestion and just have the picture and description of using the line item, then I'll accept as correct.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-15T14:59:24.237Z
Answers.Text: Done, i have updated my answer
Answers.UserDisplayName: Pradeep Nayak
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-13T18:56:16.510Z
Answers.LastActivityDate: 2024-02-15T14:59:08.207Z
Answers.LastEditDate: 2024-02-15T14:59:08.207Z
Answers.UserDisplayName: Pradeep Nayak
Answers.Body: <p>It looks like the API was missing to allow Contributions to join with Memberships.
  Here is <a href="https://github.com/civicrm/civicrm-core/pull/29388" rel="nofollow noreferrer">a PR to add it</a>, which allows you to do this in SearchKit:</p>
  <p><a href="https://i.sstatic.net/klwgz.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/klwgz.png" alt="enter image description here" /></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-14T14:58:32.033Z
Answers.Text: Confirmed, that solves my need, thanks. I'd say that's a good sign for searchkit that it was that simple a PR.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-14T15:55:06.040Z
Answers.Text: Unfortunately that PR was rejected because the `civicrm_contribution_membership` table is being (sloooowly) phased out.
Answers.UserDisplayName: Coleman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-14T16:08:14.343Z
Answers.Text: But fortunately, matt's comment set me on the one true path of enlightenment.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-14T13:59:28.357Z
Answers.LastActivityDate: 2024-02-14T13:59:28.357Z
Answers.UserDisplayName: Coleman
Answers.Body: <p>As per @mattwire's comment in @Coleman's PR, the Right Way to identify a membership payment is via the line item, when the entity_table is &quot;civicrm_membership&quot; and then the membership is the entity_id.</p>
  <p>i.e. for my purposes: join the contribution table to the line items, and find contributions that have no civicrm_membership entries.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-02-14T16:07:19.740Z
Answers.LastActivityDate: 2024-02-14T16:07:19.740Z
Answers.UserDisplayName: Alan Dixon
Body: <p>I want to create a search using searchkit that identifies contributions (payments) with memberships, which are connected using the civicrm_membership_payment table.</p>
  <p>I actually want to list the contributions of a type Membership Payment that are NOT connected to a membership.</p>
  
Tags: <civicontribute><civimember><searchkit>
Title: How can I use the membership payment table in searchkit?
ThreadText: # How can I use the membership payment table in searchkit?
  
  (Asked by: username_0 on 2024-02-13)
  
  I want to create a search using searchkit that identifies contributions (payments) with memberships, which are connected using the civicrm\_membership\_payment table.
  
  I actually want to list the contributions of a type Membership Payment that are NOT connected to a membership.
  
  **Tags:** civicontribute, civimember, searchkit
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2024-02-13. Score: 2 | Accepted answer
  
  You could use Contribution -> Left Join line items where entity\_table = civicrm\_membership
  
  Where contribution FT= ABC and line\_item.id IS NULL
  
  [![enter image description here](https://i.sstatic.net/S2peS.png)](https://i.sstatic.net/S2peS.png)
  
  > **Comments:**
  > **username_0**: 1. Are you saying I can add table joins in searchkit even if they're not listed or directly supported? 2. What do you mean by 'add a MembershipPayment file in Civi/API4' - are you talking about a custom extension? Or a PR?  
  > **username_1**: I think core won't accept PR for MembershipPayment API4 as there was a discussion in past about deprecating the civicrm_membership_payment and participant_payment table over line_item. Going forward it will be via line item. You could implement that using custom extension. Or you could use line item table to joining to contribution  
  > **username_0**: Yes, this is now almost the right answer. If you remove the 'add a file' suggestion and just have the picture and description of using the line item, then I'll accept as correct.  
  > **username_1**: Done, i have updated my answer  
  
  ---
  
  ### Answer by username_2 on 2024-02-14. Score: 2
  
  It looks like the API was missing to allow Contributions to join with Memberships.
  Here is [a PR to add it](https://github.com/civicrm/civicrm-core/pull/29388), which allows you to do this in SearchKit:
  
  [![enter image description here](https://i.sstatic.net/klwgz.png)](https://i.sstatic.net/klwgz.png)
  
  > **Comments:**
  > **username_0**: Confirmed, that solves my need, thanks. I'd say that's a good sign for searchkit that it was that simple a PR.  
  > **username_2**: Unfortunately that PR was rejected because the `civicrm_contribution_membership` table is being (sloooowly) phased out.  
  > **username_0**: But fortunately, matt's comment set me on the one true path of enlightenment.  
  
  ---
  
  ### Answer by username_0 on 2024-02-14. Score: 1
  
  As per @mattwire's comment in @username_2's PR, the Right Way to identify a membership payment is via the line item, when the entity\_table is "civicrm\_membership" and then the membership is the entity\_id.
  
  i.e. for my purposes: join the contribution table to the line items, and find contributions that have no civicrm\_membership entries.
  
  
  ---
  
  