Field as subquery with CONACT_WS renders wrong

Rappi

Error messages shown:

SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 'concat_ws

Description of problem:

I am trying to create a field by using a subquery where i want to group_concatenated two concatenated columns. The problem is that it seems that the library is rendering it wrongly.

This is the expected query which gives a correct result:

SELECT (SELECT GROUP_CONCAT(CONCAT_WS(": ", Name, Datum) ORDER BY tm_impfungentier.Datum SEPARATOR ", ") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id) as Impfungen FROM tm_tiere

This is the query rendered by the library (returned by the editor in debug mode):

SELECT (SELECT GROUP_CONCAT(CONCAT_WS(": " as '(SELECT GROUP_CONCAT(CONCAT_WS(": "', Name as 'Name', Datum) ORDER BY Datum) FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id) as 'Datum) ORDER BY Datum) FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id)' FROM tm_tiere

It is somehow duplicating the SELECT part of the subquery. Does anybody have a explanation for that?

Thanks!

colin

Could you please post the code you use to generate that query,

Colin

Rappi

Sure, how dumb to forget that sorry. Here is the field definition snippet of the editor:

$editor->fields(
        Field::inst( 'tm_tiere.id' ),
        ...
                // Lot of additional field definitions but probably not related to the issue
                ....
                // This is the definition causing the problem
        Field::inst('(SELECT GROUP_CONCAT(CONCAT_WS(": ", Name, Datum) ORDER BY tm_impfungentier.Datum SEPARATOR ", ") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id)', 'Impfungen')
    );

Rappi

@colin were you able to have a look at the issue? Thanks a lot!

allan

Sorry - Colin asked me to take a look at it and I misplaced the tab!

What version of the Editor PHP libraries are you using?

