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:
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 firstname.lastname@example.org and we can discuss further details.