Copy list not working

relsig · Dec 12, 2015

Hello

I need two separate lists/forms to access the same database table. Some of the elements are not doing exactely the same and the filtering of some dropdowns is not equal. Therefore I tried the copy list feature in the lists part of Fabrik.
I assigned new names to the list, the form and all groups (from Abrechnungen to Abrechnungen_Firmenkurse). I checked the form, it was created with the new name. The same with the groups and also all elements were created just fine.

I then tried to look at the data, pushing the show data link in the lists. Unfortunately I get a 500 error and nothing happens anymore. Here is the error message

Fabrik has generated an incorrect query for the list Abrechnungen Firmenkurse: <br /><br /><pre>Unknown column '2' in 'order clause' SQL=SELECT DISTINCT `svbg_abrechnungen`.`id` AS __pk_val0 FROM `svbg_abrechnungen` LEFT JOIN `jos_users` AS `jos_users` ON `jos_users`.`id` = `svbg_abrechnungen`.`joomla_user` LEFT JOIN `jos_eb_events` AS `jos_eb_events` ON `jos_eb_events`.`id` = `svbg_abrechnungen`.`veranstaltung_waehlen` ORDER BY `2` ASC</pre>

There is no column 2, at least not one created by myself. Since there are the users tables from Joomla mentioned I broke the link the the original user element and then checked the settings in the new user element. There isn't anything abvious.

Can you give me a hint where to search for or was I doing something wrong?

Thank you

Roger

troester · Dec 12, 2015

