MachForm Community Forums » MachForm 3

1. tinus
   Member

   I have a form with a matrix-choice field; when accessing "entries" or "logic" it gives an error:

   SQL: [672] select A.element_id, A.option_id, (select if(B.element_matrix_parent_id=0,A.option, (select C.option from ap_element_options C where C.element_id=B.element_matrix_parent_id and C.form_id=A.form_id and C.live=1 and C.option_id=A.option_id)) ) 'option_label' from ap_element_options A left join ap_form_elements B on (A.element_id=B.element_id and A Params: 1 Key: Position #0: paramno=0 name=[0] "" is_param=1 param_type=2 Query Failed: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

   the problem seems to be this subquery:
   select C.option from ap_element_options C where C.element_id=B.element_matrix_parent_id and C.form_id=A.form_id and C.live=1 and C.option_id=A.option_id

   which is used in this query:
   select A.element_id, A.option_id, (select if(B.element_matrix_parent_id=0,A.option, (select C.option from ap_element_options C where C.element_id=B.element_matrix_parent_id and C.form_id=A.form_id and C.live=1 and C.option_id=A.option_id))) 'option_label' from ap_element_options A left join ap_form_elements B on (A.element_id=B.element_id and A.form_id=B.form_id) where A.form_id='14149' and A.live=1 and B.element_status=1 and B.element_type='matrix' order by A.element_id,A.option_id asc

   please advise.

   gr.
   tinus

   Posted 12 years ago #

2. tinus
   Member

   adding "limit 1" fixes the error, but I cannot see if it makes sense:
   select A.element_id, A.option_id, (select if(B.element_matrix_parent_id=0,A.option, (select C.option from ap_element_options C where C.element_id=B.element_matrix_parent_id and C.form_id=A.form_id and C.live=1 and C.option_id=A.option_id limit 1))) 'option_label' from ap_element_options A left join ap_form_elements B on (A.element_id=B.element_id and A.form_id=B.form_id) where A.form_id='11620' and A.live=1 and B.element_status=1 and B.element_type='matrix' order by A.element_id,A.option_id asc;

   (different form_id, I know, this is the one that gave the problem)

   Posted 12 years ago #

3. tinus
   Member

   yes. adding "limit 1" to the subquery on line 1768 of includes/entry-functions.php in function "mf_get_columns_meta"
   (machform version 3.4) gets rid of the error and seems to do no harm.

   I also had to add the same to the same subquery in other functions in that file.
   (and also to the same subquery in export_entries.php)

   could you please check?

   Posted 12 years ago #

4. tinus
   Member

   here's the problem (unneeded options filtered out):

   mysql> select * from ap_element_options where form_id=11620 and live=1 and element_id>4 order by element_id, option_id;
   +--------+---------+------------+-----------+----------+------------------------+-------------------+------+
   | aeo_id | form_id | element_id | option_id | position | option | option_is_default | live |
   +--------+---------+------------+-----------+----------+------------------------+-------------------+------+
   | 164 | 11620 | 5 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 203 | 11620 | 5 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 165 | 11620 | 5 | 2 | 2 | mee eens | 0 | 1 |
   | 204 | 11620 | 5 | 2 | 2 | mee eens | 0 | 1 |
   | 166 | 11620 | 5 | 3 | 3 | oneens | 0 | 1 |
   | 205 | 11620 | 5 | 3 | 3 | oneens | 0 | 1 |
   | 206 | 11620 | 5 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 207 | 11620 | 5 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 208 | 11620 | 6 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 209 | 11620 | 6 | 2 | 2 | mee eens | 0 | 1 |
   | 210 | 11620 | 6 | 3 | 3 | oneens | 0 | 1 |
   | 211 | 11620 | 6 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 212 | 11620 | 6 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 213 | 11620 | 7 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 214 | 11620 | 7 | 2 | 2 | mee eens | 0 | 1 |
   | 215 | 11620 | 7 | 3 | 3 | oneens | 0 | 1 |
   | 216 | 11620 | 7 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 217 | 11620 | 7 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 218 | 11620 | 8 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 219 | 11620 | 8 | 2 | 2 | mee eens | 0 | 1 |
   | 220 | 11620 | 8 | 3 | 3 | oneens | 0 | 1 |
   | 221 | 11620 | 8 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 222 | 11620 | 8 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 223 | 11620 | 9 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 224 | 11620 | 9 | 2 | 2 | mee eens | 0 | 1 |
   | 225 | 11620 | 9 | 3 | 3 | oneens | 0 | 1 |
   | 226 | 11620 | 9 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 227 | 11620 | 9 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 228 | 11620 | 10 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 229 | 11620 | 10 | 2 | 2 | mee eens | 0 | 1 |
   | 230 | 11620 | 10 | 3 | 3 | oneens | 0 | 1 |
   | 231 | 11620 | 10 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 232 | 11620 | 10 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 233 | 11620 | 11 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 234 | 11620 | 11 | 2 | 2 | mee eens | 0 | 1 |
   | 235 | 11620 | 11 | 3 | 3 | oneens | 0 | 1 |
   | 236 | 11620 | 11 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 237 | 11620 | 11 | 5 | 5 | niet van toepassing | 0 | 1 |
   | 243 | 11620 | 13 | 1 | 1 | helemaal mee eens | 0 | 1 |
   | 244 | 11620 | 13 | 2 | 2 | mee eens | 0 | 1 |
   | 245 | 11620 | 13 | 3 | 3 | oneens | 0 | 1 |
   | 246 | 11620 | 13 | 4 | 4 | helemaal niet mee eens | 0 | 1 |
   | 247 | 11620 | 13 | 5 | 5 | niet van toepassing | 0 | 1 |
   +--------+---------+------------+-----------+----------+------------------------+-------------------+------+

   Posted 12 years ago #

RSS feed for this topic

Reply

You must log in to post.