Development issueshttps://lab.civicrm.org/groups/dev/-/issues2023-10-03T21:46:14Zhttps://lab.civicrm.org/dev/core/-/issues/4457Entity Reference Custom Field not working as filter in "regular" search2023-10-03T21:46:14ZjensschuppeEntity Reference Custom Field not working as filter in "regular" searchFollow-up to #3721. @fabian_SYSTOPIA found out that filtering doesn't work correctly.
Not sure this is to be fixed, as it's related to the "old-style" searches ...
## Steps to reproduce
* Create an Entity Reference Custom Field on the...Follow-up to #3721. @fabian_SYSTOPIA found out that filtering doesn't work correctly.
Not sure this is to be fixed, as it's related to the "old-style" searches ...
## Steps to reproduce
* Create an Entity Reference Custom Field on the *Participant* entity for referencing an *Event* entity
* Edit a participant and fill out the field by selecting an event
* Use the *Find Participants* search and try to limit the search result to participants with the event previously selected in that field
* Notice that all participants are being found, not only that one with the entity reference field being filledhttps://lab.civicrm.org/dev/core/-/issues/4637Profile Update from SearchKit doesn't work2023-10-03T20:55:03ZlarsssandergreenProfile Update from SearchKit doesn't workIf you try to use Profile Update in SK, it pops up a modal to select the profile and then just closes when you click continue. Verified this is the behaviour back to at least 5.58. Probably makes more sense to get in-place editing workin...If you try to use Profile Update in SK, it pops up a modal to select the profile and then just closes when you click continue. Verified this is the behaviour back to at least 5.58. Probably makes more sense to get in-place editing working well enough to replace Profile Update, but at least we should remove it if is broken.https://lab.civicrm.org/dev/core/-/issues/209Case Scheduled Reminders2023-10-03T05:03:22ZshitijgCase Scheduled Reminders**User story:**
- As a CiviCRM administrator
- I would like to configure a scheduled reminder to be sent
- that will include the following tokens:
- All fields from table civicrm_case and associated case custom fields
- All contac...**User story:**
- As a CiviCRM administrator
- I would like to configure a scheduled reminder to be sent
- that will include the following tokens:
- All fields from table civicrm_case and associated case custom fields
- All contact tokens for the contact the scheduled reminder is being sent to as per normal scheduled reminders
- when a specific case type (or types) changes to a specific case status (or status’s)
- either immediately or after a desired period of time has passed from the change in status*
- to one or more case roles, groups of contacts or individual contacts
- so that I can have an email or sms sent to specific contacts when the case status changes to inform them about changes in the case.
**Wireframe:**
The wireframe in the original ticket is somewhat incomplete and doesn’t discuss
- The time period point and how it should work
- The case role’s filtering
As such I would suggest the following solution:
![8d2fca53-d8a0-4cfa-b208-4cc889ee09e0](/uploads/a292bb55efdea95398d0ca47e4b363d4/8d2fca53-d8a0-4cfa-b208-4cc889ee09e0.png)
**Notes:**
- If case type or case status is not selected then all case types and status should send the reminder.
- The “when” feature should operate such that if it is left blank then the reminder should be sent and only sent once but for cases meeting the criteria and the following when criteria:
- “After status change”
- Immediately after the status change takes place if that is the selected option and no other fields are filled in.
- Or the specified time after the status change takes place if a “number of hours or days after” is filled in
- Or on or after the “when” date if this is filled in
- If both the when and the “number of hours or days after” is filled in then the scheduled reminder should send only once on the earlier of those two dates. CiviCRM’s UX here is a bit nutty - 😳
- “Case start date”
- Immediately after the case start date for cases that meet that criteria if that is the selected option and no other fields are filled in.
- Or the specified time after the case start date if a “number of hours or days after” is filled in
- Or on or after the “when” date if this is filled in
- If both the when and the “number of hours or days after” is filled in then the scheduled reminder should send only once on the earlier of those two dates.
- “Case end date”
- Immediately after the case end date for cases that meet that criteria if that is the selected option and no other fields are filled in.
- Or the specified time after the case end date if a “number of hours or days after” is filled in
- Or on or after the “when” date if this is filled in
- If both the when and the “number of hours or days after” is filled in then the scheduled reminder should send only once on the earlier of those two dates.
- NICE TO HAVE: Can we also support any date type custom fields that are added to the case with the same logic and list those custom fields in the dropdown? i.e. create a case custom field “Court date” and then have that date in the scheduled reminder filters? Lower priority.
- Limit or add to case roles:
- Should allow user to select the case roles that the email will be sent to.https://lab.civicrm.org/dev/core/-/issues/2859Custom File field in custom group used by entity group not saving2023-10-03T05:03:21ZnieloCustom File field in custom group used by entity group not savingI attached a new custom group to entity type group and added a field of type file. When editing the group settings and fill in a file, it is posted without errors and notification bubble confirms success. But there is no file uploaded.
...I attached a new custom group to entity type group and added a field of type file. When editing the group settings and fill in a file, it is posted without errors and notification bubble confirms success. But there is no file uploaded.
No error logs even with debugging enabled, file tmp folder is checked and I successfully tried another custom file field attached to entity contact.
Just tried on https://dmaster.demo.civicrm.org/ - same misbehavior.
Looked at the code and it seems that the posted file info is not getting into the params array. Although the posted name is of correct format custom_123_21313 the packages/HTML/QuickForm/Controller.php->exportValues gets "MAX_FILE_SIZE" as key for the custom field. Could it be that https://lab.civicrm.org/dev/core/-/blob/master/CRM/Group/Controller.php 's overridden custom file field preparation causes this error?
Civi and Drupal are current versions.https://lab.civicrm.org/dev/core/-/issues/2865Missing message template updates2023-10-02T05:03:25ZDaveDMissing message template updatesI had noticed a missing handful earlier and added it to https://github.com/civicrm/civicrm-core/blob/0ebeb224903925880f3ad0ddacd4e90470049839/CRM/Upgrade/Incremental/MessageTemplates.php#L252, but I'm noticing a few more seem to be out o...I had noticed a missing handful earlier and added it to https://github.com/civicrm/civicrm-core/blob/0ebeb224903925880f3ad0ddacd4e90470049839/CRM/Upgrade/Incremental/MessageTemplates.php#L252, but I'm noticing a few more seem to be out of date. I'll try to reconcile.
It's technically a regression but at first glance they all seem to just be php8 updates, nothing functional.
A bigger project: Why does the upgrade work the way it does and why does it need this list to be manually updated? Why doesn't it just compare the default version in the database to what's on disk, and update it if different? Why does it even need a default version in the database anyway - maybe just because it makes Revert code easier?https://lab.civicrm.org/dev/core/-/issues/2877Add recurring failed messaging to core?2023-10-02T05:03:25ZeileenAdd recurring failed messaging to core?I'm just working on updating our custom code for sending emails when people's credit cards start failing & just thought I'd put it out there that this could be moved to core if there is demand.
The basic spec is
- a workflow template
-...I'm just working on updating our custom code for sending emails when people's credit cards start failing & just thought I'd put it out there that this could be moved to core if there is demand.
The basic spec is
- a workflow template
- code to send an email when failures hit a threshold IF they don't have any active recurrings (because people often start a new one themselves).
- the threshold would be from a 'number of fails before emailing' setting I think with empty = disabled
My guess is that user orgs would feel this should be in core & extension writers might prefer to solve in an extension. If there isn't demand I won't do it.https://lab.civicrm.org/dev/core/-/issues/4627Proposal: Membership type label2023-10-01T19:23:57Zmattwiremjw@mjwconsult.co.ukProposal: Membership type labelThe membership type table only has "name" and you cannot specify a separate label/title for display. This makes it problematic for portability/import/export as well as translation.
Proposal: Add new field "label" to MembershipType which...The membership type table only has "name" and you cannot specify a separate label/title for display. This makes it problematic for portability/import/export as well as translation.
Proposal: Add new field "label" to MembershipType which is used for display instead of the name.https://lab.civicrm.org/dev/core/-/issues/2854ipAddress function and front-end proxies (like Varnish)2023-10-01T05:03:27ZAlanDixonipAddress function and front-end proxies (like Varnish)There are times where CiviCRM likes to know who it's talking to, i.e. the ip address of the visitor.
This matters especially when it gets passed on to a payment processor (e.g. for the purposes of mitigating card tumbling).
Here's the ...There are times where CiviCRM likes to know who it's talking to, i.e. the ip address of the visitor.
This matters especially when it gets passed on to a payment processor (e.g. for the purposes of mitigating card tumbling).
Here's the utility function that does that for several core-shipped payment processors:
https://github.com/civicrm/civicrm-core/blob/b599743f3daa46ab96c09ebe410fbb833cdd080f/CRM/Utils/System.php#L1293
This code is fairly naive, but notably makes use of the fact that Drupal 7 (and earlier) that had a function "ip_address()" that would pay attention to the Drupal configuration to be able to deal with front end proxies.
Unfortunately, D8/9 no longer includes this function, but more importantly, it also fails for other CMSs.
In researching this issue, I noticed that D8/9 now uses a core symphony function, which might provide a better solution than using our current CMS-specific approach.
Specifically, in Drupal, you can reliably get the 'client' ip with this: return Drupal::request()->getClientIp();
Assuming civicrm has a container similar to Drupal, a similar solution might be available for CiviCRM.
It's reasonable to ask whether ipAddress should be changed in this way - here's a search showing where this function gets called:
https://github.com/civicrm/civicrm-core/search?q=ipAddresshttps://lab.civicrm.org/dev/core/-/issues/2860Contribution Page Premium "No Thank You" is always disabled2023-10-01T05:03:26ZredgarContribution Page Premium "No Thank You" is always disabledOverview
----------------------------------------
Contribution pages that have the option of Premiums enabled always have the "No Thank You" checkbox disabled. This is true even if you select a donation greater than the amount for the mi...Overview
----------------------------------------
Contribution pages that have the option of Premiums enabled always have the "No Thank You" checkbox disabled. This is true even if you select a donation greater than the amount for the minimum premium. The checkbox still works - but the input field has disabled="disabled" and the checkbox is always grayed out.
I'm using civicrm 5.41.1 on Joomla.
Proposed behavior
----------------------------------------
Code could be written to enable the checkbox once a donation amount greater than the minimum is selected - but it would be easier, and maybe even preferable to never disable the checkbox.
[screenshots](/uploads/81546b7525d3093b8483b6ffc37f7e35/screenshots.png)
Comments
----------------------------------------
The code that sets it to disabled in in templates/CRM/Contribute/Form/Contribution/PremiumBlock.tpl
Line numbers 44, 47, 96, and 99 hardcode the checkbox to disabled.https://lab.civicrm.org/dev/core/-/issues/2869Api4 explorer - Clicking the Debug checkbox on or off doesn't change the samp...2023-09-30T05:15:09ZDaveDApi4 explorer - Clicking the Debug checkbox on or off doesn't change the sample codeI think it should add setDebug()?
Issue needs debugging. I tried clicking the debug checkbox to ... oh, right.
FYI @colemanwI think it should add setDebug()?
Issue needs debugging. I tried clicking the debug checkbox to ... oh, right.
FYI @colemanwhttps://lab.civicrm.org/dev/core/-/issues/2875CiviCase Forces Case Status to Resolved (Closed) when all Activities in a Seq...2023-09-30T05:03:30ZpbarmakCiviCase Forces Case Status to Resolved (Closed) when all Activities in a Sequence are CompleteOverview
----------------------------------------
For Cases that use a Sequence, once all activities in the sequence are complete, the Case gets set to a Resolved (aka Closed) status. This is fine. However, after a Case is in the Resolve...Overview
----------------------------------------
For Cases that use a Sequence, once all activities in the sequence are complete, the Case gets set to a Resolved (aka Closed) status. This is fine. However, after a Case is in the Resolved status, if a user then wishes to change the status to something other than Resolved (like to a custom status), even though CiviCase says it made the change and even creates a status change activity that shows the status was changed, the case remains in Resolved status.
It looks like Civi/CCase/SequenceListener.php is firing after any case status changes are written to the DB, but SequenceListener is not accounting for someone purposefully trying to change the case status. It only looks for all activities being completed and forces the case back to Resolved.
This doesn't seem like correct behavior nor a good user experience. The listener should check to see if the action was a status change action and not fire the API call in that case. I'm not sure how that can be done (not sure what input the listener has), but that's my guess on how to resolve the issue.
Reproduction steps
----------------------------------------
1. Create a Case Type that uses a sequence of activities (doesn't matter how many)
1. Create a new Case using that type
1. Complete each activity in the sequence and see that the Case automatically changes to Resolved.
1. Try to then change the Case Status to something other than Resolved (like In Progress or Ongoing or any custom status).
1. See that the Case status remains as Resolved, even though a new case activity says it was changed.
Expected behaviour
----------------------------------------
The Case should allow a change in Status even if all Activities in a Sequence are completed. That way user can change to a custom status that also reflects completion (we have multiple status that denote completion but for a variety of reasons).https://lab.civicrm.org/dev/core/-/issues/4630Searchkit: add Bypass permission to Autocomplete display2023-09-29T17:48:47ZsamuelsovSearchkit: add Bypass permission to Autocomplete displayCurrently, it's not possible to choose "Bypass permission" for autocomplete display.
If you create a public formbuilder with an autocomplete defined by SK, it will likely fail as anonymous users don't have the required access for most e...Currently, it's not possible to choose "Bypass permission" for autocomplete display.
If you create a public formbuilder with an autocomplete defined by SK, it will likely fail as anonymous users don't have the required access for most entities.
I tried exporting the SearchKit and force the setting `acl_bypass` but no luck...
In my case, the autocomplete is to give a list of Events but I suppose it's the same problem for other entities.samuelsovsamuelsovhttps://lab.civicrm.org/dev/core/-/issues/2651CivicrmBreadcrumbBuilder is getting invalid URLs and breaking2023-09-29T13:46:29ZananelsonCivicrmBreadcrumbBuilder is getting invalid URLs and breakingOverview
----------------------------------------
I ran into this exception while trying to open most any page in CiviCase (using compucorp's CiviCase but I don't think that matters).
InvalidArgumentException: The user-entered string '...Overview
----------------------------------------
I ran into this exception while trying to open most any page in CiviCase (using compucorp's CiviCase but I don't think that matters).
InvalidArgumentException: The user-entered string 'https://example.com/civicrm/case/a/?case_type_category=1#/case?case_type_category=1' must begin with a '/', '?', or '#'. in Drupal\Core\Url::fromUserInput() (line 213 of core/lib/Drupal/Core/Url.php).
I found that the breadcrumbs list contained:
- /
- /civicrm?reset=1
- https://example.com/civicrm/case/a/?case_type_category=1#/case?case_type_category=1
So somehow the items being added to breadcrumbs list weren't getting parsed correctly. I worked around this temporarily by modifying the build() function to only add valid items:
```
public function build(RouteMatchInterface $route_match) {
$breadcrumb = new Breadcrumb();
$breadcrumb->addCacheContexts(['url']);
$breadcrumb->addLink(Link::createFromRoute($this->t('Home'), '<front>'));
foreach ($this->civicrmPageState->getBreadcrumbs() as $name => $url) {
// All urls here have been passed trough CRM_Utils_System::url, so we have
// to parse and decode them before creating a drupal Url object.
> if (substr($url, 0, 1) == "/") {
$url = Url::fromUserInput(html_entity_decode($url));
$breadcrumb->addLink(new Link($name, $url));
> }
}
return $breadcrumb;
}
```
Since it's not a big deal to me to not have the current page in breadcrumbs, and this was an easy way to fix.
I'm not sure how the invalid url was added to the breadcrumbs, and whether that's happening in the extension or in core, but it does seem like a little checking to be sure an invalid breadcrumb doesn't cause the whole page to break would be useful in core.
Environment information
----------------------------------------
* __CiviCRM:__ _5.38.0_https://lab.civicrm.org/dev/core/-/issues/2879Message admin extension won't load workflows from extensions2023-09-29T05:03:22ZeileenMessage admin extension won't load workflows from extensionsI spent ages trying to get our wmf workflow message to work with the Message Admin extension (I even renamed all the abbreviated variable names to words & added getters for all the abbreviated class properties so I could read the code wi...I spent ages trying to get our wmf workflow message to work with the Message Admin extension (I even renamed all the abbreviated variable names to words & added getters for all the abbreviated class properties so I could read the code without glazing over) and ... eventually I tracked it down.....
https://github.com/civicrm/civicrm-core/blob/master/Civi/Test/ExampleDataLoader.php#L80-L88
Normally I would say it's great to constrain things to core while things bed in - but in this case being able to define workflow messages in an extension is the main deliverable.https://lab.civicrm.org/dev/core/-/issues/805CiviCRM cases don't appear on contact case tab if no activities2023-09-28T05:03:22ZDevAppCiviCRM cases don't appear on contact case tab if no activitiesWhen viewing a client record, then clicking the Cases tab - The cases are not displayed if the case has no activities associated with it. This can happen if you don't have a standard timeline with the case, which typically has the open j...When viewing a client record, then clicking the Cases tab - The cases are not displayed if the case has no activities associated with it. This can happen if you don't have a standard timeline with the case, which typically has the open job activity.
Doing some debugging, the SQL join appears to be an INNER JOIN and not a LEFT JOIN for civicrm_activity. When changed to a LEFT JOIN, then the cases without activities show as the join doesn't filter by the join.
```
SELECT civicrm_case.id as case_id
FROM civicrm_contact contact_a
LEFT JOIN civicrm_phone
ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)
LEFT JOIN civicrm_case_contact
ON civicrm_case_contact.contact_id = contact_a.id
INNER JOIN civicrm_case ON civicrm_case_contact.case_id = civicrm_case.id
LEFT JOIN civicrm_relationship case_relationship ON ( case_relationship.contact_id_a = civicrm_case_contact.contact_id AND case_relationship.contact_id_b = 1 AND case_relationship.case_id = civicrm_case.id )
LEFT JOIN civicrm_relationship_type case_relation_type ON ( case_relation_type.id = case_relationship.relationship_type_id AND case_relation_type.id = case_relationship.relationship_type_id )
LEFT JOIN civicrm_case_activity
ON civicrm_case_activity.case_id = civicrm_case.id
INNER JOIN civicrm_activity case_activity
ON ( civicrm_case_activity.activity_id = case_activity.id AND case_activity.is_current_revision = 1 )
LEFT JOIN civicrm_option_group option_group_activity_type
ON (option_group_activity_type.name = 'activity_type')
LEFT JOIN civicrm_option_value rec_activity_type ON (case_activity.activity_type_id = rec_activity_type.value AND option_group_activity_type.id = rec_activity_type.option_group_id )
LEFT JOIN civicrm_option_group option_group_case_status
ON (option_group_case_status.name = 'case_status')
LEFT JOIN civicrm_option_value case_status
ON (civicrm_case.status_id = case_status.value AND option_group_case_status.id = case_status.option_group_id )
LEFT JOIN civicrm_case_type ON civicrm_case.case_type_id = civicrm_case_type.id WHERE ( contact_a.id = '1' AND civicrm_case.is_deleted = 0 ) AND (contact_a.is_deleted = 0)
GROUP BY civicrm_case.id
```
Page: CRM_Case_BAO_Casehttps://lab.civicrm.org/dev/core/-/issues/2805Field transformations2023-09-27T05:03:14ZeileenField transformationsField transformation options for money field don't make sense
Rounding would make sense
![image](/uploads/3266f956bb062d11d3b574fc6ebad623/image.png)Field transformation options for money field don't make sense
Rounding would make sense
![image](/uploads/3266f956bb062d11d3b574fc6ebad623/image.png)https://lab.civicrm.org/dev/core/-/issues/2493CiviCRM 5.35.1, truncation of money values where $1,000 donation is recorded...2023-09-27T05:03:13ZeileenCiviCRM 5.35.1, truncation of money values where $1,000 donation is recorded as $1 which appears to be truncating at the thousand separator when using the Australian (AU) and US Locale@justinfreeman said to the AU-NZ crowd that there is a bug with money in order api but we can't do anything without a proper bug report
placeholder for regression to be clarified
I wasn't able to replicate based on the limited info pro...@justinfreeman said to the AU-NZ crowd that there is a bug with money in order api but we can't do anything without a proper bug report
placeholder for regression to be clarified
I wasn't able to replicate based on the limited info provided
Follow-up, here's the bug report:
> With CiviCRM 5.35.1, we (Agileware) have observed on two separate sites. The truncation of money values where $1,000 donation is recorded as $1 which appears to be truncating at the thousand separator. This has been observed on both WordPress and Drupal sites.
>
> Drupal site with Xero integration (Contribution stuff API) and a WordPress site using Caldera Form integration (Order API).
Agileware Ref: CIVICRM-1692https://lab.civicrm.org/dev/core/-/issues/4478(D8+) Strange handling of `$initialized` leads to crash in civix2023-09-27T04:51:47Ztotten(D8+) Strange handling of `$initialized` leads to crash in civixSteps to Reproduce
-------------------
Create a site running D9/D10. (*I focused my triage on D10, but the same arises on D9, and I suspect it would affect D8.*)
Then:
```bash
cd web/sites/default/files/civicrm/ext/civicrm
wget https...Steps to Reproduce
-------------------
Create a site running D9/D10. (*I focused my triage on D10, but the same arises on D9, and I suspect it would affect D8.*)
Then:
```bash
cd web/sites/default/files/civicrm/ext/civicrm
wget https://download.civicrm.org/civix/civix-23.08.0.phar -O civix.phar
php ./civix.phar generate:module foobar
## Answer "Y" to question about enabling the extension
```
It crashes with:
```
TypeError: htmlentities(): Argument #1 ($string) must be of type string, array given in /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Utils/System.php on line 284 #0 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Utils/System.php(284): htmlentities(Array)
#1 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Core/Menu.php(445): CRM_Utils_System::url('civicrm/tag', 'reset=1', false, NULL, true, false, true)
#2 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Core/Menu.php(265): CRM_Core_Menu::buildBreadcrumb(Array, 'civicrm/tag/edi...')
#3 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Core/Menu.php(296): CRM_Core_Menu::build(Array)
#4 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Core/Invoke.php(389): CRM_Core_Menu::store()
#5 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/CRM/Extension/Manager.php(319): CRM_Core_Invoke::rebuildMenuAndCaches(true)
#6 /home/totten/bknix/build/d10/vendor/civicrm/civicrm-core/api/v3/Extension.php(42): CRM_Extension_Manager->install(Array)
```
Analysis
--------
* The apparent error stems from `\CRM_Utils_System_Drupal8->url()`. It [swallows an exception](https://github.com/civicrm/civicrm-core/blob/5.64/CRM/Utils/System/Drupal8.php#L291-L305) and returns a malformed `$url` instead. The malformed `$url` is not a `string` and cannot be handled by `htmlentities()`.
* The underlying exception is raised by [`\Drupal\civicrm\Civicrm->initialize()`](https://github.com/civicrm/civicrm-drupal-8/blob/5.64/src/Civicrm.php#L45-L49). It tries to load `civicrm.settings.php` and fails.
* In the big picture, the underlying exception makes no sense. Look at the callstack: CiviCRM is already running. The `civicrm.settings.php` has already been loaded. The file exists.
* __Why are we initializing again?__ Because [CivicrmPathProcessor](https://github.com/civicrm/civicrm-drupal-8/blob/5.64/src/PathProcessor/CivicrmPathProcessor.php#L21-L22) repeatedly creates new instances of `Drupal\civicrm\Civicrm`. This negates the [`$initialized` guard](https://github.com/civicrm/civicrm-drupal-8/blob/5.64/src/Civicrm.php#L27-L29).
* To visualize this, you can apply https://gist.github.com/totten/e983d23439addeb7f76b0d7f52679031 and run `cv flush`.
* __Why is the file missing?__ The CWD changed.
* In the original CLI, the CWD is `DRUPALSITE/web/sites/default/files/civicrm/ext/civicrm`
* During bootstrap, CWD changes to the web-root (`DRUPALSITE/web`). The chosen path for `$settingsFile` is relative to that.
* But `civix` needs to create files in the original CWD. After bootstrap, it returns to the original CWD.
* But `$settingsFile` is relative -- and not valid in this new path.https://lab.civicrm.org/dev/core/-/issues/4567Address CiviCRM Mailing table complexity - make queries easier & data more pr...2023-09-27T01:01:44ZeileenAddress CiviCRM Mailing table complexity - make queries easier & data more prunableIssue
----------------------------------------
1) on large databases the civicrm_mailing screen is slow-to-impossible to load as the query cannot get the status of the mailing from the civicrm_mailing table and must do a many-to-one joi...Issue
----------------------------------------
1) on large databases the civicrm_mailing screen is slow-to-impossible to load as the query cannot get the status of the mailing from the civicrm_mailing table and must do a many-to-one join with DISTINCT to get the status of the mailing. This is not well suited to paging because of the many to 1 so many more records are retrieved than are needed
2) the mailing job records are really just a form of queue management. ie their logic is temporary and related to active sending. Once the send has happened it should be possible to delete them - but the mailing_job records are the only way the civicrm_mailing_event_queue table links to Bounce entries
3) the verp hash needs to be of limited length. While efforts were made in the past to reduce the length the ids of the values in the hash can get large enough to start to blow it up. It really doesn't need to have a queue_id AND a job_id along with the hash
4) complexity complexity complexity. The mailing_job has fields like 'parent_id' and job_type' = 'child'. It's just confusing as well as bloated and unnecessary to refer to it outside it's 'real' function - tracking the actual sending
5) I have some historical nervousness about the civimail system sending out stuff it shouldn't - I feel like great transparency about loading from civicrm_mailing & then processing would be possible with
Proposal
----------------------------------------
**Stage 1**
1) adding the field mailing_id to civicrm_mailing_event_queue
2) altering the verp calculation to stop including the job_id on new mailings(we would need to handle it's presence for a while in bounce processing)
3) altering the job_id index in civicrm_event_queue to set to NULL on delete
4) add columns for status_id, start_date, end_date to civicrm_mailing, update as appropriate.
5) add a batch script to allow people to populate the columns above in their own time.
6) use more efficient queries on sites that have fully migrated (eg. conditional on status_id being populated for all mailings then the civicrm_mailing dashboard could use a more performant query)
<details>
<summary>Handy query for getting row counts in these tables</summary>
```
SELECT 'civicrm_mailing' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing
UNION
SELECT 'civicrm_mailing_job' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_job
UNION
SELECT 'civicrm_mailing_event_queue' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_queue
UNION
SELECT 'civicrm_mailing_event_bounce' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_bounce
UNION
SELECT 'civicrm_mailing_event_confirm' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_confirm
UNION
SELECT 'civicrm_mailing_event_delivered' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_delivered
UNION
SELECT 'civicrm_mailing_event_forward' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_forward
UNION
SELECT 'civicrm_mailing_event_opened' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_opened
UNION
SELECT 'civicrm_mailing_event_reply' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_reply
UNION
SELECT 'civicrm_mailing_event_subscribe' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_subscribe
UNION
SELECT 'civicrm_mailing_event_trackable_url_open' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_trackable_url_open
UNION
SELECT 'civicrm_mailing_event_unsubscribe' as mailing_table, COUNT(*) as number_rows
FROM civicrm_mailing_event_unsubscribe
```
</details>
**Stage 2**
1) deprecate the field civicrm_mailing.is_completed
6) set up some processes around deleting mailing_job records once no longer needed.
8)remove the interim queries for the partially migrated mailing records
*Stage 3*
7) eventually the civicrm_mailing.is_completed column could go
8) stop handling the job id in the verp (probably after a few years)
9) add self-cleanup on civicrm_mailing_job records (how would we ideally clean them up once completed?)
10) migrate mailing_job to our queue system
**Technical notes**
The way in which mailing events link back to the mailing looks like (eg. for bounce)
- civicrm_mailing_event_bounce.event_queue_id => civicrm_mailing_event_queue.id
- civicrm_mailing_event_queue.job_id => civicrm_mailing_job.id
- civicrm_mailing_job.mailing_id => civicrm_mailing.id
A similar pattern is followed for
civicrm_mailing_event_delivered
civicrm_mailing_event_forward
civicrm_mailing_event_opened
civicrm_mailing_event_reply
civicrm_mailing_event_subscribe
civicrm_mailing_event_confirm links back through civicrm_mailing_event_subscribe
**Still confusing**
How do civicrm_mailing_recipients & civicrm_mailing_event_queue relate to each other?**** UPDATE - it seems like mailing_event_recipients doesn't add much extra over civicrm_mailing_event_queue but it is used to serve 2 purposes
1) it fulfills the role of a temp table when generating the entries for civicrm_mailing_queue
2) it is used for include / exclude queries - this could be done by event_queue too but perhaps would be more blocking
It does feel like it doesn't add anything that just using queue wouldn't, if we pointed the various queries at queue
What is the status with this historic evilness https://lab.civicrm.org/dev/core/-/issues/4385https://lab.civicrm.org/dev/core/-/issues/4566Unsubscribe group field is no longer shown when creating a Mosaico mailing fr...2023-09-26T18:25:40ZlarsssandergreenUnsubscribe group field is no longer shown when creating a Mosaico mailing from search resultsTo reproduce:
1. Enable Mosaico
1. Do a search, select some contacts
2. Select Email - schedule/send via CiviMail
Note that the Unsubscribe Group field is missing. It will be show for a traditional mailing.
I think this a core problem...To reproduce:
1. Enable Mosaico
1. Do a search, select some contacts
2. Select Email - schedule/send via CiviMail
Note that the Unsubscribe Group field is missing. It will be show for a traditional mailing.
I think this a core problem, rather than a Mosaico problem, as it showed up for us after upgrading to 5.65, but without having upgraded Mosaico versions. Verified this is still an issue on dmaster with latest Mosaico. Possibly related to [#26585](https://github.com/civicrm/civicrm-core/pull/26585) which changed the recipient fields?