Unknown column in 'field list' error

[pundip]

I am getting the error ?Unknown column 'batch_number' in 'field list'? when I try to update a record that is part of a list with two joins. The database join element batch_number is using {thistable} for concats. The strange part is that the record ends up getting updated so I am not sure what the error is stopping. I am including a screenshot of the list join config but I suspect I will have to get some to look at the site.

I have recreated the issue on a test site but I am not sure what the process is to share credentials etc.

 

[cheesegrits]

If you remove the CONCAT, and just specify a field to use in the Label dropdown, does the error go away?

In other words, are you sure it's the use of {thistable} in the CONCAT causing the problem? I suspect it may be, because it looks like your 'batch' join is the "wrong" way round (FK on main table pointing to PK on the joined table). Which isn't really "wrong", it's a valid join, but initially all our join code was written with the assumption of an FK on the joined table pointing back to the PK of the main table, and we gradually improved the code to understand going the other way. Bu the code that handles what {thistable} gets replaced with in a CONCAT may get confused by that.

You could also try removing the {thistable}, and just use the field name, which may work (if it's unambiguous).

-- hugh

 

[pundip]

Hi Hugh,

Removing {thistable} in the CONCAT does fix the problem. I am looking at the ways to make the join the right way. I have find a way to storing the FK (PK of batch table) on the main fabrik application table.

 

[cheesegrits]

It's not really "wrong", it's more that there may still be a few places in Fabrik's code that make assumptions about which "end" of the join is the parent and which is the child. To put it bluntly, bugs.

You may run into problems if you add a second join to the same table, which is when {thistable} is needed, because then we have to assign to aliases to the joins, "LEFT JOIN foo AS foo_1", so `foo` will no longer work.

-- hugh