Field as subquery with CONACT_WS renders wrong

Field as subquery with CONACT_WS renders wrong

RappiRappi Posts: 82Questions: 18Answers: 1

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!

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • RappiRappi Posts: 82Questions: 18Answers: 1
    edited October 2020

    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')
        );
    
  • RappiRappi Posts: 82Questions: 18Answers: 1

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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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

  • RappiRappi Posts: 82Questions: 18Answers: 1

    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!

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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

  • RappiRappi Posts: 82Questions: 18Answers: 1
    edited October 2020

    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!

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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

  • RappiRappi Posts: 82Questions: 18Answers: 1

    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!

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

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

    Allan

  • RappiRappi Posts: 82Questions: 18Answers: 1

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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

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

  • RappiRappi Posts: 82Questions: 18Answers: 1
    edited November 2020

    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.

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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

  • RappiRappi Posts: 82Questions: 18Answers: 1
    edited November 2020

    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.

  • RappiRappi Posts: 82Questions: 18Answers: 1

    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)

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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

  • RappiRappi Posts: 82Questions: 18Answers: 1
    edited November 2020

    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

  • allanallan Posts: 61,436Questions: 1Answers: 10,049 Site admin

    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

  • RappiRappi Posts: 82Questions: 18Answers: 1

    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!

This discussion has been closed.