I found some strange behavior when joining a table to the model() in a query.
Suppose I have a global table with values, and objects in my model that have those values on a specific label. What I would like to do is join the table to the objects in the model, so as to find the objects with the label with the matching value.
Please see the attached model join-table-to-model.fsm for my attempts.
First I tried option A:
query( "SELECT [Objects].[ID], $2 AS Object FROM Objects, $1 AS Model", model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );
Strangely, the references I get in the second column point to the rows of my Objects table. It seems like $iter does not loop through $1, which is model(), as documented, but rather through the rows of the first source, which is the global table Objects. However, this is not quite right, as changing $iter(1) to $iter(2) does not fix the problem - I only get references to /Tools instead.
Just for fun, I tired option B, where all I did was switch the sources so model() becomes the first source:
query( "SELECT [Objects].[ID], $2 AS Object, $3 AS ObjectLabel FROM $1 AS Model, Objects", model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );
This already looks better: I get the Cartesian product of the rows in my table and the objects in my model, with the correct label value in the third column (where set).
So I thought I was done: only need to make it into a proper join by adding a WHERE clause:
query( "SELECT [Objects].[ID], $2 AS Object, $3 AS ObjectLabel FROM $1 AS Model, Objects WHERE $3 = [Objects].[ID]", model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );
But suddenly I get only one match! It seems like the $3 in the WHERE clause is not evaluated properly, because when I use its alias:
query( "SELECT [Objects].[ID], $2 AS Object, $3 AS ObjectLabel FROM $1 AS Model, Objects WHERE ObjectLabel = [Objects].[ID]", model(), $iter( 1 ), getlabel( $iter( 1 ), "id" ) );
I get the expected result.
Finally, in my actual model I ran into a problem when I dumped the query to a bundle instead of a table: it made all the fields numeric so all my string labels turned into zeroes.
