Answers

• allan Posts: 65,347Questions: 1Answers: 10,841 Site admin

  October 8

  What is the error please? I would have expected that to work.

  Allan
• Martyn.s Posts: 11Questions: 4Answers: 0

  October 9

  So, the code shown above works a charm, but when I try to introduce where() to not include any soft deleted records in the join, it displays an error. For example:

    ->join(
      Mjoin::inst( $tables->storageUnitsTable )
        ->name( $containers )
        ->link( $containers.'.parentUnitID', $storageUnits.'.storageUnitID')
        ->fields(
          Field::inst('storageUnitID')
        )
      )
    ->join(
      Mjoin::inst( $tables->prototypeSerialsTable )
        ->name( $serializedParts )
        ->link( $serializedParts.'.storageUnitID', $serializedParts.'.storageUnitID')
        ->fields(
          Field::inst('prototypeSerialNumber'),
          Field::inst('isDeleted'),
        )
        ->where($serializedParts.'.isDeleted', 0)
      )
    ->where( $storageUnits.'.isDeleted', 0)
  

  produces the following error message:

  DataTables warning: table id=storageUnitsList - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'serialized.isDeleted' in 'where clause'

  I don't understand why it doesn't acknowledge the column when I do so, my first thought is it might be tied to the fact that I am aliasing the tables with name(), but even with that removed It does not want to play nice and I am at a loss for what to do to make it so that it recognizes the isDeleted soft delete flag and prevents any data that should be inaccessible from being joined
• allan Posts: 65,347Questions: 1Answers: 10,841 Site admin

  October 9

  Can you show me the SQL schema for these tables please? Specifically I'd like to see the structure of the serialized table.

  Also, could you add ->debug(true) immediately before the ->process(...) call, and then show me the raw JSON that is returned from the server when you load the page?

  Thanks,
  Allan
