Urgent: how to concat label for a cdd element renderd as checkbox

[sales2010]

Hello,

This is the code i used to concat the label before last update from Github (this morning):

(SELECT extraoptiune
FROM contracte_extraoptiuni
WHERE contracte_extraoptiuni.id = {thistable}.extraoptiuni_clasa)

and now i get this error:

1054 Unknown column 'contracte_clase_repeat_extraoptiuni_clasa.extraoptiuni_clasa' in 'where clause'.........
 
  (SELECT GROUP_CONCAT(CONCAT((SELECT extraoptiune FROM contracte_extraoptiuni WHERE contracte_extraoptiuni.id = contracte_clase_repeat_extraoptiuni_clasa.extraoptiuni_clasa)) SEPARATOR '//..*..//')

what could be the problem?

Note that the problem appear only when i edit the information, run fine for new records

 

[sales2010]

more tests:
Here i get the message "Subquery return more than 1 row"

SELECT a.extraoptiune
FROM contracte_extraoptiuni a
LEFT JOIN contracte_clase_repeat_extraoptiuni_clasa b ON a.id = b.extraoptiuni_clasa
GROUP BY a.extraoptiune

same message using "IN" instead of "=" + specifying the table name instead of using the placeholder+ adding the table to the "SELECT" statement:

SELECT contracte_extraoptiuni.extraoptiune
FROM contracte_extraoptiuni, contracte_clase_repeat_extraoptiuni_clasa
WHERE contracte_extraoptiuni.id IN( contracte_clase_repeat_extraoptiuni_clasa.extraoptiuni_clasa)

The query run fine for a single selection, but not for multiple selections.

 

[sales2010]

friendly bump...

 

[sales2010]

Rob, Hugh, Troester, can you help me here, please?

 

[sales2010]

Come on, guys, as i said in title, it's urgent..can you help me,please?

 

[rob]

Here i get the message "Subquery return more than 1 row"

Well thats the clue , your subquery must return only one record, so ensure that you join is not creating more than one result, and that your where query is precise enough to only return one row.


Also I don't think the group by should be the query, as you should be returning one row only grouping a single row doesn't make sense I think.

 

[sales2010]

Thank you, Rob, but i don't understand how to do this, as i said in first post, the "original" query stop working after update. Is not ok to do this kind of query?

(select element from table where table.id = {thistable}.element)

All i want is to get the label of the element instead of value..

 

[rob]

I don't think its the update that affects this, but rather (probably) the fact that more data has been added to the database and thus the subquery is now returning more than one row of results.

Does the error occur when you edit a record, where only one value has been selected for the cdd?

Are you sure that the "table.id" values are all unique?

 

[sales2010]

It happen no matter if there is a value, multiple values or no value ticked in cdd for existing record. Work fine when i start to add a new record, the concat query return the correct labels.

Yes, the table.id is the pk of the table created by Fabrik for each new list.

 

[cheesegrits]

Which query are you currently using? The one from your original post, or one of the ones in subsequent posts which use joins?

I'm testing now, to see if this is an issue specific to CDD's as checkboxes.

-- hugh

 

[cheesegrits]

BTW, do you get the same error, or a different error, or no error, when displaying the list?

I'm having problems coming up with a query which works in both list and form view for CDD's rendered as checkboxes.

-- hugh

 

[sales2010]

No error in list, just in form and only when i want to edit a existing records. I use the first query to concat label:

(SELECT extraoptiune
FROM contracte_extraoptiuni
WHERE contracte_extraoptiuni.id = {thistable}.extraoptiuni_clasa)

 

[cheesegrits]

I can see why this is happening, but it's not going to be a trivial fix.

Working on it.

-- hugh

 

[cheesegrits]

Can you try without the {thistable}?

(SELECT extraoptiune
FROM contracte_extraoptiuni
WHERE contracte_extraoptiuni.id = extraoptiuni_clasa)

That may or may not work, depending whether you have more than one join to that table.

The problem is hard to describe, but basically we have to mess with {thistable} if the CONCAT is on a multi-select join, because "this table" is different ... multi-select joins create a many-to-many table automagically.

That gets handled correctly if you just use {thistable} in a "simple" concat, like "{thistable}.lastname, ' - ', {thistable.firstname}", but once it's inside a subquery, things get ... complicated.

-- hugh

 

[sales2010]

thank you, hugh, seems to work fine now after i removed the placeholder "{thistable}".

 

[cheesegrits]

OK. Note, if you add a second or subsequent join (element or list join) to the same table, that query may quit working. Just be aware of that. It's some fairly complex code. I could probably fix it if it does break with more than one join to the same table, but it'd be non-trivial.

-- hugh