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

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?

  • 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…

    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.

    1 Like

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

    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 :frowning:

    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

    [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
    
    1 Like