MIGX and MIGXdb is very powerful, and can sometimes be a bit difficult to figure out how to get the data back out the way you want it. Especially in the case of nested MIGX TVs, where one TV contains another MIGX TV as one of its fields. This example came from a Slack conversation on the #development channel.
On Slack, josh asked...
Is there a best way to perform a &where on a MIGX snippet call where what I want to test is in a nested MIGX variable? Is it best to query that second tier as a string if possible?
The Solution:
Bruno17 says...
[[migxLoopCollection?
&classname=`modTemplateVarResource`
&where=`{"tmplvarid":"91","value:LIKE":"%\"year\":\"2015\"%"}`
&debug=`1`
]]
This would return a comma-separated list of resource-ids
[[migxLoopCollection?
&classname=`modTemplateVarResource`
&where=`{"tmplvarid":"91","value:LIKE":"%\"year\":\"2015\"%"}`
&tpl=`@CODE:[[+contentid]]`
&outputSeparator=`,`
]]
And this would return both the template variable fields and all of the associated resource fields:
[[migxLoopCollection?
&classname=`modTemplateVarResource`
&joins=`[{"alias":"Resource"}]`
&where=`{"tmplvarid":"91","value:LIKE":"%\"year\":\"2015\"%"}`
&debug=`1`
]]
What's the Story?
A MIGX snippet stores all of its rows of field data in a JSON string which gets stored in the "value" field of a row in the site_tmplvar_contentvalues table in the database. So if one of a MIGX TV's fields is another MIGX TV, you would have a nested JSON array string in that "value" field. Fortunately Bruno17 is genius enough to provide us with a number of useful snippets to take care of sorting through the JSON array strings.
First, tell migxGetLoopCollection what kind of object you are looking for - in this case, you're looking for modTemplateVarResource objects as they are stored in the site_tmplvar_contentvalues table.
&classname=`modTemplateVarResource`
Then we specify which ones we're looking for. In this case, we want values for TV #91, and only the ones that have a "year" field in that JSON array string, and where that "year" field has the value of 2015. It helps to look at the string in the database so you can see how the JSON string is constructed. Since this will be made into a query, make sure to escape the quote marks that are part of the JSON string.
&where=`{"tmplvarid":"91","value:LIKE":"%\"year\":\"2015\"%"}`
It's always a good idea to provide a tpl, whether inline or as a chunk, to format the output of the snippet. This tpl just makes a comma-separated list of the "contentid" from the table, this is the ID of the resource that has that value. The snippet will loop through all of the rows it finds with that value, and apply the tpl to each one in turn.
&tpl=`@CODE:[[+contentid]]` &outputSeparator=`,`
The schema for modTemplateVarResource has an aggregate alias, Resource, to indicate that it is related to the site_content table.
<aggregate alias="Resource" class="modResource" local="contentid" foreign="id" cardinality="one" owner="foreign" />
So using the &joins property will result in not only all of the fields from the site_tmplvar_contentvalues being made available as placeholders, so will all of the fields from the site_content table for each one's associated resource. MODX will take care of creating the correct JOIN query based on the schema.
&joins=`[{"alias":"Resource"}]`