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.