select
    distinct user0_.id as id1_14_0_,
             contact4_.id as id1_0_1_,
             user7_.id as id1_14_2_,
             organizati9_.id as id1_3_3_,
             contact10_.id as id1_0_4_,
             user12_.id as id1_14_5_,
             role14_.id as id1_18_6_,
             contact15_.id as id1_0_7_,
             user0_.active as active2_14_0_,
             user0_.avatar as avatar3_14_0_,
             user0_.birth_day as birth_da4_14_0_,
             user0_.create_date as create_d5_14_0_,
             user0_.creation_as_author_date as creation6_14_0_,
             user0_.esia_contact_id as esia_co19_14_0_,
             user0_.inn as inn7_14_0_,
             user0_.internal_contact_id as interna20_14_0_,
             user0_.last_update as last_upd8_14_0_,
             user0_.middlename as middlena9_14_0_,
             user0_.name as name10_14_0_,
             user0_.nick_name as nick_na11_14_0_,
             user0_.password as passwor12_14_0_,
             user0_.pen_name as pen_nam13_14_0_,
             user0_.probation as probati14_14_0_,
             user0_.snils as snils15_14_0_,
             user0_.status_id as status_16_14_0_,
             user0_.surname as surname17_14_0_,
             user0_.username as usernam18_14_0_,
             pseudonyms5_.user_id as user_id1_13_0__,
             pseudonyms5_.pseudonyms as pseudony2_13_0__,
             contact4_.email as email2_0_1_,
             contact4_.phone as phone3_0_1_,
             pseudonyms5_.user_id as user_id1_13_0__,
             pseudonyms5_.pseudonyms as pseudony2_13_0__,
             user7_.active as active2_14_2_,
             user7_.avatar as avatar3_14_2_,
             user7_.birth_day as birth_da4_14_2_,
             user7_.create_date as create_d5_14_2_,
             user7_.creation_as_author_date as creation6_14_2_,
             user7_.esia_contact_id as esia_co19_14_2_,
             user7_.inn as inn7_14_2_,
             user7_.internal_contact_id as interna20_14_2_,
             user7_.last_update as last_upd8_14_2_,
             user7_.middlename as middlena9_14_2_,
             user7_.name as name10_14_2_,
             user7_.nick_name as nick_na11_14_2_,
             user7_.password as passwor12_14_2_,
             user7_.pen_name as pen_nam13_14_2_,
             user7_.probation as probati14_14_2_,
             user7_.snils as snils15_14_2_,
             user7_.status_id as status_16_14_2_,
             user7_.surname as surname17_14_2_,
             user7_.username as usernam18_14_2_,
             children6_.child_id as child_id2_16_1__,
             children6_.parent_id as parent_i1_16_1__,
             organizati9_.address as address2_3_3_,
             organizati9_.contact_id as contact12_3_3_,
             organizati9_.full_name as full_nam3_3_3_,
             organizati9_.head_person as head_per4_3_3_,
             organizati9_.inn as inn5_3_3_,
             organizati9_.kpp as kpp6_3_3_,
             organizati9_.legal_form as legal_fo7_3_3_,
             organizati9_.ogrn as ogrn8_3_3_,
             organizati9_.short_name as short_na9_3_3_,
             organizati9_.site as site10_3_3_,
             organizati9_.org_type as org_typ11_3_3_,
             organizati8_.user_id as user_id1_15_2__,
             organizati8_.organizations_id as organiza2_15_2__,
             contact10_.email as email2_0_4_,
             contact10_.phone as phone3_0_4_,
             user12_.active as active2_14_5_,
             user12_.avatar as avatar3_14_5_,
             user12_.birth_day as birth_da4_14_5_,
             user12_.create_date as create_d5_14_5_,
             user12_.creation_as_author_date as creation6_14_5_,
             user12_.esia_contact_id as esia_co19_14_5_,
             user12_.inn as inn7_14_5_,
             user12_.internal_contact_id as interna20_14_5_,
             user12_.last_update as last_upd8_14_5_,
             user12_.middlename as middlena9_14_5_,
             user12_.name as name10_14_5_,
             user12_.nick_name as nick_na11_14_5_,
             user12_.password as passwor12_14_5_,
             user12_.pen_name as pen_nam13_14_5_,
             user12_.probation as probati14_14_5_,
             user12_.snils as snils15_14_5_,
             user12_.status_id as status_16_14_5_,
             user12_.surname as surname17_14_5_,
             user12_.username as usernam18_14_5_,
             parents11_.parent_id as parent_i1_16_3__,
             parents11_.child_id as child_id2_16_3__,
             role14_.id_school as id_schoo2_18_6_,
             role14_.role_name as role_nam3_18_6_,
             roles13_.user_id as user_id1_17_4__,
             roles13_.role_id as role_id2_17_4__,
             contact15_.email as email2_0_7_,
             contact15_.phone as phone3_0_7_
from
    users user0_
        left outer join
    contact contact4_
    on user0_.esia_contact_id=contact4_.id
        left outer join
    user_pseudonyms pseudonyms5_
    on user0_.id=pseudonyms5_.user_id
        left outer join
    users_rel children6_
    on user0_.id=children6_.child_id
        left outer join
    users user7_
    on children6_.parent_id=user7_.id
        left outer join
    users_organizations organizati8_
    on user0_.id=organizati8_.user_id
        left outer join
    organization organizati9_
    on organizati8_.organizations_id=organizati9_.id
        left outer join
    contact contact10_
    on organizati9_.contact_id=contact10_.id
        left outer join
    users_rel parents11_
    on user0_.id=parents11_.parent_id
        left outer join
    users user12_
    on parents11_.child_id=user12_.id
        left outer join
    users_role_mapping roles13_
    on user0_.id=roles13_.user_id
        left outer join
    users_roles role14_
    on roles13_.role_id=role14_.id
        left outer join
    contact contact15_
    on user0_.internal_contact_id=contact15_.id
where
        user0_.id in (
        select
            user1_.id
        from
            users user1_
                inner join
            contact contact2_
            on user1_.esia_contact_id=contact2_.id
                inner join
            contact contact3_
            on user1_.internal_contact_id=contact3_.id
        where
            (
                    user1_.id in (
                    user0_.id
                    )
                )
          and (
                    lower(contact2_.email) like 'es'
                or lower(contact3_.email) like 'es'
            )
    )