[SOLVED]Issue with sc_select_where(add) on a query containing a sub-query

adz1111 · May 7, 2020, 7:59pm

I have a grid which has the following source query (don’t worry, you do not need to go through it in any detail). The point to focus on (I believe) is that:

[LIST=1]

The query has a sub-query in it, and

The highlighted WHERE clause (see near the bottom) is to be added dynamically only if certain conditions are met. [/LIST] The query runs perfectly well - with or without the WHERE - through phpMyAdmin and other SQL utilities.

SELECT
    SUB_ProjName.TimesheetID,
    SUB_ProjName.TimesheetName,
    SUB_ProjName.AgencyID,
    SUB_ProjName.ClientID,
    SUB_ProjName.AgencyContactID,
    SUB_ProjName.ClientContactID,
    SUB_ProjName.WeekCommencing,
    SUB_ProjName.ClientApprovedDate,
    SUB_ProjName.SignedTimesheet,
    SUB_ProjName.User,
    SUB_ProjName.projectname,
    SUB_ProjName.AgencyName,
    SUB_ProjName.ClientName,
    SUB_ProjName.ContactName
FROM
    (SELECT
        tbltimesheets.TimesheetID,
        tbltimesheets.TimesheetName,
        tbltimesheets.AgencyID,
        tbltimesheets.ClientID,
        tbltimesheets.AgencyContactID,
        tbltimesheets.ClientContactID,
        tbltimesheets.WeekCommencing,
        tbltimesheets.ClientApprovedDate,
        tbltimesheets.SignedTimesheet,
        tbltimesheets.User,
        GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname,
        tblagencies.AgencyName,
        tblclients.ClientName,
        tblclientcontacts.ContactName
     FROM
        tblprojects
        INNER JOIN tblprojecthours
         ON tblprojects.projectid = tblprojecthours.ProjectID
        INNER JOIN tbltimesheets
         ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
        INNER JOIN tblclientcontacts
         ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
        INNER JOIN tblagencies
         ON tbltimesheets.AgencyID = tblagencies.AgencyID
        INNER JOIN tblclients
         ON tbltimesheets.ClientID = tblclients.ClientID
         AND tblclients.ClientID = tblclientcontacts.ClientID
     GROUP BY
        tbltimesheets.TimesheetID) SUB_ProjName
WHERE
    SUB_ProjName.User = 'testdel'
ORDER BY
    SUB_ProjName.WeekCommencing DESC

The bit that determines whether the WHERE is to be included or not is handled in the grid’s onScriptInit event, and if it is to be added, I use the following code:

if ({something} == 'this_or_that') {
        sc_select_where(add) = "WHERE SUB_ProjName.User = 'testdel' ";
}

When I run the app I get 1054: Unknown column ‘SUB_ProjName.User’ in ‘where clause’.

When I look at the SQL that SC generates I see (look at the last bit):

(mysqlt): select count(*) from (SELECT SUB_ProjName.TimesheetID, SUB_ProjName.TimesheetName, SUB_ProjName.AgencyID,
 SUB_ProjName.ClientID, SUB_ProjName.AgencyContactID, SUB_ProjName.ClientContactID, SUB_ProjName.WeekCommencing,
 SUB_ProjName.ClientApprovedDate, SUB_ProjName.SignedTimesheet, SUB_ProjName.User, SUB_ProjName.projectname,
 SUB_ProjName.AgencyName, SUB_ProjName.ClientName, SUB_ProjName.ContactName FROM (SELECT tbltimesheets.TimesheetID,
 tbltimesheets.TimesheetName, tbltimesheets.AgencyID, tbltimesheets.ClientID, tbltimesheets.AgencyContactID, tbltimesheets.ClientContactID,
 tbltimesheets.WeekCommencing, tbltimesheets.ClientApprovedDate, tbltimesheets.SignedTimesheet, tbltimesheets.User, GROUP_CONCAT(
 projectname SEPARATOR ' ' ) AS projectname, tblagencies.AgencyName, tblclients.ClientName, tblclientcontacts.ContactName FROM tblprojects
 INNER JOIN tblprojecthours ON tblprojects.projectid = tblprojecthours.ProjectID INNER JOIN tbltimesheets ON tblprojecthours.TimesheetID =
 tbltimesheets.TimesheetID INNER JOIN tblclientcontacts ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID INNER JOIN
 tblagencies ON tbltimesheets.AgencyID = tblagencies.AgencyID INNER JOIN tblclients ON tbltimesheets.ClientID = tblclients.ClientID AND
 tblclients.ClientID = tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName ) nm_sel_esp WHERE SUB_ProjName.User
 = 'testdel'

It seems to me that the query is fine except for the red bit that SC adds near the end. I am only guessing here, but it looks like SC wants nm_sel_esp at the very END of the query - so that the last bit should look like this (with the close bracket moved too):

......... tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName WHERE SUB_ProjName.User = 'testdel' ) nm_sel_esp

