Recordset_Selecting using OR and custom field value (v2021)

philmills1 · October 14, 2021, 4:53pm

v2021.0.15I’m working on two tables: Tasks and MilestonesEvery task has one owner, but other users can be added as collaborators to any task.
Colaborators and task owners can create their own Milestones under the main task, and when they do they are assigned as owner of that milestone.
Colaborators can also be added to milestones.The milestone table has a field fk_TaskID which contains the id of the main task:
The milestone table also has a custom field fk_TaskOwner which looks up the id of the owner of the main task:
(SELECT fk_StaffID FROM Workplan_Tasks WHERE Workplan_Tasks.id=fk_TaskID)I’m trying to using recordsetSelecting to filter the milestones table to show all records that the user is asssociated with, either as:

owner of the milestone
collaborator of the milestone
owner of the main task

Here is my code currently:

public function recordsetSelecting(&$filter) {
if (CurrentUserLevel() !='-1') {  
    	    	AddFilter($filter, "fk_StaffID LIKE '%".CurrentUserCode()."%' OR Owner='".CurrentUserID()."' OR fk_TaskOwner ='".CurrentUserID()."'");
    }
}

which throws this error:/var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php(79): An exception occurred while executing ‘SELECT COUNT(*) FROM (SELECT * FROM Workplan_Milestones_User WHERE (fk_StaffID LIKE ‘%36812100048%’ OR Owner=‘1’ OR fk_TaskOwner=‘1’) AND ((((SELECT fk_StudyYear FROM Workplan_Tasks WHERE id=fk_TaskID) = 15) AND (fk_School = ‘75014149’)) AND (fk_StaffID LIKE ‘%36812100048%’ OR Owner=‘1’ OR fk_TaskOwner =’‘))) COUNT_TABLE’: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘fk_TaskOwner’ in 'where clause’The problem is with the owner lookup. If i remove that part, the page works fine, but also shows me tasks I’m not associated with at all.
It seems as though I can’t use custom fields in the filter, so I also tried this:

public function recordsetSelecting(&$filter) {
if (CurrentUserLevel() !='-1') {
    	$taskowner = ExecuteScalar("SELECT fk_StaffID FROM Workplan_Tasks WHERE id='".$this->fk_TaskID->CurrentValue."'");
    	AddFilter($filter, "fk_StaffID LIKE '%".CurrentUserCode()."%' OR Owner='".CurrentUserID()."' OR fk_TaskOwner ='".$taskowner."'");
        }
 }

This throws a similar error and I’m guessing its because I can’t use $this->fk_TaskID->CurrentValue within the recordsetSelecting function.In case anyone is wondering, yes, my global function CurrentUserCode() is working fineHow can I get around this problem?

mobhar · October 14, 2021, 4:57pm

Please post your tables schema (CREATE TABLE …) including some records in it (INSERT INTO …) for more discussion.

philmills1 · October 14, 2021, 5:12pm

