# Manipulate segment query build

**URL:** <https://forum.mautic.org/t/manipulate-segment-query-build/29384>\
**Category:** Commercial\
**Created:** [September 21, 2023, 10:57am UTC](https://forum.mautic.org/t/manipulate-segment-query-build/29384 "2023-09-21T10:57:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\
**Post date:** [September 21, 2023, 10:57am UTC](https://forum.mautic.org/t/manipulate-segment-query-build/29384/1 "2023-09-21T10:57:14Z")

</div>

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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![seth2398](https://avatars.discourse-cdn.com/v4/letter/s/9de0a6/32.png) [@seth2398](https://forum.mautic.org/u/seth2398)\
**Post date:** [September 21, 2023, 11:24am UTC](https://forum.mautic.org/t/manipulate-segment-query-build/29384/2 "2023-09-21T11:24:54Z")

</div>

Hello,

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

Email - [seth@cisinlabs.com](mailto:seth@cisinlabs.com)

Skype - cis.seth

---

<div class="post-metadata">

**Author:** ![mzagmajster](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mautic.org/mzagmajster/32/687_2.png) [@mzagmajster](https://forum.mautic.org/u/mzagmajster)\
**Post date:** [September 27, 2023, 1:21pm UTC](https://forum.mautic.org/t/manipulate-segment-query-build/29384/3 "2023-09-27T13:21:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/mautic/original/1X/43c63600fe51440378769136903f1fa2a9a34102.png) [@system](https://forum.mautic.org/u/system)\
**Post date:** [September 29, 2023, 2:00pm UTC](https://forum.mautic.org/t/manipulate-segment-query-build/29384/4 "2023-09-29T14:00:14Z")

</div>

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