We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered migxLoopCollection filtering problem

    • 37464
    • 16 Posts
    th0car Reply #1, 11 years, 4 months ago
    This chunk...
    [[!migxLoopCollection?
&classname=`MikAssignment`
&packageName=`mik_organization`
&limit=`2`
&debug = `1`
&joins=`[{"alias":"Leader"},{"alias":"Leaderrole"}]`
&tpl=`@CODE:<div class="wp-block article grid bb">
<p>
[[+Leader_fullname]], [[+Leaderrole_level_id]]
</p>`
]]

    ...returns this expected result (Both of the alias fields are generated) 
    Bertil Bertilsson, 2
Adam Adamsson, 1

    ... and this debug_code
    SELECT `MikAssignment`.`id`, `MikAssignment`.`team`, `MikAssignment`.`leader_id`, `MikAssignment`.`team_id`, `MikAssignment`.`leaderrole_id`, `Leader`.`id` AS `Leader_id`, `Leader`.`fullname` AS `Leader_fullname`, `Leader`.`email` AS `Leader_email`, `Leader`.`phone` AS `Leader_phone`, `Leaderrole`.`id` AS `Leaderrole_id`, `Leaderrole`.`name` AS `Leaderrole_name`, `Leaderrole`.`long_name` AS `Leaderrole_long_name`, `Leaderrole`.`level_id` AS `Leaderrole_level_id`, `Leaderrole`.`section_id` AS `Leaderrole_section_id` FROM `modx_mik_assignments` AS `MikAssignment` LEFT JOIN `modx_mik_leaders` `Leader` ON `MikAssignment`.`leader_id` = `Leader`.`id` LEFT JOIN `modx_mik_org_roles` `Leaderrole` ON `MikAssignment`.`leaderrole_id` = `Leaderrole`.`id` LIMIT 2


    So everything is fine this far. However . if I want to filter by +Leaderrole_level_id like this:

    [[!migxLoopCollection?
&classname=`MikAssignment`
&packageName=`mik_organization`
&limit=`2`
&debug = `1`
&joins=`[{"alias":"Leader"},{"alias":"Leaderrole"}]`
&where=`{"Leaderrole_level_id":"2"}`
&tpl=`@CODE:<div class="wp-block article grid bb">
<p>
[[+Leader_fullname]], [[+Leaderrole_level_id]]
    </p>
</div>`
]]


    I get no results and the following debug message 
    SELECT `MikAssignment`.`id`, `MikAssignment`.`team`, `MikAssignment`.`leader_id`, `MikAssignment`.`team_id`, `MikAssignment`.`leaderrole_id`, `Leader`.`id` AS `Leader_id`, `Leader`.`fullname` AS `Leader_fullname`, `Leader`.`email` AS `Leader_email`, `Leader`.`phone` AS `Leader_phone`, `Leaderrole`.`id` AS `Leaderrole_id`, `Leaderrole`.`name` AS `Leaderrole_name`, `Leaderrole`.`long_name` AS `Leaderrole_long_name`, `Leaderrole`.`level_id` AS `Leaderrole_level_id`, `Leaderrole`.`section_id` AS `Leaderrole_section_id` FROM `modx_mik_assignments` AS `MikAssignment` LEFT JOIN `modx_mik_leaders` `Leader` ON `MikAssignment`.`leader_id` = `Leader`.`id` LEFT JOIN `modx_mik_org_roles` `Leaderrole` ON `MikAssignment`.`leaderrole_id` = `Leaderrole`.`id` WHERE `MikAssignment`.`Leaderrole_level_id` = '2' LIMIT 2


    Any clues on what am I doing wrong here?

    • Wrong syntax in some part of the chunk?
    • Alias fields can't be used in WHERE-clause? (in that case what is best practice to use instead?)
    • something else?

    This question has been answered by Bruno17. See the first response.

    • discuss.answer
      • 4172
      • 5,888 Posts
      Bruno17 Reply #2, 11 years, 4 months ago
      replace the underscore with a dot


      &where=`{"Leaderrole.level_id":"2"}`
        -------------------------------

        you can buy me a beer, if you like MIGX

        http://webcmsolutions.de/migx.html

        Thanks!
        • 37464
        • 16 Posts
        th0car Reply #3, 11 years, 4 months ago
        Long question, short answer. Works now - thank you!