CREATE TABLE `Workplan_Tasks` (
  `id` int(11) NOT NULL,
  `fk_School` int(8) NOT NULL,
  `fk_StudyYear` int(11) NOT NULL,
  `fk_Team` int(11) NOT NULL,
  `Objective` varchar(500) COLLATE utf8mb4_estonian_ci NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `fk_StaffID` int(11) NOT NULL,
  `fk_Category` varchar(30) COLLATE utf8mb4_estonian_ci NOT NULL,
  `Deadline` date DEFAULT NULL,
  `Cancel` enum('0','1') COLLATE utf8mb4_estonian_ci NOT NULL DEFAULT '0',
  `Completed` date DEFAULT NULL,
  `VisibleTo` varchar(512) COLLATE utf8mb4_estonian_ci DEFAULT NULL,
  `LastUpdated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_estonian_ci ROW_FORMAT=COMPACT;

INSERT INTO `Workplan_Tasks` (`id`, `fk_School`, `fk_StudyYear`, `fk_Team`, `Objective`, `Timestamp`, `fk_StaffID`, `fk_Category`, `Deadline`, `Cancel`, `Completed`, `VisibleTo`, `LastUpdated`) VALUES
(1, 12345678, 8, 1, 'build something cool', '2020-11-25 12:25:25', 1, '4', '2021-06-30', '0', NULL, NULL, '2021-05-24 12:18:33');

CREATE TABLE `Workplan_Milestones` (
  `id` int(11) NOT NULL,
  `fk_School` int(8) NOT NULL,
  `fk_Tasks_id` int(11) NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `Owner` int(11) NOT NULL,
  `Activity` varchar(255) COLLATE utf8mb4_estonian_ci NOT NULL,
  `Documents` varchar(500) COLLATE utf8mb4_estonian_ci DEFAULT NULL,
  `Link` varchar(100) COLLATE utf8mb4_estonian_ci DEFAULT NULL,
  `Deadline_Date` date DEFAULT NULL,
  `Complete` enum('0','1') COLLATE utf8mb4_estonian_ci NOT NULL DEFAULT '0',
  `Completed_Date` date DEFAULT NULL,
  `fk_StaffID` varchar(255) COLLATE utf8mb4_estonian_ci DEFAULT NULL,
  `VisibleTo` varchar(512) COLLATE utf8mb4_estonian_ci DEFAULT NULL,
  `LastUpdated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `Cancel` enum('0','1') COLLATE utf8mb4_estonian_ci NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_estonian_ci ROW_FORMAT=COMPACT;

INSERT INTO `Workplan_Milestones` (`id`, `fk_School`, `fk_Tasks_id`, `Timestamp`, `Owner`, `Activity`, `Documents`, `Link`, `Deadline_Date`, `Complete`, `Completed_Date`, `fk_StaffID`, `VisibleTo`, `LastUpdated`, `Cancel`) VALUES
(1, 12345678, 1, '2020-11-25 12:25:25', 1, 'build 1', NULL, NULL, '2020-11-30', '0', '2020-11-26', '12345674910,23456789101', NULL, '2021-05-24 12:13:43', '0'),
(2, 12345678, 1, '2020-11-24 22:00:00', 1, 'build 2', NULL, NULL, '2020-12-31', '1', NULL, '34567891011', NULL, '2021-05-24 12:13:43', '0');

mobhar · October 15, 2021, 1:16am

philmills wrote:

Unknown column ‘fk_TaskOwner’ in ‘where clause’

As the error message said, there is no fk_TaskOwner field either in Workplan_Tasks or Workplan_Milestones table.

The question is, which field you want to refer as “TaskOwner” from both tables above?

philmills1 · October 15, 2021, 6:15am

I gave this info in my first post.
fk_Taskowner is a custom field with this query:
(SELECT fk_StaffID FROM Workplan_Tasks WHERE Workplan_Tasks.id=fk_TaskID)

arbei · October 15, 2021, 6:21am

philmills wrote:

The > milestone > table also has a custom field > fk_TaskOwner >

You said the custom field is in the milestone table (I assume it is actually the Workplan_Milestones table) but the error is from the Workplan_Milestones_User table:philmills wrote:

/var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php(79):
An exception occurred while executing 'SELECT COUNT(*) FROM (SELECT * FROM > Workplan_Milestones_User > WHERE (> fk_StaffID > LIKE ‘%36812100048%’ OR Owner=‘1’ OR> fk_TaskOwner=‘1’)…

philmills1 · October 15, 2021, 6:44am

Right sorry - my bad, but your assumption is basically correct.
Workplan_Milestones_User is a view of Workplan_Milestones, in essence its the same table.
I’m now looking at a workaround, whereby the fk_TaskOwner would be an actual saved value in Workplan_Milestones.
It should make things easier, I’ll put the select query into a function, and put the function into Auto-Update Value for that field.
It’s not perfect, because if the main task owner changes, Workplan_Milestones.fk_TaskOwner won’t get updated.
For that I’d need to add code to update them, like : if task owner is edited, update owner in all associated milestones

mobhar · October 15, 2021, 6:53am

Unfortunately, you cannot include the Custom Field as part of filter in AddFilter global function, because it will added into WHERE clause of the SQL SELECT.