SQLSTATE[42S02]: Base table or view not found error when used with CiviCRM entities and separate CiviCRM database

My guess is you're going to have problems with all sorts of queries if you're trying to join/union between separate DB's.

Drupal technically can work this way, but only if the DB's are on the same server. Most managed hosting these days the DB server is different between DB's which means join/union is not natively supported without FEDERATED storage.

Open to MR's here to solve, but just thought I'd point that out

Hi Bryan,

Yes, both databases are on the same server which is pretty standard for the Drupal/CiviCRM sites that I maintain. CiviCRM entities are made available to Drupal via a CiviCRM Entities module.

I've been developing in CiviCRM since Drupal 6 and prior to Drupal 9/10 we used to add a hard coded DB array to the settings.php file to tell Views about the CiviCRM database. It was the form of:

$databases['default']['default']['prefix']= [
'default' => '',
'civicrm_acl' => '`database_civicrm`.',
'civicrm_acl_cache' => '`database_civicrm`.',
'civicrm_acl_contact_cache' => '`database_civicrm`.',
'civicrm_acl_entity_role' => '`database_civicrm`.',
. . . More definitions . . .,
];

I've opened up a conversation of the mattermost channel that handles the CiviCRM Entities module to see if there is a database api method that recognizes the database that is responsible for the appropriate entities.

The actual error I get is as follows:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database_civicrm.civicrm_event' doesn't exist: SELECT node_field_data.nid AS "nid", NULL AS "civicrm_event_start_date", NULL AS "id", :view_combined_event_listing_block_1_combined_event_listing_block_1 AS "_view_id", NULL AS "_order_0" FROM "node_field_data" "node_field_data" WHERE ("node_field_data"."status" = :db_condition_placeholder_0) AND ("node_field_data"."type" IN (:db_condition_placeholder_1)) UNION SELECT node_field_data.nid AS "nid", NULL AS "civicrm_event_start_date", NULL AS "id", :view_drupal_event_listing_default_drupal_event_listing_default AS "_view_id", NULL AS "_order_0" FROM "node_field_data" "node_field_data" WHERE ("node_field_data"."status" = :db_condition_placeholder_2) AND ("node_field_data"."type" IN (:db_condition_placeholder_3)) UNION SELECT NULL AS "nid", civicrm_event.start_date AS "civicrm_event_start_date", civicrm_event.id AS "id", :view_events_listing_default_events_listing_default AS "_view_id", civicrm_event.start_date AS "_order_0" FROM "civicrm_event" "civicrm_event" WHERE (("civicrm_event"."is_active" = :db_condition_placeholder_4) AND ("civicrm_event"."is_public" = :db_condition_placeholder_5)) AND (((DATE_FORMAT((civicrm_event.start_date + INTERVAL -25200 SECOND), '%Y-%m-%d\T%H:%i:%s') >= DATE_FORMAT(('2024-12-10T07:00:00' + INTERVAL -25200 SECOND), '%Y-%m-%d\T%H:%i:%s'))) OR ((DATE_FORMAT((civicrm_event.end_date + INTERVAL -25200 SECOND), '%Y-%m-%d\T%H:%i:%s') >= DATE_FORMAT(('2024-12-10T07:00:00' + INTERVAL -25200 SECOND), '%Y-%m-%d\T%H:%i:%s')))) ORDER BY "_order_0" ASC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => drupal_event [:view_combined_event_listing_block_1_combined_event_listing_block_1] => combined_event_listing:block_1 [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => drupal_event [:view_drupal_event_listing_default_drupal_event_listing_default] => drupal_event_listing:default [:db_condition_placeholder_4] => 1 [:db_condition_placeholder_5] => 1 [:view_events_listing_default_events_listing_default] => events_listing:default )

If I can't find a solution that uses the database API, I'll likely just write a module and run the queries in code. Hopefully I can though because this would make a lot of Drupal/CiviCRM listings better; not just events but newsletters and other things that would benefit from being listed together.

Cheers,
Andrew

Hi Bryan,
The CiviCRM Entity module devs have indicated that it's unlikely I'll be able to resolve this issue without custom coding my own Views field plugins so I'm going to close this as works as designed and carry on.

It's a very cool module though so I'll be using in again when I can. I may poke around and see if I can add a custom views field that works in tandem with it where I can override the database connection to assign the one for CiviCRM. I think that would work but it would probably be easier to just write a purpose built plugin for this one specific job. I've got Views field plugin boiler plate code I can borrow from a recent project that did something similar with a remote data warehouse.

Thanks again for the help.

Cheers,
Andrew