If that’s the case then SC has miscalculated where the query ends. And I think its the sub-query that’s confused the software. Is this a bug, or am I doing something stupid?

Thoughts folks?

rr1 · May 7, 2020, 8:48pm

I would use a slightly different method…

SELECT
SUB_ProjName.TimesheetID,
SUB_ProjName.TimesheetName,
SUB_ProjName.AgencyID,
SUB_ProjName.ClientID,
SUB_ProjName.AgencyContactID,
SUB_ProjName.ClientContactID,
SUB_ProjName.WeekCommencing,
SUB_ProjName.ClientApprovedDate,
SUB_ProjName.SignedTimesheet,
SUB_ProjName.User,
SUB_ProjName.projectname,
SUB_ProjName.AgencyName,
SUB_ProjName.ClientName,
SUB_ProjName.ContactName
FROM
(SELECT
tbltimesheets.TimesheetID,
tbltimesheets.TimesheetName,
tbltimesheets.AgencyID,
tbltimesheets.ClientID,
tbltimesheets.AgencyContactID,
tbltimesheets.ClientContactID,
tbltimesheets.WeekCommencing,
tbltimesheets.ClientApprovedDate,
tbltimesheets.SignedTimesheet,
tbltimesheets.User,
GROUP_CONCAT( projectname SEPARATOR ’ ’ ) AS projectname,
tblagencies.AgencyName,
tblclients.ClientName,
tblclientcontacts.ContactName
FROM
tblprojects
INNER JOIN tblprojecthours
ON tblprojects.projectid = tblprojecthours.ProjectID
INNER JOIN tbltimesheets
ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
INNER JOIN tblclientcontacts
ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
INNER JOIN tblagencies
ON tbltimesheets.AgencyID = tblagencies.AgencyID
INNER JOIN tblclients
ON tbltimesheets.ClientID = tblclients.ClientID
AND tblclients.ClientID = tblclientcontacts.ClientID
GROUP BY
tbltimesheets.TimesheetID) SUB_ProjName
WHERE
1=1 and SUB_ProjName.User = ‘testdel’
ORDER BY
SUB_ProjName.WeekCommencing DESC

Then adding the rest would be simpler…
Lets hope that works for you…

adz1111 · May 7, 2020, 8:48pm

Thanks rr

Unfortunately, I tried that and it makes no difference I’m afraid.

This was the error:

Error
Error while accessing the database:
Unknown column 'SUB_ProjName.User' in 'where clause'
select count(*) from (SELECT SUB_ProjName.TimesheetID, SUB_ProjName.TimesheetName, SUB_ProjName.AgencyID, SUB_ProjName.ClientID, SUB_ProjName.AgencyContactID, SUB_ProjName.ClientContactID, SUB_ProjName.WeekCommencing, SUB_ProjName.ClientApprovedDate, SUB_ProjName.SignedTimesheet, SUB_ProjName.User, SUB_ProjName.projectname, SUB_ProjName.AgencyName, SUB_ProjName.ClientName, SUB_ProjName.ContactName FROM (SELECT tbltimesheets.TimesheetID, tbltimesheets.TimesheetName, tbltimesheets.AgencyID, tbltimesheets.ClientID, tbltimesheets.AgencyContactID, tbltimesheets.ClientContactID, tbltimesheets.WeekCommencing, tbltimesheets.ClientApprovedDate, tbltimesheets.SignedTimesheet, tbltimesheets.User, GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname, tblagencies.AgencyName, tblclients.ClientName, tblclientcontacts.ContactName FROM tblprojects INNER JOIN tblprojecthours ON tblprojects.projectid = tblprojecthours.ProjectID INNER JOIN tbltimesheets ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID INNER JOIN tblclientcontacts ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID INNER JOIN tblagencies ON tbltimesheets.AgencyID = tblagencies.AgencyID INNER JOIN tblclients ON tbltimesheets.ClientID = tblclients.ClientID AND tblclients.ClientID = tblclientcontacts.ClientID GROUP BY tbltimesheets.TimesheetID) SUB_ProjName ) nm_sel_esp WHERE 1=1 AND SUB_ProjName.User = 'testdel'

Again, the entire WHERE clause (regardless of what’s in it) is placed AFTER the end of the query as far as SC is concerned. I’m going to hack the generated code later to see if it works with the query string changed so the “) nm_sel_esp” is last in the string, AFTER the WHERE.

I’ll post back soon.

rr1 · May 7, 2020, 8:48pm

You could also create a view for the inner query and then test it with that setup.

adz1111 · May 7, 2020, 8:48pm

