2

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

6
  • 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.
    – Coleman
    Nov 2, 2023 at 11:59
  • Also for faster help try chat.civicrm.org/civicrm/channels/search-improvements
    – Coleman
    Nov 2, 2023 at 12:00
  • I wish the debug would work but it did not reveal the issue. Nov 2, 2023 at 14:28
  • 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? Nov 2, 2023 at 14:31
  • No SearchKit doesn't require it to be an integer. Ensure your user has "view debug output" permission.
    – Coleman
    Nov 2, 2023 at 15:13

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.