• Martyn.s Posts: 11Questions: 4Answers: 0

  October 15

  Sorry for the slow reply, just had a long weekend

  the SQL schema for the serialized table is as follows:

  CREATE TABLE `prototypeSerialized` (
  
    `prototypeGroupID` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  
    `serializedNum` int NOT NULL DEFAULT '0',
  
    `prototypeSerialNumber` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (concat(`prototypeGroupID`,`serializedNum`)) STORED NOT NULL,
    `isEngraved` tinyint(1) NOT NULL DEFAULT '0',
  
    `dateEngraved` timestamp NULL DEFAULT NULL,
  
    `storageUnitID` varchar(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  
    `parentPrototypeSN` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  
    `comments` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  
    `isDeleted` bit(1) NOT NULL DEFAULT b'0',
  
    `deletedByID` int DEFAULT NULL,
  
    `dateDeleted` timestamp NULL DEFAULT NULL
  
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  
  ALTER TABLE `prototypeSerialized`
    ADD PRIMARY KEY (`prototypeGroupID`,`serializedNum`);
  COMMIT;
  

  as for the Raw JSON that is passed back, it's as follows:

  {
      "fieldErrors": [],
      "error": "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'serialized.isDeleted' in 'where clause'",
      "data": [],
      "ipOpts": [],
      "cancelled": [],
      "debug": [
          "Editor PHP libraries - version 2.3.2",
          {
              "query": "SELECT  `storage`.`storageUnitID` as 'storage.storageUnitID', `storage`.`storageUnitTypeID` as 'storage.storageUnitTypeID', `storage`.`parentUnitID` as 'storage.parentUnitID', `storage`.`storageUnitLabelName` as 'storage.storageUnitLabelName', `storage`.`storageUnitLabeled` as 'storage.storageUnitLabeled', `storage`.`isArchival` as 'storage.isArchival', `storage`.`dateCreated` as 'storage.dateCreated', `storage`.`createdByID` as 'storage.createdByID', `totes`.`storageUnitLabelName` as 'totes.storageUnitLabelName', `totes`.`storageUnitLabeled` as 'totes.storageUnitLabeled', `storageType`.`storageUnitTypeName` as 'storageType.storageUnitTypeName', `creator`.`fullName` as 'creator.fullName' FROM  `storageUnits` storage LEFT JOIN `staff` creator ON `creator`.`staffID` = `storage`.`createdByID`  LEFT JOIN `storageUnits` totes ON `totes`.`storageUnitID` = `storage`.`parentUnitID`  LEFT JOIN `storageUnitTypes` storageType ON `storageType`.`storageUnitTypeID` = `storage`.`storageUnitTypeID` WHERE `storage`.`isDeleted` = :where_0 ",
              "bindings": [
                  {
                      "name": ":where_0",
                      "value": 0,
                      "type": null
                  }
              ]
          },
          {
              "query": "SELECT DISTINCT  `storageUnitTypeID` as 'storageUnitTypeID', `storageUnitTypeName` as 'storageUnitTypeName', `storageUnitTypeShorthand` as 'storageUnitTypeShorthand', `storageunittypename` as 'storageunittypename' FROM  `storageUnitTypes` WHERE (1=1) ORDER BY `storageUnitTypeName`  ",
              "bindings": []
          },
          {
              "query": "SELECT DISTINCT  `storageUnitID` as 'storageUnitID', `storageUnitID` as 'storageUnitID', `storageUnitLabeled` as 'storageUnitLabeled', `storageUnitLabelName` as 'storageUnitLabelName', `storageunitid` as 'storageunitid' FROM  `storageUnits` WHERE (`isDeleted` = :where_1 ) ORDER BY `storageUnitID`  ",
              "bindings": [
                  {
                      "name": ":where_1",
                      "value": 0,
                      "type": null
                  }
              ]
          },
          {
              "query": "SELECT DISTINCT  `storage`.`storageUnitID` as 'dteditor_pkey', `storageUnits`.`storageUnitID` as 'storageUnitID' FROM  `storageUnits` storage  JOIN `storageUnits` ON `storageUnits`.`parentUnitID` = `storage`.`storageUnitID` WHERE `storage`.`storageUnitID` IN (:wherein1, :wherein2, :wherein3, :wherein4) ",
              "bindings": [
                  {
                      "name": ":wherein1",
                      "value": "CT-D6L9",
                      "type": null
                  },
                  {
                      "name": ":wherein2",
                      "value": "CT-L6C0",
                      "type": null
                  },
                  {
                      "name": ":wherein3",
                      "value": "CT-S0Z3",
                      "type": null
                  },
                  {
                      "name": ":wherein4",
                      "value": "CT-S4P9",
                      "type": null
                  }
              ]
          },
          {
              "query": "SELECT DISTINCT  `storage`.`storageUnitID` as 'dteditor_pkey', `prototypeSerialized`.`prototypeSerialNumber` as 'prototypeSerialNumber', `prototypeSerialized`.`isDeleted` as 'isDeleted' FROM  `storageUnits` storage  JOIN `prototypeSerialized` ON `prototypeSerialized`.`storageUnitID` = `storage`.`storageUnitID` WHERE `serialized`.`isDeleted` = :where_0 AND `storage`.`storageUnitID` IN (:wherein1, :wherein2, :wherein3, :wherein4) ",
              "bindings": [
                  {
                      "name": ":where_0",
                      "value": 0,
                      "type": null
                  },
                  {
                      "name": ":wherein1",
                      "value": "CT-D6L9",
                      "type": null
                  },
                  {
                      "name": ":wherein2",
                      "value": "CT-L6C0",
                      "type": null
                  },
                  {
                      "name": ":wherein3",
                      "value": "CT-S0Z3",
                      "type": null
                  },
                  {
                      "name": ":wherein4",
                      "value": "CT-S4P9",
                      "type": null
                  }
              ]
          }
      ]
  }