Manipulate segment query build

The opportunity is:

I do not really how to manipulate query generated by the segment. Here is the situation: I have custom table TableA that has FK referencing the leads table. Inside TableA I have two attributes that are used by the segment filter:

  • attribute1
  • attribute2_datetime

Now all of the fields above are in segment filter drop down and I can create a segment, without an issue. It even works if I use just one of the fields in the filter. But when I add more then one condition to the segment, it does not work as expected. The problem is in generated query.

For each filter Mautic generates new sub-query but in order for me to get the leads I want on the segment, I need to either generate just one sub-query or no sub-query at all.

Here is the SQL, it generates:

SELECT l.id
FROM leads l
WHERE
  l.id IN (
    SELECT parc.lead_id
    FROM tableA parc
    WHERE parc.attribute2_datetime >= :parb
  )
  AND
  l.id IN (
    SELECT parf.lead_id
    FROM tableA parf
    WHERE parf.attribute2_datetime <= :pare
  )
  AND
  l.id IN (
    SELECT pari.lead_id
    FROM tableA pari
    WHERE pari.attribute1 = :parh
  );

And here is the example query, I am after:

SELECT l.id
FROM leads l
WHERE
  l.id IN (
    SELECT parc.lead_id
    FROM tableA parc
    WHERE pari.attribute1 = :parh AND parc.attribute2_datetime >= :parb AND parf.attribute2_datetime <= :pare
  );

Things to be aware of:

  • Solution has to work when segments:update is trying to add or remove the contacts from the segment
  • Given the nature of the fields this new query build should be triggered for any segment that has attribute2_datetime inside filters.

It is based in: Online

Remote OK?: Yes

Salary/remuneration: To be discussed

To find our more:

Drop me an email at matic@sos-sw.si and we can discuss further details.

Hello,

I am interested, If the work is still open please get in touch with me via email or Skype.

Email - seth@cisinlabs.com

Skype - cis.seth

Hi,

at the end I managed to this on my own by manipulating the query via event subscriber on this: LeadEvents::LIST_FILTERS_QUERYBUILDER_GENERATED, happy to help someone else if it comes up :).

Regards, M.

1 Like

This topic was automatically closed 36 hours after the last reply. New replies are no longer allowed.