Multiple messages with send_mail function

[DSMejantel]

Hello !

I try to send a message to different user when some condition is done :

--Mailing des besoinsSELECTsqlpage.send_mail(json_object(
'to', courriel,
'subject', 'Alerte Barjac Mobilités',
'body', 'Bonjour '||user_id||'. Un conducteur propose un trajet vers une de vos destinations demandées : '|| arrivee ||', le '|| strftime('%d/%m/%Y',jour) ||' pour une arrivée vers '|| heure||'. Vous pouvez consulter https://covoiturage.barjac-en-lozere.fr pour réserver votre place.'
)) FROM alerte WHERE courriel IS NOT NULLAND courriel !='';

Its work. Each user receive a message with personal information.
But this function is verbose and we can't use the redirect component.

I try to use sqlpage.run_sql but I have the same issue.

How can I do to use this awesome new function ?

[lovasoa]

Hi ! run_sql does sound like the solution to your problem, why doesn't it work ? you can use it to summarize the results of all your send_mail calls

send_mails.sql

SELECTsqlpage.send_mail(json_object(...)) FROM myemails

show.sql

something like

SET mail_results =sqlpage.run_sql('send_mails.sql');
SELECT'alert'AS component,
CASE
WHEN failed_count >0 THEN 'warning'
WHEN total_count =0 THEN 'info'
ELSE 'success'
END AS color,
'Mailing result'AS title,
printf(
'%d recipient(s)%d messages sent%d errors',
total_count,
total_count - failed_count,
failed_count
) AS description
FROM (
SELECTcount(*) AS total_count,
coalesce(
sum(((value ->>'$.result') ->>'$.status') ='error'),
0
) AS failed_count
FROM json_each($mail_results)
);

[DSMejantel]

Hi lovasoa !
You're right. I have the soluce with sqlpage.run_sql but I have another error in my file !

So :

-- Exécution muette des envois de mail (via le fichier envoi_alerte.sql)SET mail_results =sqlpage.run_sql('envoi_alerte.sql'); 

envoi_alerte.sql

SELECTsqlpage.send_mail(json_object(
'to', courriel,
'subject', 'Alerte Barjac Mobilités',
'body', 'Bonjour '||user_id||'. Un conducteur propose un trajet vers une de vos destinations demandées : '|| arrivee ||', le '|| strftime('%d/%m/%Y',jour) ||' pour une arrivée vers '|| heure||'. Vous pouvez consulter https://covoiturage.barjac-en-lozere.fr pour réserver votre place.'
)) FROM alerte WHERE courriel IS NOT NULLAND courriel !=''