Skip to Content Skip to Menu

🌟 CB Editor Assistant 1.0.0 is here! Discover our new AI Joomla Plugin that wrote its story! (and this banner!)
Start at just $12.50/month* or 💸 save 30% with our 🛍️ Black Friday Intro Offer for your subscription's lifetime
🎉 Black Friday sale is here! Great savings on professional and developer memberships! Get 25% off now with code BLACK-FRIDAY-2024!

List with users, that are subscribed to an acymailing list

  • kommid
  • kommid
  • ONLINE
  • Posts: 67
  • Thanks: 12
  • Karma: 0
2 weeks 5 days ago #339588 by kommid
Hi there,
I need to set up a list of my users, that are subscribed to my newsletter. As newsletter component I use acymailing. Is this possible?

Please Log in or Create an account to join the conversation.

  • krileon
  • krileon
  • ONLINE
  • Posts: 48570
  • Thanks: 8293
  • Karma: 1445
2 weeks 5 days ago #339590 by krileon
Yes, you can use Advanced filters in your userlist to directly add to the query to filter it as needed, but you'll need to write the SQL for that.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in or Create an account to join the conversation.

  • kommid
  • kommid
  • ONLINE
  • Posts: 67
  • Thanks: 12
  • Karma: 0
2 weeks 4 days ago - 2 weeks 4 days ago #339600 by kommid
Thank you Kyle,
I think I don't understand, how the advanced filter works.
I have a query that returns 1 (my list id) if the email address is subscribed to the list and it returns an empty set, if the email address is not subcribed to the list.
Code:
SELECT l.list_id AS liste FROM uwyke_acym_user_has_list l INNER JOIN uwyke_acym_user u ON u.id = l.user_id WHERE l.list_id = 1 and l.status = 1 and u.email = "\[email\]"
This query works fine, if I run it in PHPMyAdmin, (substuting \[email\] with real email addresses) but my list is empty despite the fact, that there are multiple users subscribed to this list.
Last edit: 2 weeks 4 days ago by kommid.

Please Log in or Create an account to join the conversation.

  • krileon
  • krileon
  • ONLINE
  • Posts: 48570
  • Thanks: 8293
  • Karma: 1445
2 weeks 4 days ago #339604 by krileon
The advanced filter directly adds to the WHERE statement of the userlist query. Example as follows.
Code:
ue.`cb_myfield` = 123

That would filter on the cb_myfield field of the _comprofiler table checking for the value 123. This is added to the query as an AND. The table alias "ue" refers to _comprofiler and "u" to _users (this is noted in the parameter description with examples as well).

You won't be able to do any joins here so you need to write your query as a subquery to filter the list down. I'm not familiar with AcyMailing data structure, but below is an example based off what I could find about the structure of acym_user_has_list table.
Code:
EXISTS ( SELECT 1 FROM `#__acym_user_has_list` AS acy WHERE acy.`user_id` = u.`id` AND acy.`list_id` = 1 AND acy.`status` = 1 )


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in or Create an account to join the conversation.

  • kommid
  • kommid
  • ONLINE
  • Posts: 67
  • Thanks: 12
  • Karma: 0
2 weeks 7 hours ago #339635 by kommid
I'm afraid, this won't work, because for some reason acym_user_has_list.user_id is not equal to u.id. There is another table acym_user that translates joomla user ids to acymailing user ids, that's why I used the join.
Can I use joins with the query field? Maybe I could use the query field and use it's result for my filter?

Please Log in or Create an account to join the conversation.

  • krileon
  • krileon
  • ONLINE
  • Posts: 48570
  • Thanks: 8293
  • Karma: 1445
2 weeks 1 hour ago #339638 by krileon
You can use a join in the select subquery and that should solve your issue.

Can I use joins with the query field? Maybe I could use the query field and use it's result for my filter?

No, query fields have no storage and they can't be used to insert queries into other queries.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in or Create an account to join the conversation.

Moderators: beatnantkrileon
Powered by Kunena Forum