Answers.Body: <p>The solution is that after updating your schema xml files, you need to run the command <code>civix generate:entity-boilerplate</code>. This will update your generated DAO files so that SearchKit can use the metadata.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-05T16:30:52.443Z
Answers.LastActivityDate: 2023-11-05T16:30:52.443Z
Answers.UserDisplayName: Coleman
Body: <p>I asked a question about CSV downloads and was suggested to look at SearchKit to make this work.
  I have a SQL command that works, but just lists the contents.</p>
  <pre><code>select civicrm_registered_dogs.registered_name as DogName, civicrm_dog_scores.Event as Event, civicrm_trial_levels.description as LevelDescript,  civicrm_dog_scores.Stream as Stream, civicrm_dog_scores.Score as Score, civicrm_trial_levels.MaxPoints as MaxPoints, civicrm_dog_scores.Pass from civicrm_dog_scores
  inner join civicrm_registered_dogs ON civicrm_dog_scores.DogId = civicrm_registered_dogs.id
  inner join civicrm_contact ON civicrm_contact.id = civicrm_registered_dogs.contact_id
  inner join civicrm_trial_levels ON civicrm_dog_scores.Rules = civicrm_trial_levels.Id
  where DogId=&quot;.$id.&quot; ORDER by Event&quot;`
  </code></pre>
  <p>First thing I did was make sure the custom table definitions were correct and the foreign keys were correct.  I think I have it all correct.</p>
  <blockquote>
  <pre><code>&lt;field&gt;
  &lt;name&gt;Rules&lt;/name&gt;
  &lt;type&gt;varchar&lt;/type&gt;
  &lt;length&gt;5&lt;/length&gt;
  &lt;comment&gt;FK to Trial Levels&lt;/comment&gt;   &lt;/field&gt;   &lt;primaryKey&gt;
  &lt;name&gt;Rules&lt;/name&gt;
  &lt;autoincrement&gt;false&lt;/autoincrement&gt;   &lt;/primaryKey&gt;   &lt;foreignKey&gt;
  &lt;name&gt;Rules&lt;/name&gt;
  &lt;table&gt;civicrm_trial_levels&lt;/table&gt;
  &lt;key&gt;Id&lt;/key&gt;   &lt;/foreignKey&gt;   &lt;field&gt;
  &lt;name&gt;DogId&lt;/name&gt;
  &lt;type&gt;int unsigned&lt;/type&gt;
  &lt;comment&gt;FK to Registered Dogs&lt;/comment&gt;   &lt;/field&gt;   &lt;primaryKey&gt;
  &lt;name&gt;DogId&lt;/name&gt;
  &lt;autoincrement&gt;false&lt;/autoincrement&gt;   &lt;/primaryKey&gt;    &lt;foreignKey&gt;
  &lt;name&gt;DogId&lt;/name&gt;
  &lt;table&gt;civicrm_registered_dogs&lt;/table&gt;
  &lt;key&gt;id&lt;/key&gt;   &lt;/foreignKey&gt;
  </code></pre>
  </blockquote>
  <p>Above is the scores table segment that defines the Rules field and the foreign key to the trial_levels table.  I included the DogId for comparison.  The trial levels look like this:</p>
  <pre><code>&lt;table&gt;
    &lt;base&gt;CRM/Dogregistry&lt;/base&gt;
    &lt;class&gt;TrialLevels&lt;/class&gt;
    &lt;name&gt;civicrm_trial_levels&lt;/name&gt;
    &lt;comment&gt;FIXME&lt;/comment&gt;
    &lt;log&gt;true&lt;/log&gt;
    &lt;field&gt;
      &lt;name&gt;Id&lt;/name&gt;
      &lt;type&gt;varchar&lt;/type&gt;
      &lt;length&gt;5&lt;/length&gt;
      &lt;required&gt;true&lt;/required&gt;
      &lt;comment&gt;Unique TrialLevels ID&lt;/comment&gt;
      &lt;html&gt;
        &lt;type&gt;Number&lt;/type&gt;
      &lt;/html&gt;
    &lt;/field&gt;
    &lt;primaryKey&gt;
      &lt;name&gt;Id&lt;/name&gt;
      &lt;autoincrement&gt;false&lt;/autoincrement&gt;
    &lt;/primaryKey&gt;
    &lt;field&gt;
      &lt;name&gt;Description&lt;/name&gt;
      &lt;type&gt;varchar&lt;/type&gt;
      &lt;length&gt;50&lt;/length&gt;
      &lt;comment&gt;Description&lt;/comment&gt;
    &lt;/field&gt;
    &lt;field&gt;
      &lt;name&gt;Level&lt;/name&gt;
      &lt;type&gt;INT&lt;/type&gt;
      &lt;length&gt;6&lt;/length&gt;
      &lt;comment&gt;Level&lt;/comment&gt;
    &lt;/field&gt;
    &lt;field&gt;
      &lt;name&gt;MaxPoints&lt;/name&gt;
      &lt;type&gt;INT&lt;/type&gt;
      &lt;length&gt;6&lt;/length&gt;
      &lt;comment&gt;Maximum points&lt;/comment&gt;
    &lt;/field&gt;
    &lt;field&gt;
      &lt;name&gt;PassPoints&lt;/name&gt;
      &lt;type&gt;INT&lt;/type&gt;
      &lt;length&gt;6&lt;/length&gt;
      &lt;comment&gt;Pass points&lt;/comment&gt;
    &lt;/field&gt;
    &lt;field&gt;
      &lt;name&gt;Weight&lt;/name&gt;
      &lt;type&gt;INT&lt;/type&gt;
      &lt;length&gt;6&lt;/length&gt;
      &lt;comment&gt;Weight&lt;/comment&gt;
    &lt;/field&gt;
  
  &lt;/table&gt;
  </code></pre>
  <p>In SearchKit, I can create a search for DogScores, select the join to registered_dogs and I get results.
  The Output from an export of the search:</p>
  <pre><code>[
    [
      &quot;SavedSearch&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;Dog_Scores&quot;,
            &quot;label&quot;: &quot;Dog Scores&quot;,
            &quot;form_values&quot;: null,
            &quot;mapping_id&quot;: null,
            &quot;search_custom_id&quot;: null,
            &quot;api_entity&quot;: &quot;DogScores&quot;,
            &quot;api_params&quot;: {
              &quot;version&quot;: 4,
              &quot;select&quot;: [
                &quot;id&quot;
              ],
              &quot;orderBy&quot;: [],
              &quot;where&quot;: [],
              &quot;groupBy&quot;: [],
              &quot;join&quot;: [
                [
                  &quot;RegisteredDogs AS DogScores_RegisteredDogs_DogId_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;DogId&quot;,
                    &quot;=&quot;,
                    &quot;DogScores_RegisteredDogs_DogId_01.id&quot;
                  ]
                ]
              ],
              &quot;having&quot;: []
            },
            &quot;expires_date&quot;: null,
            &quot;description&quot;: null
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;
        ]
      }
    ]
  ]
  </code></pre>
  <p>As soon as I add the Trial Levels table I no longer get any output and under the QueryInfo block it no longer shows the SQL Like it did in the working search.  Below is the NON-working search:</p>
  <pre><code>[
    [
      &quot;SavedSearch&quot;,
      &quot;save&quot;,
      {
        &quot;records&quot;: [
          {
            &quot;name&quot;: &quot;Dog_Scores&quot;,
            &quot;label&quot;: &quot;Dog Scores&quot;,
            &quot;form_values&quot;: null,
            &quot;mapping_id&quot;: null,
            &quot;search_custom_id&quot;: null,
            &quot;api_entity&quot;: &quot;DogScores&quot;,
            &quot;api_params&quot;: {
              &quot;version&quot;: 4,
              &quot;select&quot;: [
                &quot;id&quot;
              ],
              &quot;orderBy&quot;: [],
              &quot;where&quot;: [],
              &quot;groupBy&quot;: [],
              &quot;join&quot;: [
                [
                  &quot;RegisteredDogs AS DogScores_RegisteredDogs_DogId_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;DogId&quot;,
                    &quot;=&quot;,
                    &quot;DogScores_RegisteredDogs_DogId_01.id&quot;
                  ]
                ],
                [
                  &quot;TrialLevels AS DogScores_TrialLevels_Rules_01&quot;,
                  &quot;LEFT&quot;,
                  [
                    &quot;Rules&quot;,
                    &quot;=&quot;,
                    &quot;DogScores_TrialLevels_Rules_01.Id&quot;
                  ]
                ]
              ],
              &quot;having&quot;: []
            },
            &quot;expires_date&quot;: null,
            &quot;description&quot;: null
          }
        ],
        &quot;match&quot;: [
          &quot;name&quot;
        ]
      }
    ]
  ]
  </code></pre>
  <p>I'm not sure why its doing what it is - any pointers would be very welcome!  I know it is a long post but wasn't sure where to even start to figure out why - there didn't seem to be any logs providing errors or anything.</p>
  <p>TIA</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-02T11:59:03.947Z
Comments.Text: You're going down the right path! Adding correct metadata will benefit you in the long-term, not just for this one scenario. It seems you're getting a fatal SQL error that probably isn't being shown because your site is suppressing error messages. I recommend you turn on debugging on your site to see if you can get at the error.
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-02T12:00:24.087Z
Comments.Text: Also for faster help try https://chat.civicrm.org/civicrm/channels/search-improvements
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-02T14:28:17.323Z
Comments.Text: I wish the debug would work but it did not reveal the issue.
Comments.UserDisplayName: Norm Dressler
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-02T14:31:13.263Z
Comments.Text: I thought next step might be to completely uninstall my extension - which is a pain because I have a lot of data in other custom tables for this extension.  I uninstalled it, which deleted all extension tables, installed it again, which created all the extension tables from the schema.  I reloaded the data from extracts and still nothing.  The only difference I see from this table and other foreign key tables is the primary key in this case is alphanumeric and not integer.  Is there some assumptions happening in SearchKit that assumes its integer?
Comments.UserDisplayName: Norm Dressler
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-02T15:13:00.467Z
Comments.Text: No SearchKit doesn't require it to be an integer. Ensure your user has "view debug output" permission.
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-05T20:55:06.220Z
Comments.Text: minor gripe - but would help if your Question Title could be more specific to help others searching for similar
Comments.UserDisplayName: petednz - fuzion
Tags: <extension><searchkit><formbuilder><custom>
Title: SearchKit - Can't figure this out - works until I add a second join
ThreadText: # SearchKit - Can't figure this out - works until I add a second join
  
  (Asked by: username_0 on 2023-11-01)
  
  I asked a question about CSV downloads and was suggested to look at SearchKit to make this work.
  I have a SQL command that works, but just lists the contents.
  
  ```
  select civicrm_registered_dogs.registered_name as DogName, civicrm_dog_scores.Event as Event, civicrm_trial_levels.description as LevelDescript,  civicrm_dog_scores.Stream as Stream, civicrm_dog_scores.Score as Score, civicrm_trial_levels.MaxPoints as MaxPoints, civicrm_dog_scores.Pass from civicrm_dog_scores
  inner join civicrm_registered_dogs ON civicrm_dog_scores.DogId = civicrm_registered_dogs.id
  inner join civicrm_contact ON civicrm_contact.id = civicrm_registered_dogs.contact_id
  inner join civicrm_trial_levels ON civicrm_dog_scores.Rules = civicrm_trial_levels.Id
  where DogId=".$id." ORDER by Event"`
  
  ```
  
  First thing I did was make sure the custom table definitions were correct and the foreign keys were correct. I think I have it all correct.
  
  > ```
  > <field>
  > <name>Rules</name>
  > <type>varchar</type>
  > <length>5</length>
  > <comment>FK to Trial Levels</comment>   </field>   <primaryKey>
  > <name>Rules</name>
  > <autoincrement>false</autoincrement>   </primaryKey>   <foreignKey>
  > <name>Rules</name>
  > <table>civicrm_trial_levels</table>
  > <key>Id</key>   </foreignKey>   <field>
  > <name>DogId</name>
  > <type>int unsigned</type>
  > <comment>FK to Registered Dogs</comment>   </field>   <primaryKey>
  > <name>DogId</name>
  > <autoincrement>false</autoincrement>   </primaryKey>    <foreignKey>
  > <name>DogId</name>
  > <table>civicrm_registered_dogs</table>
  > <key>id</key>   </foreignKey>
  >
  > ```
  
  Above is the scores table segment that defines the Rules field and the foreign key to the trial\_levels table. I included the DogId for comparison. The trial levels look like this:
  
  ```
  <table>
    <base>CRM/Dogregistry</base>
    <class>TrialLevels</class>
    <name>civicrm_trial_levels</name>
    <comment>FIXME</comment>
    <log>true</log>
    <field>
      <name>Id</name>
      <type>varchar</type>
      <length>5</length>
      <required>true</required>
      <comment>Unique TrialLevels ID</comment>
      <html>
        <type>Number</type>
      </html>
    </field>
    <primaryKey>
      <name>Id</name>
      <autoincrement>false</autoincrement>
    </primaryKey>
    <field>
      <name>Description</name>
      <type>varchar</type>
      <length>50</length>
      <comment>Description</comment>
    </field>
    <field>
      <name>Level</name>
      <type>INT</type>
      <length>6</length>
      <comment>Level</comment>
    </field>
    <field>
      <name>MaxPoints</name>
      <type>INT</type>
      <length>6</length>
      <comment>Maximum points</comment>
    </field>
    <field>
      <name>PassPoints</name>
      <type>INT</type>
      <length>6</length>
      <comment>Pass points</comment>
    </field>
    <field>
      <name>Weight</name>
      <type>INT</type>
      <length>6</length>
      <comment>Weight</comment>
    </field>
  
  </table>
  
  ```
  
  In SearchKit, I can create a search for DogScores, select the join to registered\_dogs and I get results.
  The Output from an export of the search:
  
  ```
  [
    [
      "SavedSearch",
      "save",
      {
        "records": [
          {
            "name": "Dog_Scores",
            "label": "Dog Scores",
            "form_values": null,
            "mapping_id": null,
            "search_custom_id": null,
            "api_entity": "DogScores",
            "api_params": {
              "version": 4,
              "select": [
                "id"
              ],
              "orderBy": [],
              "where": [],
              "groupBy": [],
              "join": [
                [
                  "RegisteredDogs AS DogScores_RegisteredDogs_DogId_01",
                  "LEFT",
                  [
                    "DogId",
                    "=",
                    "DogScores_RegisteredDogs_DogId_01.id"
                  ]
                ]
              ],
              "having": []
            },
            "expires_date": null,
            "description": null
          }
        ],
        "match": [
          "name"
        ]
      }
    ]
  ]
  
  ```
  
  As soon as I add the Trial Levels table I no longer get any output and under the QueryInfo block it no longer shows the SQL Like it did in the working search. Below is the NON-working search:
  
  ```
  [
    [
      "SavedSearch",
      "save",
      {
        "records": [
          {
            "name": "Dog_Scores",
            "label": "Dog Scores",
            "form_values": null,
            "mapping_id": null,
            "search_custom_id": null,
            "api_entity": "DogScores",
            "api_params": {
              "version": 4,
              "select": [
                "id"
              ],
              "orderBy": [],
              "where": [],
              "groupBy": [],
              "join": [
                [
                  "RegisteredDogs AS DogScores_RegisteredDogs_DogId_01",
                  "LEFT",
                  [
                    "DogId",
                    "=",
                    "DogScores_RegisteredDogs_DogId_01.id"
                  ]
                ],
                [
                  "TrialLevels AS DogScores_TrialLevels_Rules_01",
                  "LEFT",
                  [
                    "Rules",
                    "=",
                    "DogScores_TrialLevels_Rules_01.Id"
                  ]
                ]
              ],
              "having": []
            },
            "expires_date": null,
            "description": null
          }
        ],
        "match": [
          "name"
        ]
      }
    ]
  ]
  
  ```
  
  I'm not sure why its doing what it is - any pointers would be very welcome! I know it is a long post but wasn't sure where to even start to figure out why - there didn't seem to be any logs providing errors or anything.
  
  TIA
  
  **Tags:** extension, searchkit, formbuilder, custom
  
  > **Comments:**
  > **username_1**: You're going down the right path! Adding correct metadata will benefit you in the long-term, not just for this one scenario. It seems you're getting a fatal SQL error that probably isn't being shown because your site is suppressing error messages. I recommend you turn on debugging on your site to see if you can get at the error.  
  > **username_1**: Also for faster help try https://chat.civicrm.org/civicrm/channels/search-improvements  
  > **username_0**: I wish the debug would work but it did not reveal the issue.  
  > **username_0**: I thought next step might be to completely uninstall my extension - which is a pain because I have a lot of data in other custom tables for this extension.  I uninstalled it, which deleted all extension tables, installed it again, which created all the extension tables from the schema.  I reloaded the data from extracts and still nothing.  The only difference I see from this table and other foreign key tables is the primary key in this case is alphanumeric and not integer.  Is there some assumptions happening in SearchKit that assumes its integer?  
  > **username_1**: No SearchKit doesn't require it to be an integer. Ensure your user has "view debug output" permission.  
  > **username_2**: minor gripe - but would help if your Question Title could be more specific to help others searching for similar  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-11-05. Score: 1
  
  The solution is that after updating your schema xml files, you need to run the command `civix generate:entity-boilerplate`. This will update your generated DAO files so that SearchKit can use the metadata.
  
  
  ---
  
  