Yeah - that’s the next bit I’ll try as moving the “) nm…” made no difference. That’s because I thought the “nm_sel_esp” was a variable they put in, but when looking at the fully built query I can see its just the name they give to their own wrapper subquery. So the query that’s failing is not actually the one that feeds the grid, but a pre-query SC runs for SC to determine the record count my query will produce (presumably for the navigation bits on the grid). Their full query is:

select 
	count(*) 
from 
	(SELECT 
		SUB_ProjName.TimesheetID, 
		SUB_ProjName.TimesheetName, 
		SUB_ProjName.AgencyID, 
		SUB_ProjName.ClientID, 
		SUB_ProjName.AgencyContactID, 
		SUB_ProjName.ClientContactID, 
		SUB_ProjName.WeekCommencing, 
		SUB_ProjName.ClientApprovedDate, 
		SUB_ProjName.SignedTimesheet, 
		SUB_ProjName.User, 
		SUB_ProjName.projectname, 
		SUB_ProjName.AgencyName, 
		SUB_ProjName.ClientName, 
		SUB_ProjName.ContactName 
	FROM 
		(SELECT 
			tbltimesheets.TimesheetID, 
			tbltimesheets.TimesheetName, 
			tbltimesheets.AgencyID, 
			tbltimesheets.ClientID, 
			tbltimesheets.AgencyContactID, 
			tbltimesheets.ClientContactID, 
			tbltimesheets.WeekCommencing, 
			tbltimesheets.ClientApprovedDate, 
			tbltimesheets.SignedTimesheet, 
			tbltimesheets.User, 
			GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname, 
			tblagencies.AgencyName, 
			tblclients.ClientName, 
			tblclientcontacts.ContactName 
		FROM 
			tblprojects 
			INNER JOIN tblprojecthours 
				ON tblprojects.projectid = tblprojecthours.ProjectID 
			INNER JOIN tbltimesheets 
				ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID 
			INNER JOIN tblclientcontacts 
				ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID 
			INNER JOIN tblagencies 
				ON tbltimesheets.AgencyID = tblagencies.AgencyID 
			INNER JOIN tblclients 
				ON tbltimesheets.ClientID = tblclients.ClientID AND tblclients.ClientID = tblclientcontacts.ClientID 
			GROUP BY tbltimesheets.TimesheetID
		) SUB_ProjName 
         ) nm_sel_esp	
WHERE
        SUB_ProjName.User = 'testdel'

Clearly that will fail as the WHERE is in the wrong place. If I move it as I suggested to the right place it works in phpMyAdmin - but not in the hacked generated code

UPDATE: Realised moving to the end does work but then there is another SC pre-query that takes the corrected query and adds an ORDER BY to it so once again the nm_sel_esp is not at the very end again - so that query then fails. Looks like a view is the way to go (hoping not to take too much of a performance hit)… will update here later

adz1111 · May 7, 2020, 8:49pm

[SOLVED] Issue with sc_select_where(add) on a query containing a sub-query

SOLVED

Ok - as Views don’t allow sub-queries, by rewriting the query so it used joins rather than sub-queries, I simply tried that rewritten query (before making it a view), and it worked like that, so no view was necessary. So the new query became (without the WHERE - but the WHERE is now happily dynamically added as needed to that SQL with the sc_select_where(add) macro):

SELECT
	tbltimesheets.TimesheetID,
	tbltimesheets.TimesheetName,
	tbltimesheets.WeekCommencing,
	tbltimesheets.AgencyID,
	tblagencies.AgencyName,
	tbltimesheets.AgencyContactID,
	tblagencycontacts.ContactName,
	tbltimesheets.ClientID,
	tblclients.ClientName,
	tbltimesheets.ClientContactID,
	tblclientcontacts.ContactName,
	tbltimesheets.ClientApprovedDate,
	tbltimesheets.SignedTimesheet,
	tbltimesheets.User,
	GROUP_CONCAT( projectname SEPARATOR ' ' ) AS projectname
FROM
	tblprojects
	INNER JOIN tblprojecthours
	 ON tblprojects.projectid = tblprojecthours.ProjectID
	INNER JOIN tbltimesheets
	 ON tblprojecthours.TimesheetID = tbltimesheets.TimesheetID
	INNER JOIN tblagencycontacts
	 ON tbltimesheets.AgencyContactID = tblagencycontacts.AgencyContactID
	INNER JOIN tblclientcontacts
	 ON tbltimesheets.ClientContactID = tblclientcontacts.ClientContactID
	INNER JOIN tblclients
	 ON tblclientcontacts.ClientID = tblclients.ClientID
	INNER JOIN tblagencies
	 ON tblagencycontacts.AgencyID = tblagencies.AgencyID
GROUP BY
	tbltimesheets.TimesheetID
ORDER BY
	tbltimesheets.WeekCommencing DESC