Can you check your element "veranstaltung_w?hlen"? Is it a dbjoin with some "order by" set (maybe directly in data-where; or in element's "List view"/Filters settings)?

relsig · Dec 12, 2015

Original dbjoin Element

WHERE
jos_eb_events.id IN (
SELECT
jos_eb_events.id
FROM
jos_eb_events
RIGHT JOIN jos_eb_event_categories ON jos_eb_events.id = jos_eb_event_categories.event_id
WHERE
jos_eb_events.event_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND jos_eb_events.event_date < DATE_ADD(CURDATE(), INTERVAL 180 DAY)
AND jos_eb_event_categories.category_id <= 5
)
ORDER BY jos_eb_events.event_date

copied dbjoin element

WHERE
jos_eb_events.id IN (
SELECT
jos_eb_events.id
FROM
jos_eb_events
RIGHT JOIN jos_eb_event_categories ON jos_eb_events.id = jos_eb_event_categories.event_id
WHERE
jos_eb_events.event_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND jos_eb_events.event_date < DATE_ADD(CURDATE(), INTERVAL 180 DAY)
AND jos_eb_event_categories.category_id = 13
)
ORDER BY jos_eb_events.event_date

The original one is working fine the second one creates an error. I checked all the settings of the two elements, they absolutely identical.

troester · Dec 12, 2015

Do you have a CDD in your list?
Do you have an element with id=2?
Such column "numbers" may refer to element ids if something in the setup went wrong and the element name is not found.
E.g. e list joined to an unpublished elements or a CDD watching a non-existing element...

relsig · Dec 13, 2015

CDD = cascading drop dows? No i don't have such an Element. The Rest i will have to check

relsig · Dec 13, 2015

Did some more tests. Disabled all elements except the id element on the copied list, disabled all groups. Then the error message looks different

Unknown column '2' in 'order clause' SQL=SELECT DISTINCT `svbg_abrechnungen`.`id` AS __pk_val0 FROM `svbg_abrechnungen`
ORDER BY `2` ASC

I have no idea that __pk_valo is and where it comes from.

Also set the Joomla mode to debug and got out the message in the annexe, see

Made a check of the database structure for the svbg_abrechnungen. See it here

There is no column 2. Waste basket is empty, so nothing there as well.

Did a new copy with a totaly different name for the list, exactely same error messages as shown above.

Tried to do a new list to the same db table, which is not possible.

No idea where to look for anymore, hope you can do something with the information above. Can give you administrator access if that helps.

cheesegrits · Dec 13, 2015

Which method of copying did you use? Did you "Save as Copy" when editing the original, or select the original in the main Lists tab, and use the "Copy" button at the top?

-- hugh

cheesegrits · Dec 13, 2015

BTW, the __pk_valX are aliases we use for identifying PK's, so when you are joining lists, we'll have a __pk_val0 (for the main) list, __pk_val1 (for the first join), etc.

The reason you are getting the "ORDER BY '2'" is probably you have unpublished the element you are using as the ORDER BY.

-- hugh

relsig · Dec 13, 2015

cheesegrits said:
Which method of copying did you use? Did you "Save as Copy" when editing the original, or select the original in the main Lists tab, and use the "Copy" button at the top?

-- hugh

The second one. Select the original then click copy.

relsig · Dec 13, 2015

cheesegrits said:
BTW, the __pk_valX are aliases we use for identifying PK's, so when you are joining lists, we'll have a __pk_val0 (for the main) list, __pk_val1 (for the first join), etc.

The reason you are getting the "ORDER BY '2'" is probably you have unpublished the element you are using as the ORDER BY.

-- hugh

I had the error message right after the copying before unpublishing the elements, so this can't be the problem. Just did the check, published all resources again, same problem occurs.

relsig · Dec 13, 2015

It seems to me I found the solution. It actually has to do how you copy the list. As you suspected above. Opening the original list and saving it under another name produced a working copied list.

Will have to do an extended test tomorrow, will keep you posted.

cheesegrits · Dec 13, 2015

Hmmm, usually it's the other way round, using "Save as Copy" produces problems. I was looking at that a few weeks ago, it's not something I'd ever even used, I always do the "Copy" from the main list tab. The "Save as Copy" definitely has issues if you are copying a list which has joins, which someone else reported recently.

-- hugh

relsig · Jan 2, 2016

With version 3.4 it works, selecting the list and then hit the copy button.

Thanks

relsig · Jan 5, 2016

It seems that the one copy I did above was just luck. Trying to do another copy creates the same error. Here is the error message, probably pretty much the same as above

Code:

Fabrik has generated an incorrect query for the list Abrechnungen Termine:

Unknown column '2' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `svbg_abrechnungen`.`Erstellungsdatum` AS `svbg_abrechnungen___Erstellungsdatum`,
`svbg_abrechnungen`.`Erstellungsdatum` AS `svbg_abrechnungen___Erstellungsdatum_raw`,
`svbg_abrechnungen`.`id` AS `svbg_abrechnungen___id`,
`svbg_abrechnungen`.`id` AS `svbg_abrechnungen___id_raw`,
`svbg_abrechnungen`.`abgerechnet` AS `svbg_abrechnungen___abgerechnet`,
`svbg_abrechnungen`.`abgerechnet` AS `svbg_abrechnungen___abgerechnet_raw`,
`svbg_abrechnungen`.`joomla_user` AS `svbg_abrechnungen___joomla_user_raw`,
`jos_users`.`name` AS `svbg_abrechnungen___joomla_user`,
`svbg_abrechnungen`.`abrechnungstyp` AS `svbg_abrechnungen___abrechnungstyp`,
`svbg_abrechnungen`.`abrechnungstyp` AS `svbg_abrechnungen___abrechnungstyp_raw`,
`svbg_abrechnungen`.`abrechnung_abgeschlossen_datum` AS `svbg_abrechnungen___abrechnung_abgeschlossen_datum`,
`svbg_abrechnungen`.`abrechnung_abgeschlossen_datum` AS `svbg_abrechnungen___abrechnung_abgeschlossen_datum_raw`,
`svbg_abrechnungen`.`veranstaltung_waehlen` AS `svbg_abrechnungen___veranstaltung_waehlen_raw`,
CONCAT_WS('', DATE_FORMAT(jos_eb_events.event_date, '%d.%m.%Y'),' ',jos_eb_events.title) AS `svbg_abrechnungen___veranstaltung_waehlen`,
`svbg_abrechnungen`.`dauer` AS `svbg_abrechnungen___dauer`,
`svbg_abrechnungen`.`dauer` AS `svbg_abrechnungen___dauer_raw`,
`svbg_abrechnungen`.`teilnehmer_anzahl` AS `svbg_abrechnungen___teilnehmer_anzahl`,
`svbg_abrechnungen`.`teilnehmer_anzahl` AS `svbg_abrechnungen___teilnehmer_anzahl_raw`,
`svbg_abrechnungen`.`einnahmen_veranstaltung` AS `svbg_abrechnungen___einnahmen_veranstaltung`,
`svbg_abrechnungen`.`einnahmen_veranstaltung` AS `svbg_abrechnungen___einnahmen_veranstaltung_raw`,
`svbg_abrechnungen`.`kl_1_name` AS `svbg_abrechnungen___kl_1_name`,
`svbg_abrechnungen`.`kl_1_name` AS `svbg_abrechnungen___kl_1_name_raw`,
`svbg_abrechnungen`.`kl_1_einsatzdauer` AS `svbg_abrechnungen___kl_1_einsatzdauer`,
`svbg_abrechnungen`.`kl_1_einsatzdauer` AS `svbg_abrechnungen___kl_1_einsatzdauer_raw`,
`svbg_abrechnungen`.`kl_1_entschaedigung` AS `svbg_abrechnungen___kl_1_entschaedigung`,
`svbg_abrechnungen`.`kl_1_entschaedigung` AS `svbg_abrechnungen___kl_1_entschaedigung_raw`,
`svbg_abrechnungen`.`kl_1_geld_erhalten` AS `svbg_abrechnungen___kl_1_geld_erhalten`,
`svbg_abrechnungen`.`kl_1_geld_erhalten` AS `svbg_abrechnungen___kl_1_geld_erhalten_raw`,
`svbg_abrechnungen`.`ph1` AS `svbg_abrechnungen___ph1`,
`svbg_abrechnungen`.`ph1` AS `svbg_abrechnungen___ph1_raw`,
`svbg_abrechnungen`.`ph2` AS `svbg_abrechnungen___ph2`,
`svbg_abrechnungen`.`ph2` AS `svbg_abrechnungen___ph2_raw`,
`svbg_abrechnungen`.`veranstaltungspauschale` AS `svbg_abrechnungen___veranstaltungspauschale`,
`svbg_abrechnungen`.`veranstaltungspauschale` AS `svbg_abrechnungen___veranstaltungspauschale_raw`,
`svbg_abrechnungen`.`ph3` AS `svbg_abrechnungen___ph3`,
`svbg_abrechnungen`.`ph3` AS `svbg_abrechnungen___ph3_raw`,
`svbg_abrechnungen`.`ph4` AS `svbg_abrechnungen___ph4`,
`svbg_abrechnungen`.`ph4` AS `svbg_abrechnungen___ph4_raw`,
`svbg_abrechnungen`.`diverses_beschrieb` AS `svbg_abrechnungen___diverses_beschrieb`,
`svbg_abrechnungen`.`diverses_beschrieb` AS `svbg_abrechnungen___diverses_beschrieb_raw`,
`svbg_abrechnungen`.`diverses_betrag` AS `svbg_abrechnungen___diverses_betrag`,
`svbg_abrechnungen`.`diverses_betrag` AS `svbg_abrechnungen___diverses_betrag_raw`,
`svbg_abrechnungen`.`ph5` AS `svbg_abrechnungen___ph5`,
`svbg_abrechnungen`.`ph5` AS `svbg_abrechnungen___ph5_raw`,
`svbg_abrechnungen`.`kl_2_name` AS `svbg_abrechnungen___kl_2_name`,
`svbg_abrechnungen`.`kl_2_name` AS `svbg_abrechnungen___kl_2_name_raw`,
`svbg_abrechnungen`.`kl_2_einsatzdauer` AS `svbg_abrechnungen___kl_2_einsatzdauer`,
`svbg_abrechnungen`.`kl_2_einsatzdauer` AS `svbg_abrechnungen___kl_2_einsatzdauer_raw`,
`svbg_abrechnungen`.`kl_2_entschaedigung` AS `svbg_abrechnungen___kl_2_entschaedigung`,
`svbg_abrechnungen`.`kl_2_entschaedigung` AS `svbg_abrechnungen___kl_2_entschaedigung_raw`,
`svbg_abrechnungen`.`kl_2_geld_erhalten` AS `svbg_abrechnungen___kl_2_geld_erhalten`,
`svbg_abrechnungen`.`kl_2_geld_erhalten` AS `svbg_abrechnungen___kl_2_geld_erhalten_raw`,
`svbg_abrechnungen`.`helfer_name` AS `svbg_abrechnungen___helfer_name`,
`svbg_abrechnungen`.`helfer_name` AS `svbg_abrechnungen___helfer_name_raw`,
`svbg_abrechnungen`.`helfer_einsatzdauer` AS `svbg_abrechnungen___helfer_einsatzdauer`,
`svbg_abrechnungen`.`helfer_einsatzdauer` AS `svbg_abrechnungen___helfer_einsatzdauer_raw`,
`svbg_abrechnungen`.`helfer_entschaedigung` AS `svbg_abrechnungen___helfer_entschaedigung`,
`svbg_abrechnungen`.`helfer_entschaedigung` AS `svbg_abrechnungen___helfer_entschaedigung_raw`,
`svbg_abrechnungen`.`helfer_geld_erhalten` AS `svbg_abrechnungen___helfer_geld_erhalten`,
`svbg_abrechnungen`.`helfer_geld_erhalten` AS `svbg_abrechnungen___helfer_geld_erhalten_raw`,
`svbg_abrechnungen`.`guthaben_kl_1` AS `svbg_abrechnungen___guthaben_kl_1`,
`svbg_abrechnungen`.`guthaben_kl_1` AS `svbg_abrechnungen___guthaben_kl_1_raw`,
`svbg_abrechnungen`.`abzuege_total` AS `svbg_abrechnungen___abzuege_total`,
`svbg_abrechnungen`.`abzuege_total` AS `svbg_abrechnungen___abzuege_total_raw`,
`svbg_abrechnungen`.`guthaben_kl_2` AS `svbg_abrechnungen___guthaben_kl_2`,
`svbg_abrechnungen`.`guthaben_kl_2` AS `svbg_abrechnungen___guthaben_kl_2_raw`,
`svbg_abrechnungen`.`gesamttotal` AS `svbg_abrechnungen___gesamttotal`,
`svbg_abrechnungen`.`gesamttotal` AS `svbg_abrechnungen___gesamttotal_raw`,
`svbg_abrechnungen`.`guthaben_helfer` AS `svbg_abrechnungen___guthaben_helfer`,
`svbg_abrechnungen`.`guthaben_helfer` AS `svbg_abrechnungen___guthaben_helfer_raw`,
`svbg_abrechnungen`.`bemerkungen` AS `svbg_abrechnungen___bemerkungen`,
`svbg_abrechnungen`.`bemerkungen` AS `svbg_abrechnungen___bemerkungen_raw`,
(SELECT GROUP_CONCAT(quittungen_hinzufuegen SEPARATOR '//..*..//') FROM svbg_abrechnungen_repeat_quittungen_hinzufuegen WHERE parent_id = `svbg_abrechnungen`.`id`) AS `svbg_abrechnungen___quittungen_hinzufuegen`,
(SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM svbg_abrechnungen_repeat_quittungen_hinzufuegen WHERE parent_id = `svbg_abrechnungen`.`id`) AS `svbg_abrechnungen___quittungen_hinzufuegen_raw`,
(SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM svbg_abrechnungen_repeat_quittungen_hinzufuegen WHERE parent_id = `svbg_abrechnungen`.`id`) AS `svbg_abrechnungen___quittungen_hinzufuegen___params`,
`svbg_abrechnungen`.`id` AS slug
, `svbg_abrechnungen`.`id` AS `__pk_val`

FROM `svbg_abrechnungen`
LEFT JOIN `jos_users` AS `jos_users` ON `jos_users`.`id` = `svbg_abrechnungen`.`joomla_user`
LEFT JOIN `jos_eb_events` AS `jos_eb_events` ON `jos_eb_events`.`id` = `svbg_abrechnungen`.`veranstaltung_waehlen`
ORDER BY `2` DESC,`svbg_abrechnungen`.`Erstellungsdatum` ASC LIMIT 0, 30

I can't find the problem. I searched all the Fabrik records in the database and compared them, there is nothing different to the two working lists.
Turned on the error reporting this just shows me that Fabrik throws an error. See Picture error 1.

The number 2 as mentioned in the error message is used in the dropdown `svbg_abrechnungen`.`kl_2_name`
Also a couple of times in SQL queries like `svbg_abrechnungen`.`teilnehmer_anzahl` and `svbg_abrechnungen`.`einnahmen_veranstaltung`.

I could do the export of the content type of a working list and one from the not working list if that would help.

Any advice would be greatly appreciated.

cheesegrits · Jan 6, 2016

Which site is this on? I've tried to replicate it with no luck. I've copied about a dozen lists of varying complexity, all work just fine. So I'll need to get at your site.

-- hugh

relsig · Jan 6, 2016

Sent you a PM

cheesegrits · Jan 6, 2016

OK, it's working now. As the order by seemed to be the problem, and there was no order by set on that list, I just set it to something, saved and edited, then unset it again.

-- hugh

relsig · Jan 6, 2016

Thank you very much for your fantastic support. Which order by do you mean?

relsig · Jan 10, 2016

Hugh,

I did a couple of copies always adjusting the sorting inside the list. This seemed to work until I wanted to do the last copy I need. Now I have the problem again. Could you tell me which "order by" you meant, is it the one I mentioned.

Would you mind having a look at the list with the id 10? "Abrechnungen Diverses". You should have the login credentials in your PM.

Thank you

cheesegrits · Jan 11, 2016

Which site? I know you sent a PM, but I delete all my PM's on a regular basis, as they have a lot of passwords in them. Best to use My Sites.

-- hugh

Copy list not working

Member

Administrator

Member

Administrator

Member

Member

Support Gopher

Support Gopher

Member

Member

Member

Support Gopher

Member

Member

Attachments

Support Gopher

Member

Support Gopher

Member

Member

Support Gopher

Useful links