I've just tried it, and code the following which is a little different from your own:

    (SELECT GROUP_CONCAT(CONCAT_WS(\": \",Name,Datum) ORDER BY tm_impfungentier.Datum SEPARATOR \",\") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id) as '(SELECT GROUP_CONCAT(CONCAT_WS(\": \",Name,Datum) ORDER BY tm_impfungentier.Datum SEPARATOR \",\") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id)'
FROM `datatables_demo`

I don't have your database tables so I get an error, but I think that looks like it should work (if a very ugly alias!).

Allan

Rappi

Hi @allan, thanks a lot for looking into this issue. Attached you'll find the versions i am using. I tried your definition but it did not work either. I dont really understand why you are aliasing like that.

Maybe you could double check if the version should work and then it would be great if you include the whole field::inst declaration into a snippet so i can try it out.

Thanks so much!

allan

I dont really understand why you are aliasing like that.

It is just using the string passed in as the value to read from the database. Otherwise I need to map it some other way - which is certainly possible, but this was the easiest option (although it might cause issues for more complex strings like this possibly).

Regarding the versions - that is the client-side versions. Could you have a look in the Editor.php file you have for the version string there?

Allan

Rappi

The php library version is 1.6.1. I think it would help a lot if you could provide the whole Field instance definition.

Thanks a lot!

allan

The php library version is 1.6.1.

Yup - you'll want to update that .

I think it would help a lot if you could provide the whole Field instance definition.

You mean the one I used in my test? It was just the same as what you have above.

Allan

Rappi

Hi @allan, thanks so much, updating the lib to the latest version fixed that issue. Now i have another little issue which is about date formatting:

Field::inst('(SELECT GROUP_CONCAT(CONCAT_WS(": ", Name, DATE_FORMAT(Datum, "%d.%m")) ORDER BY tm_impfungentier.Datum SEPARATOR "<br />") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id)', 'Impfungen')

This problem is very weird, basically what i found out is: as long as i am using only two variables in the date format string it works, but if i want to add a third one (year) it returns null for the whole field.

I tested the same query directly with SQL and it worked so i suppose the problems lays in the lib. Can you confirm?

Thanks!

allan

What is the JSON response from the server when you run it with three parameters please?

Allan

Rappi

It is the normal valid response with proper data but that specific field is "null".

allan

Sorry - I was actually after the debug information. Can you show me the full JSON please?

Rappi

Hi @allan, here you go. It is just the debug property because the other parts could contain sensitive information, sorry. If this is still not enough to pinpoint the problem i need to check it.

[
  {
    "query": "SELECT COUNT( `tm_tiere`.`id` ) as `cnt` FROM  `tm_tiere` LEFT JOIN `tm_adressen` ON `tm_adressen`.`id` = `tm_tiere`.`Besitzer`  LEFT JOIN `tm_uc_users` ON `tm_uc_users`.`id` = `tm_tiere`.`Betreuer`  LEFT JOIN `tm_tierart` ON `tm_tierart`.`id` = `tm_tiere`.`Tierart`  LEFT JOIN `tm_aufenthaltsort` ON `tm_aufenthaltsort`.`id` = `tm_tiere`.`Aufenthaltsort`  LEFT JOIN `tm_pflegestellen` ON `tm_pflegestellen`.`id` = `tm_tiere`.`Pflegestelle`  LEFT JOIN `tm_adoptanten` ON `tm_adoptanten`.`id` = `tm_tiere`.`Adoptant`  LEFT JOIN `tm_arzt` ON `tm_arzt`.`id` = `tm_tiere`.`Tierarzt` WHERE `tm_tiere`.`lfdnr` != :where_0 AND  `tm_tiere`.`Vermitteltdatum` = :where_1 AND  `tm_tiere`.`Adoptant` = :where_2 AND  `tm_tiere`.`Geloescht` = :where_3 AND  `tm_tiere`.`Verstorben` = :where_4 AND  `tm_tiere`.`Gestorben` = :where_5 ",
    "bindings": [
      {
        "name": ":where_0",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_1",
        "value": "0000-00-00",
        "type": null
      },
      {
        "name": ":where_2",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_3",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_4",
        "value": "0000-00-00",
        "type": null
      },
      {
        "name": ":where_5",
        "value": 0,
        "type": null
      }
    ]
  },
  {
    "query": "SELECT COUNT( `tm_tiere`.`id` ) as `cnt` FROM  `tm_tiere` LEFT JOIN `tm_adressen` ON `tm_adressen`.`id` = `tm_tiere`.`Besitzer`  LEFT JOIN `tm_uc_users` ON `tm_uc_users`.`id` = `tm_tiere`.`Betreuer`  LEFT JOIN `tm_tierart` ON `tm_tierart`.`id` = `tm_tiere`.`Tierart`  LEFT JOIN `tm_aufenthaltsort` ON `tm_aufenthaltsort`.`id` = `tm_tiere`.`Aufenthaltsort`  LEFT JOIN `tm_pflegestellen` ON `tm_pflegestellen`.`id` = `tm_tiere`.`Pflegestelle`  LEFT JOIN `tm_adoptanten` ON `tm_adoptanten`.`id` = `tm_tiere`.`Adoptant`  LEFT JOIN `tm_arzt` ON `tm_arzt`.`id` = `tm_tiere`.`Tierarzt` WHERE `tm_tiere`.`lfdnr` != :where_0 AND  `tm_tiere`.`Vermitteltdatum` = :where_1 AND  `tm_tiere`.`Adoptant` = :where_2 AND  `tm_tiere`.`Geloescht` = :where_3 AND  `tm_tiere`.`Verstorben` = :where_4 AND  `tm_tiere`.`Gestorben` = :where_5 ",
    "bindings": [
      {
        "name": ":where_0",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_1",
        "value": "0000-00-00",
        "type": null
      },
      {
        "name": ":where_2",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_3",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_4",
        "value": "0000-00-00",
        "type": null
      },
      {
        "name": ":where_5",
        "value": 0,
        "type": null
      }
    ]
  },
  {
    "query": "SELECT  `tm_tiere`.`id` as 'tm_tiere.id', `tm_tiere`.`lfdnr` as 'tm_tiere.lfdnr', `tm_tiere`.`Geloescht` as 'tm_tiere.Geloescht', `tm_tiere`.`LGrund` as 'tm_tiere.LGrund', `tm_tiere`.`Name` as 'tm_tiere.Name', `tm_tiere`.`InternNr` as 'tm_tiere.InternNr', `tm_tiere`.`Aufnahmedatum` as 'tm_tiere.Aufnahmedatum', `tm_tiere`.`Aufnahmegrund` as 'tm_tiere.Aufnahmegrund', `tm_tiere`.`Woher` as 'tm_tiere.Woher', `tm_tiere`.`Tierart` as 'tm_tiere.Tierart', `tm_tierart`.`Art` as 'tm_tierart.Art', `tm_tiere`.`Rasse` as 'tm_tiere.Rasse', `tm_tiere`.`Merkmale` as 'tm_tiere.Merkmale', `tm_tiere`.`Geburtsdatum` as 'tm_tiere.Geburtsdatum', `tm_tiere`.`Age` as 'tm_tiere.Age', `tm_tiere`.`Geschlecht` as 'tm_tiere.Geschlecht', `tm_tiere`.`Kastriert` as 'tm_tiere.Kastriert', `tm_tiere`.`Laeufig` as 'tm_tiere.Laeufig', `tm_tiere`.`Farbe` as 'tm_tiere.Farbe', `tm_tiere`.`Aufenthaltsort` as 'tm_tiere.Aufenthaltsort', `tm_aufenthaltsort`.`Ort` as 'tm_aufenthaltsort.Ort', `tm_tiere`.`Intra` as 'tm_tiere.Intra', `tm_tiere`.`Tasso` as 'tm_tiere.Tasso', `tm_tiere`.`DHR` as 'tm_tiere.DHR', `tm_tiere`.`Chip1` as 'tm_tiere.Chip1', `tm_tiere`.`Chiport1` as 'tm_tiere.Chiport1', `tm_tiere`.`Chip1wann` as 'tm_tiere.Chip1wann', `tm_tiere`.`Chip2` as 'tm_tiere.Chip2', `tm_tiere`.`Chiport2` as 'tm_tiere.Chiport2', `tm_tiere`.`Ringnummer` as 'tm_tiere.Ringnummer', `tm_tiere`.`Groesse` as 'tm_tiere.Groesse', `tm_tiere`.`Ausweis` as 'tm_tiere.Ausweis', `tm_tiere`.`Ausweisnr` as 'tm_tiere.Ausweisnr', `tm_tiere`.`Tierarzt` as 'tm_tiere.Tierarzt', `tm_arzt`.`Name` as 'tm_arzt.Name', `tm_arzt`.`Praxis` as 'tm_arzt.Praxis', `tm_arzt`.`Vorname` as 'tm_arzt.Vorname', `tm_tiere`.`Naechste_Entwurmung` as 'tm_tiere.Naechste_Entwurmung', `tm_tiere`.`Entwurmungdatum` as 'tm_tiere.Entwurmungdatum', `tm_tiere`.`Krankheiten` as 'tm_tiere.Krankheiten', `tm_tiere`.`Pflegestelle` as 'tm_tiere.Pflegestelle', `tm_pflegestellen`.`id` as 'tm_pflegestellen.id', `tm_pflegestellen`.`Name` as 'tm_pflegestellen.Name', `tm_pflegestellen`.`Vorname` as 'tm_pflegestellen.Vorname', `tm_tiere`.`Besitzer` as 'tm_tiere.Besitzer', `tm_adressen`.`id` as 'tm_adressen.id', `tm_adressen`.`Name` as 'tm_adressen.Name', `tm_adressen`.`Firma` as 'tm_adressen.Firma', `tm_adressen`.`Vorname` as 'tm_adressen.Vorname', `tm_tiere`.`Quarantaene` as 'tm_tiere.Quarantaene', `tm_tiere`.`Quarantaene_Datum` as 'tm_tiere.Quarantaene_Datum', `tm_tiere`.`Betreuer` as 'tm_tiere.Betreuer', `tm_uc_users`.`display_name` as 'tm_uc_users.display_name', `tm_tiere`.`Verstorben` as 'tm_tiere.Verstorben', `tm_tiere`.`Gestorben` as 'tm_tiere.Gestorben', `tm_tiere`.`Euthanasie` as 'tm_tiere.Euthanasie', `tm_tiere`.`Entflohung` as 'tm_tiere.Entflohung', `tm_tiere`.`Entflohung_Bis` as 'tm_tiere.Entflohung_Bis', `tm_tiere`.`Adoptant` as 'tm_tiere.Adoptant', `tm_tiere`.`Vermitteltdatum` as 'tm_tiere.Vermitteltdatum', (SELECT GROUP_CONCAT(CONCAT_WS(\": \", Name, DATE_FORMAT(Datum, \"%d.%m.%Y\")) ORDER BY tm_impfungentier.Datum SEPARATOR \"<br />\") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id) as '(SELECT GROUP_CONCAT(CONCAT_WS(\": \", Name, DATE_FORMAT(Datum, \"%d.%m.%Y\")) ORDER BY tm_impfungentier.Datum SEPARATOR \"<br />\") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id)' FROM  `tm_tiere` LEFT JOIN `tm_adressen` ON `tm_adressen`.`id` = `tm_tiere`.`Besitzer`  LEFT JOIN `tm_uc_users` ON `tm_uc_users`.`id` = `tm_tiere`.`Betreuer`  LEFT JOIN `tm_tierart` ON `tm_tierart`.`id` = `tm_tiere`.`Tierart`  LEFT JOIN `tm_aufenthaltsort` ON `tm_aufenthaltsort`.`id` = `tm_tiere`.`Aufenthaltsort`  LEFT JOIN `tm_pflegestellen` ON `tm_pflegestellen`.`id` = `tm_tiere`.`Pflegestelle`  LEFT JOIN `tm_adoptanten` ON `tm_adoptanten`.`id` = `tm_tiere`.`Adoptant`  LEFT JOIN `tm_arzt` ON `tm_arzt`.`id` = `tm_tiere`.`Tierarzt` WHERE `tm_tiere`.`lfdnr` != :where_0 AND  `tm_tiere`.`Vermitteltdatum` = :where_1 AND  `tm_tiere`.`Adoptant` = :where_2 AND  `tm_tiere`.`Geloescht` = :where_3 AND  `tm_tiere`.`Verstorben` = :where_4 AND  `tm_tiere`.`Gestorben` = :where_5  ORDER BY `tm_tiere`.`lfdnr`  asc  LIMIT 10",
    "bindings": [
      {
        "name": ":where_0",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_1",
        "value": "0000-00-00",
        "type": null
      },
      {
        "name": ":where_2",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_3",
        "value": 0,
        "type": null
      },
      {
        "name": ":where_4",
        "value": "0000-00-00",
        "type": null
      },
      {
        "name": ":where_5",
        "value": 0,
        "type": null
      }
    ]
  },
  {
    "query": "SELECT DISTINCT  `id` as 'id', `Art` as 'Art' FROM  `tm_tierart` ",
    "bindings": []
  },
  {
    "query": "SELECT DISTINCT  `id` as 'id', `Ort` as 'Ort' FROM  `tm_aufenthaltsort` ",
    "bindings": []
  },
  {
    "query": "SELECT DISTINCT  `id` as 'id', `Name` as 'Name' FROM  `tm_arzt` ",
    "bindings": []
  },
  {
    "query": "SELECT DISTINCT  `id` as 'id', `Name` as 'Name' FROM  `tm_pflegestellen` ",
    "bindings": []
  },
  {
    "query": "SELECT DISTINCT  `id` as 'id', `Name` as 'Name' FROM  `tm_adressen` ",
    "bindings": []
  },
  {
    "query": "SELECT DISTINCT  `id` as 'id', `display_name` as 'display_name' FROM  `tm_uc_users` ",
    "bindings": []
  }
]

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Thank you. I don't see any reason why adding the %Y would make any difference there. If you run:

 SELECT    `tm_tiere`.`id`                  AS 'tm_tiere.id',
          `tm_tiere`.`lfdnr`               AS 'tm_tiere.lfdnr',
          `tm_tiere`.`geloescht`           AS 'tm_tiere.Geloescht',
          `tm_tiere`.`lgrund`              AS 'tm_tiere.LGrund',
          `tm_tiere`.`name`                AS 'tm_tiere.Name',
          `tm_tiere`.`internnr`            AS 'tm_tiere.InternNr',
          `tm_tiere`.`aufnahmedatum`       AS 'tm_tiere.Aufnahmedatum',
          `tm_tiere`.`aufnahmegrund`       AS 'tm_tiere.Aufnahmegrund',
          `tm_tiere`.`woher`               AS 'tm_tiere.Woher',
          `tm_tiere`.`tierart`             AS 'tm_tiere.Tierart',
          `tm_tierart`.`art`               AS 'tm_tierart.Art',
          `tm_tiere`.`rasse`               AS 'tm_tiere.Rasse',
          `tm_tiere`.`merkmale`            AS 'tm_tiere.Merkmale',
          `tm_tiere`.`geburtsdatum`        AS 'tm_tiere.Geburtsdatum',
          `tm_tiere`.`age`                 AS 'tm_tiere.Age',
          `tm_tiere`.`geschlecht`          AS 'tm_tiere.Geschlecht',
          `tm_tiere`.`kastriert`           AS 'tm_tiere.Kastriert',
          `tm_tiere`.`laeufig`             AS 'tm_tiere.Laeufig',
          `tm_tiere`.`farbe`               AS 'tm_tiere.Farbe',
          `tm_tiere`.`aufenthaltsort`      AS 'tm_tiere.Aufenthaltsort',
          `tm_aufenthaltsort`.`ort`        AS 'tm_aufenthaltsort.Ort',
          `tm_tiere`.`intra`               AS 'tm_tiere.Intra',
          `tm_tiere`.`tasso`               AS 'tm_tiere.Tasso',
          `tm_tiere`.`dhr`                 AS 'tm_tiere.DHR',
          `tm_tiere`.`chip1`               AS 'tm_tiere.Chip1',
          `tm_tiere`.`chiport1`            AS 'tm_tiere.Chiport1',
          `tm_tiere`.`chip1wann`           AS 'tm_tiere.Chip1wann',
          `tm_tiere`.`chip2`               AS 'tm_tiere.Chip2',
          `tm_tiere`.`chiport2`            AS 'tm_tiere.Chiport2',
          `tm_tiere`.`ringnummer`          AS 'tm_tiere.Ringnummer',
          `tm_tiere`.`groesse`             AS 'tm_tiere.Groesse',
          `tm_tiere`.`ausweis`             AS 'tm_tiere.Ausweis',
          `tm_tiere`.`ausweisnr`           AS 'tm_tiere.Ausweisnr',
          `tm_tiere`.`tierarzt`            AS 'tm_tiere.Tierarzt',
          `tm_arzt`.`name`                 AS 'tm_arzt.Name',
          `tm_arzt`.`praxis`               AS 'tm_arzt.Praxis',
          `tm_arzt`.`vorname`              AS 'tm_arzt.Vorname',
          `tm_tiere`.`naechste_entwurmung` AS 'tm_tiere.Naechste_Entwurmung',
          `tm_tiere`.`entwurmungdatum`     AS 'tm_tiere.Entwurmungdatum',
          `tm_tiere`.`krankheiten`         AS 'tm_tiere.Krankheiten',
          `tm_tiere`.`pflegestelle`        AS 'tm_tiere.Pflegestelle',
          `tm_pflegestellen`.`id`          AS 'tm_pflegestellen.id',
          `tm_pflegestellen`.`name`        AS 'tm_pflegestellen.Name',
          `tm_pflegestellen`.`vorname`     AS 'tm_pflegestellen.Vorname',
          `tm_tiere`.`besitzer`            AS 'tm_tiere.Besitzer',
          `tm_adressen`.`id`               AS 'tm_adressen.id',
          `tm_adressen`.`name`             AS 'tm_adressen.Name',
          `tm_adressen`.`firma`            AS 'tm_adressen.Firma',
          `tm_adressen`.`vorname`          AS 'tm_adressen.Vorname',
          `tm_tiere`.`quarantaene`         AS 'tm_tiere.Quarantaene',
          `tm_tiere`.`quarantaene_datum`   AS 'tm_tiere.Quarantaene_Datum',
          `tm_tiere`.`betreuer`            AS 'tm_tiere.Betreuer',
          `tm_uc_users`.`display_name`     AS 'tm_uc_users.display_name',
          `tm_tiere`.`verstorben`          AS 'tm_tiere.Verstorben',
          `tm_tiere`.`gestorben`           AS 'tm_tiere.Gestorben',
          `tm_tiere`.`euthanasie`          AS 'tm_tiere.Euthanasie',
          `tm_tiere`.`entflohung`          AS 'tm_tiere.Entflohung',
          `tm_tiere`.`entflohung_bis`      AS 'tm_tiere.Entflohung_Bis',
          `tm_tiere`.`adoptant`            AS 'tm_tiere.Adoptant',
          `tm_tiere`.`vermitteltdatum`     AS 'tm_tiere.Vermitteltdatum',
          (
                   SELECT   group_concat(Concat_ws(": ", name, Date_format(datum, "%d.%m.%Y")) ORDER BY tm_impfungentier.datum SEPARATOR "")
                   FROM     tm_impfungentier
                   JOIN     tm_impfungen
                   ON       tm_impfungen.id = tm_impfungentier.impfung
                   WHERE    tier = tm_tiere.id
                   GROUP BY tm_tiere.id) AS '(SELECT GROUP_CONCAT(CONCAT_WS(": ", Name, DATE_FORMAT(Datum, "%d.%m.%Y")) ORDER BY tm_impfungentier.Datum SEPARATOR "<br />") FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung WHERE Tier = tm_tiere.id GROUP BY tm_tiere.id)'
FROM      `tm_tiere`
LEFT JOIN `tm_adressen`
ON        `tm_adressen`.`id` = `tm_tiere`.`besitzer`
LEFT JOIN `tm_uc_users`
ON        `tm_uc_users`.`id` = `tm_tiere`.`betreuer`
LEFT JOIN `tm_tierart`
ON        `tm_tierart`.`id` = `tm_tiere`.`tierart`
LEFT JOIN `tm_aufenthaltsort`
ON        `tm_aufenthaltsort`.`id` = `tm_tiere`.`aufenthaltsort`
LEFT JOIN `tm_pflegestellen`
ON        `tm_pflegestellen`.`id` = `tm_tiere`.`pflegestelle`
LEFT JOIN `tm_adoptanten`
ON        `tm_adoptanten`.`id` = `tm_tiere`.`adoptant`
LEFT JOIN `tm_arzt`
ON        `tm_arzt`.`id` = `tm_tiere`.`tierarzt`
WHERE     `tm_tiere`.`lfdnr` != :where_0
AND       `tm_tiere`.`vermitteltdatum` = :where_1
AND       `tm_tiere`.`adoptant` = :where_2
AND       `tm_tiere`.`geloescht` = :where_3
AND       `tm_tiere`.`verstorben` = :where_4
AND       `tm_tiere`.`gestorben` = :where_5
ORDER BY  `tm_tiere`.`lfdnr` ASC
LIMIT     10 

in a direct MySQL query (e.g. phpMyAdmin) what does happens?

I'm wondering if there is a size limit on the identifier and the extra two characters of %Y might just be taking it over that limit...

Allan

Rappi

Running that in phpmyadmin works perfectly fine. So it must be something with datatables. I tried removing the two dots and only leaving "%d%m%Y" which should save the two characters %Y but it still does not work so i assume a size limit is not the reason.

Rappi

I needed to extend the subquery a bit to actually select the correct data. After doing this i noticed that also those changes are causing datatables to return null for the column while in phpmyadmin the query works just fine:

(SELECT GROUP_CONCAT(impfungen) FROM (SELECT Tier, CONCAT_WS(": ", Name, MAX(Datum)) as impfungen FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung GROUP BY tm_impfungentier.Tier, tm_impfungentier.Impfung) t WHERE Tier = tm_tiere.id)

allan

What happens if you use %d.%Y, or just %Y? The query I pasted above is the one that the libraries are running, so if it works in phpMyAdmin, I can't see any reason why it wouldn't work in the library.

I think I'd probably need a dump of your database schema and a full copy of the script to be able to debug what is going on here.

Thanks,
Allan

Rappi

Hi @allan, regarding the second prolem (it is more important than the date): i tried now to debug myself and found out that MySQL is for some reason truncating the column name of the subquery in the result. That means that the Editor internal column name and the one in the mysql result are not matching anymore and that is why no data is delivered for that column.

I tried aliasing the subquery with

(SELECT GROUP_CONCAT(impfungen) FROM (SELECT Tier, CONCAT_WS(": ", Name, MAX(Datum)) as impfungen FROM tm_impfungentier JOIN tm_impfungen ON tm_impfungen.id = tm_impfungentier.Impfung GROUP BY tm_impfungentier.Tier, tm_impfungentier.Impfung) t WHERE Tier = tm_tiere.id) AS MyAlias

The problem is that due to the implementation of Field::dbField this alias is only used internally and is not passed to mysql. Now one solution would be to find a way to pass that alias actually to mysql. Is there a way to do that?

Edit: i found out the reason why the result column name is truncated it is because of the Alias max length of mysql which is 256 chars. So to get this to work with the editor i really need to be able to alias the subquery in mysql

allan

Thanks for your insight into this! Unfortunately adding the aliasing isn't going to be a trivial task, so for the moment a workaround might be to use a VIEW instead?

Allan

Rappi

Hi @allan,

using a view is a great idea. I will do that. Actually i can live with this solution. Anyway if somewhen in the future the aliasing is available, maybe you can notice me so we can switch.

Thanks a lot for your awesome support there!